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

  • Thread starter Thread starter nouveauricheinvestments
  • Start date Start date
N

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
 
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.
 
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,
Click to expand...
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?
Click to expand...
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.
Click to expand...
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
Click to expand...
End Sub
Click to expand...
Click to expand...

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.
 
Back
Top