Date lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chuck216 - 21 May 2007 21:41 GMT

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

Reply to this Message

Ofer Cohen - 21 May 2007 21:54 GMT

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

Signature

Good Luck
BS"D

> Hi
>
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message