AUTO FILL OF FIELDS

[Guest]

Can anybody out there help me? Please look at the following and tell me what
I'm doing wrong:
Dim varHAZTYPE, varDEPT, varDIV As Variant
varHAZTYPE = (DLookup("[HAZTYPE]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=
LOC&FRAMES"))
varDEPT = (DLookup("[DEPT]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=LOC&FRAMES"))
varDIV = (DLookup("[DIV]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=LOC&FRAMES"))
If (Not IsNull(varHAZTYPE)) Then Me![HAZTYPE] = varHAZTYPE
If (Not IsNull(varDEPT)) Then Me![DEPT] = varDEPT
If (Not IsNull(varDIV)) Then Me![DIV] = varDIV

I want the HAZTYPE, DEPT, and DIV fields to be automatically filled in from
my lookup table whenever LOC and FRAMES are the same on my form as they are
in the lookup table. It sort of works. I get the first record in the lookup
table no matter what I type in for LOC and FRAMES. What am I doing wrong?
James

 

[John Spencer (MVP)]

Your "Where" clause in the DLookup is incorrect. It is always going to match
the first record it finds since you have basically said Where [Loc] = [Loc] -
that is where this field is equal to itself.

varHAZTYPE = DLookup("[HAZTYPE]", "SPACELISTLOOKUP", _
"[LOC]&[FRAMES]='" & Me.Loc & Me.Frames & "'")

My assumption in the above was that Loc and Frames are text fields in your table
and that you have two controls named Loc and Frames on your form.

You might be better off using a recordset, since you are looking up three values
at one at a time. SOMETHING like the UNTESTED AIRCODE below.

DIM strSQL as String
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset

strSQL = "SELECT HazType, Dept, Div FROM SpaceListLookup" & _
" WHERE LOC ='" & Me.Loc & "' AND FRAMES ='" & me.Frames &"' "

Set dbAny = CurrentDB()
Set rstAny = dbany.OpenRecordset (strSQL)

With rstAny
If .BOF = False and .EOF = False THEN
Me.HazType = .Fields(0)
Me.Dept = .Fields(1)
Me.Div = .Field(2)
End if
End with

rstAny.Close
Set rstAny = Nothing

Set dbAny = Nothing

'You might want to include some error handling, etc.

LOC&FRAMES"))

Can anybody out there help me? Please look at the following and tell me what
I'm doing wrong:
Dim varHAZTYPE, varDEPT, varDIV As Variant
varHAZTYPE = (DLookup("[HAZTYPE]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=
LOC&FRAMES"))
varDEPT = (DLookup("[DEPT]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=LOC&FRAMES"))
varDIV = (DLookup("[DIV]", "SPACELISTLOOKUP", "[LOC]&[FRAMES]=LOC&FRAMES"))
If (Not IsNull(varHAZTYPE)) Then Me![HAZTYPE] = varHAZTYPE
If (Not IsNull(varDEPT)) Then Me![DEPT] = varDEPT
If (Not IsNull(varDIV)) Then Me![DIV] = varDIV

I want the HAZTYPE, DEPT, and DIV fields to be automatically filled in from
my lookup table whenever LOC and FRAMES are the same on my form as they are
in the lookup table. It sort of works. I get the first record in the lookup
table no matter what I type in for LOC and FRAMES. What am I doing wrong?
James