Date lookup

[Chuck216]

Hi

I have the following code behind one of my forms to automatically fill in
the LastName, FirstName & Division when a hang tag number is typed into the
form. The data is retrieved from tblName which also has a “year†field in it.
Since tblName can contain many years worth of data I would like to only fill
the form with the current years data. I don’t know how to add the code to
only retrieve the data if the year field is equal to the current year .I hope
I’m making my question understandable ( sometimes I confuse myself ) any
help with this will be greatly appreciated .


Private Sub HangTag_AfterUpdate()
On Error GoTo Err_HangTag_AfterUpdate


Dim varX As Variant
Dim varY As Variant
Dim varZ As Variant

varX = DLookup("LastName", "tblName", "HangTag ='" & Me.HangTag & "'")
varY = DLookup("FirstName", "tblName", "HangTag ='" & Me.HangTag & "'")
varZ = DLookup("Division", "tblName", "HangTag ='" & Me.HangTag & "'")
Me.LastName = varX
Me.FirstName = varY
Me.Division = varZ

Exit_HangTag_AfterUpdate:
Exit Sub

Err_HangTag_AfterUpdate:
MsgBox Err.Description
Resume Exit_HangTag_AfterUpdate

End Sub

 

[Ofer Cohen]

Every time you run the DlookUp is searching the table, so instead of running
dlookup 3 times for each field, open the table once with RecordSet

Something like

Private Sub HangTag_AfterUpdate()
On Error GoTo Err_HangTag_AfterUpdate


Dim MyDb as Dao.DataBase, MyRec As RecordSet
Set MyDb=CurrentDb
Set MyRec=MyDb.OpenRecordSet("Select * From TableNAme Where HangTag ='" &
Me.HangTag & "' And [Year] =" & Year(Date()))
If Not MyRec.Eof Then
Me.LastName = MyRec!LastName
Me.FirstName = MyRec!FirstName
Me.Division = MyRec!Division
End If

Exit_HangTag_AfterUpdate:
Exit Sub

Err_HangTag_AfterUpdate:
MsgBox Err.Description
Resume Exit_HangTag_AfterUpdate

End Sub