problems looking up table with vba and SQL

[jackie77]

Hi all

Its been a while since I have been on here in fact its been a while since i have been using access and as usual I am stuck on one thing that I have been working on for hours trying different things to get it to work so hope someone could give me some advice please bear in mind I'm still a novice

The problem is:

i have a form that displays a number of property details (from a query) and on the form I have a number of unbound fields that i am trying to use vba to look up another table to see if a tenant is currently living in the property (as the table tenancy displays all tenant past and present) I have been using the following code which i know is wrong but does anyone have any suggestions I have tried various codes but cant get it to work!!

Private Sub Form_Load()
Dim strtest As String
strtest = Me.PropertyID.Value
Me.Forename = "SELECT "Tenancy.PropertyID FROM Tenancy " & _
"WHERE Tenancy.PropertyID = " & strtest & ";"

End Sub

Cheers
Jackie

 

[pbaldy]

You can't use SQL like that. The closest equivalent would be a DLookup(). Helpful link here:

http://www.mvps.org/access/general/gen0018.htm

 

[jackie77]

Cheers for the reply got it working with Dlookup as suggested thanks


Me.Forename = DLookup("[Forename]", "TenancyQuery", _
"[PropertyID] = Form![PropertyID]")

however when the field in the query is empty it returns an error how do I go about handling a null value??

Jackie

 

[pbaldy]

I assume you didn't copy/paste that, as the form reference doesn't look valid. In any case, use the Nz() function:

Me.Forename = Nz(DLookup(...), "whatever")

 

[jackie77]

Cheers works great!! Many Thanks for your help

 

[pbaldy]

No problemo, Jackie. Glad it worked for you.