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
--
Good Luck
BS"D
"Chuck216" wrote:
> 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
>
|
Similar Threads
