DLookup/Global Function 1

[rjoshi2]

I am reposting my question because I just realized that I posted in the wrong forum (previous post is in Microsoft: Access Forms).

I am trying to use dlookup to find the phone number of a person based on there name. I am trying to due this using a global function because I need to due this in more than one form. The problem that I am having is that I can get to run but not the other. Any help in fixing would be appreciated.

Thank You,
rjoshi2

My code

Form Class Module

Private Sub Reference_Person_AfterUpdate()
Dim try As Object
try = ReferencePerson(Me)
end sub

Global Module

Public Function ReferencePerson(ByRef frmRef As Form)
If IsNull(frmRef.ActiveControl) = False Then
frmRef.Phone_Number = DLookup("[Phone Number]", "[tblPscITAcquisitionLog]", "[Reference Person]= Forms![XXXXX]![Reference Person]&quot
Else
frmRef.Phone_Number = Null
End If
End Function

This code will work for one form or the other if make XXXXX the name of that form (frmDataEntry1 or frmDataEntry2). I am not sure how to pass in the name of the form in. I tried to passing the name in ByRef but it did not work the way I did it.

 

[GHolden]

You would be better making it a function and passing the name..

eg.

Public Function gstrPhoneNumber(ByRef strPerson As string)as string

gstrPhoneNumber = DLookup("[Phone Number]", "[tblPscITAcquisitionLog]", "[Reference Person]= '" & strPerson & "'&quot
Else
gstrPhoneNumber = Null
End If
End Function

then call from form using..

Phone_Number = gstrPhoneNumber (<form person>)



There are two ways to write error-free programs; only the third one works.

 

[rjoshi2]

GHolden,

What is your “if” statement for your function? What do you mean by (<form person>)?

Thank You,
rjoshi2

 

[rjoshi2]

I figured it out.

Here is my code:

Form Class Module

Private Sub Reference_Person_AfterUpdate()
Me.Phone_Number = gstrPhoneNumber(Me, Me.Reference_Person)
End Sub

Form Class Module

Public Function gstrPhoneNumber(ByRef frmRef As Form, ByRef strPerson As String) As String
If IsNull(frmRef.ActiveControl) = False Then
gstrPhoneNumber = DLookup(&quot;[Phone Number]&quot;, &quot;[tblPscITAcquisitionLog]&quot;, &quot;[Reference Person]= '&quot; & strPerson & &quot;'&quot
Else
gstrPhoneNumber = Null
End If
End Function

 

[GHolden]

Ooops... sorry, rushing again, no if statement required..


Public Function gstrPhoneNumber(ByRef strPerson As string)as string

gstrPhoneNumber = DLookup(&quot;[Phone Number]&quot;, &quot;[tblPscITAcquisitionLog]&quot;, &quot;[Reference Person]= '&quot; & strPerson & &quot;'&quot

End Function

DlookUp returns null if no record found..

(<form person>) is the 'Reference Person' from your form..

in your case Forms![XXXXX]![Reference Person]

if ReferencePerson on your form is a text box containing the name of the person and PhoneNumber is the name of the textbox you want to copy the Phone Number into, you would put on your form.

On your form you would have

PhoneNumber = gstrPhoneNumber(ReferencePerson)

Sorry, hope this is clearer?


There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Sorry, was writing my post while you were writing yours.

Spot on, you don't actually need to pass the form name though, because DLookUp will return null if no record is found.

There are two ways to write error-free programs; only the third one works.

 

[rjoshi2]

GHolden,
Thank you for your help. Which way to do you think is better?

rjoshi2

 

[GHolden]

I would avoid passing the form as a variable as it's an unnecessary overhead.

The only variable you need is the persons name as in this example.

Public Function gstrPhoneNumber(ByRef strPerson As string)as string

gstrPhoneNumber = DLookup(&quot;[Phone Number]&quot;, &quot;[tblPscITAcquisitionLog]&quot;, &quot;[Reference Person]= '&quot; & strPerson & &quot;'&quot

End Function

Because that's the only parameter required to find the persons phone number.

If you want to make sure null is not sent to the function I would do this in the form. This will be more efficient as you will avoid making the call to the function at all if Reference Person is null.

So in your form you would have.

If Not IsNull(ReferencePerson) then
PhoneNumber = gstrPhoneNumber(ReferencePerson)
Else
'Some kind of error message eg.
End if

There are two ways to write error-free programs; only the third one works.