Using A Dlookup Function On A Form When A Combo Box is Updated

[nouveauricheinvestments]

Hi,

I have a combo box. This combo box looks up dates in a query. I have
the following code in the afterupdate event procedure. When I do
this, it tells me "Invalid Use Of null". I don't understand what the
problem could be. I use the dlookup function in like 30 other places
in my database...Does anyone have any ideas?

Also, you should know I had set the control source to this dlookup
value previously but it was blank so I thought I would try doing it
via code. No luck, so here I am, posting.

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Integer
ID = DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]",
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]")
Me!TSID = ID
Me!UnappTS.Requery: Me!UnappTS.Visible = True

End Sub

 

[Douglas J. Steele]

DLookup returns Null if no records are found matching the criteria. The only
data type that can hold a value of Null is the Variant.

Try:

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Variant

ID = DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]", _
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]")
If IsNull(ID) = False Then
Me!TSID = ID
Me!UnappTS.Requery
Me!UnappTS.Visible = True
End If

End Sub

or

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Integer

ID = Nz(DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]", _
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]"), -1)
If ID > 0 Then
Me!TSID = ID
Me!UnappTS.Requery
Me!UnappTS.Visible = True
End If

End Sub

Incidentally, are you sure that Integer is an appropriate choice? The
largest value an Integer can hold is 32,767.

 

[nouveauricheinvestments]

DLookup returns Null if no records are found matching the criteria. The only
data type that can hold a value of Null is the Variant.

Try:

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Variant

ID = DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]", _
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]")
If IsNull(ID) = False Then
Me!TSID = ID
Me!UnappTS.Requery
Me!UnappTS.Visible = True
End If

End Sub

or

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Integer

ID = Nz(DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]", _
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]"), -1)
If ID > 0 Then
Me!TSID = ID
Me!UnappTS.Requery
Me!UnappTS.Visible = True
End If

End Sub

Incidentally, are you sure that Integer is an appropriate choice? The
largest value an Integer can hold is 32,767.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi,

I have a combo box. This combo box looks up dates in a query. I have
the following code in the afterupdate event procedure. When I do
this, it tells me "Invalid Use Of null". I don't understand what the
problem could be. I use the dlookup function in like 30 other places
in my database...Does anyone have any ideas?

Also, you should know I had set the control source to this dlookup
value previously but it was blank so I thought I would try doing it
via code. No luck, so here I am, posting.

Private Sub SelectUnAppTicket_AfterUpdate()
Dim ID As Integer
ID = DLookup("[TradeSpecialist_ID]", "[UnapprovedTickets]",
"[SelectUnAppTicket] =[UnapprovedTickets]![Start Time]")
Me!TSID = ID
Me!UnappTS.Requery: Me!UnappTS.Visible = True

End Sub

Yeah I don't need anything that big. Integer is just fine. I'm
looking up a primary key in an employee table. I actually just
changed the record source of my combo box. Something about the date
was screwing things up because I am looking things up by the account
now and it's working fine.