Function w/ Dlookup

  • Thread starter Thread starter D. Stacy
  • Start date Start date
D

D. Stacy

Trying to write a function that takes the criteria from a text field on a
form. Current structure is not working:


Public Function FindGPCI_Work()
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI

CurrentWork_GPCI = DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
"frmLocalitySelect!txtLocality_ID")


End Function

Help!
 
1. Remove the double quotes from
"[ID] = " & frmLocalitySelect!txtLocality_ID


2. Rewrite it as a private function in the code module for frmLocalitySelect
Replace
frmLocalitySelect!txtLocality_ID
with
"[ID] = " & Me!txtLocality_ID

3. Add a check for when there is no value returned by the dlookup.

CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
Me!txtLocality_ID),0)



4. Rewrite it as a private function in the code module for frmLocalitySelect

Private Function FindGPCI_Work()
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI

CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
Me!txtLocality_ID),0)

End Function

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
One more thing, give the function a data type for the return value.
See where I have added As Long on the first line of the function.

Private Function FindGPCI_Work() As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI

CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
Me!txtLocality_ID),0)

Debug.Print CurrentWork_GPCI
End Functiion


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette Cunningham said:
1. Remove the double quotes from
"[ID] = " & frmLocalitySelect!txtLocality_ID


2. Rewrite it as a private function in the code module for
frmLocalitySelect
Replace
frmLocalitySelect!txtLocality_ID
with
"[ID] = " & Me!txtLocality_ID

3. Add a check for when there is no value returned by the dlookup.

CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
Me!txtLocality_ID),0)



4. Rewrite it as a private function in the code module for
frmLocalitySelect

Private Function FindGPCI_Work()
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI

CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
Me!txtLocality_ID),0)

End Function

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


D. Stacy said:
Trying to write a function that takes the criteria from a text field on a
form. Current structure is not working:


Public Function FindGPCI_Work()
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI

CurrentWork_GPCI = DLookup("Work_GPCI", "tblGPCI", "[ID] = " &
"frmLocalitySelect!txtLocality_ID")


End Function

Help!
Click to expand...
Click to expand...
 
Back
Top