DLookup

[Steven]

I want to look up two fields on the same record at one
time. Currently I have:

Dim vOne, vTwo as Integer
vOne = DLookup("[StopInput]","Deposits","BatchNum =
[Forms]![FrmDeposit]![tbBatNum]")

vTwo = DLookup("[StopAllocation]","Deposits","BatchNum =
[Forms]![FrmDeposit]![tbBatNum]")

Can I put these in one statement so I dont have to make
two passes on the table.

Thank you for your help.

 

[Graham Mandeno]

Hi Steven

It would be much more efficient to open a recordset and read the two values.
If you need to do it from several places in your code, then write your own
procedure to do the lookup:

Public Function MyLookup( BatchNum as long, StopInput as Integer, _
StopAllocation as Integer) as Boolean
Dim rs as DAO.Recordset
On Error goto ProcErr
Set rs = CurrentDb.OpenRecordset( _
"Select StopInput, StopAllocation from Deposits where BatchNum=" _
& BatchNum, dbOpenSnapShot)
With rs
If not .EOF then
StopInput = !StopInput
StopAllocation = !StopAllocation
MyLookup = True
End If
End With
ProcEnd:
On Error resume next
rs.Close
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

You can then call it like this:

If MyLookup( Me.tbBatNum, vOne, vTwo ) then ...

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Fredg]

No can do. One lookup per go