Software Catalog:
A Generic Lookup Function

GetMfgName

Here's a function that will do a lookup of the Manufacturer's Name for any report or form. To use it simply include a field with it's control source as MfgID from any table that includes it and edit box with it's control source set to =GetMfgName(MfgID) .

Or you could send a constant as a parameter instead of MfgID by enclosing it in quotes.

Of course you can use the code as a model for a lookup of any field in any table by changing table and field names.

Listing 7

Public Function GetMfgName(IDCode As String) As String
'************************************************************************************************************
' This function uses the passed ID code to find the company name in the Manufacturers table.
'************************************************************************************************************
Dim ThisDB As Database
Dim tblRead As Recordset
Dim TempCode As String
Dim TempNameStr As String
Dim SQLstr As String
`Database assumed open
`to hold recordset
`MfgID code from recordset
`MfgName from recordset
` holds SQL statement

` Protect against empty string parameter
If IDCode <> " " Then
SQLstr = " SELECT DISTINCTROW FROM Manufacturers WHERE " & _
" (((Manufacturers.MfgID) = `" & IDCode & " `));"
Set ThisDB = CurrentDb
` Create a recordset
Set tblRead = ThisDB.OpenRecordset(SQLstr)
`Protect against empty recordset e.g. the IDCode was not in the table
If tblRead <> Nil Then
` Read the field values of the recordset
TempCode = tblRead.Fields(" MfgID" )
TempNameStr = tblRead.Fields(" MfgName" )
GetMfgName = TempNameStr
Else
GetMfgName = " Name not found!
End If

` release memory used by the recordset
tblRead.Close
Else
GetMfgName = " ID code was missing"
End If
End Function

End of Listing 7



Return to Beginning
Previous
This document authored by Pat Tyler
Copyright July 1997