DLookup and #Error

[ScrmingWhisprs]

I have a Registration form for an Event Management database, and there are a couple of textboxes that use the DLookup function.

The first one named [EventLocation], which is not visible, has the following row source:

=DLookUp("[Location]","tblEvents","[EventID] =" & [Event])

This brings back the ID number of a Location from the Location table, based on the event selected in the [Event] combobox.

The second textbox named [tboLocation] with Rowsource:

=DLookUp("[LocationName]","tblLocation","[LocationID] =" & [EventLocation])

Looks at [EventLocation] and returns the Location name that matches the ID number from the Location table.

The problem is, until an Event is selected, tboLocation displays #Error. What can I do to display nothing until an Event is selected?

Let me know if you need more clarification. Thanks in advance.

ScrmingWhisprs

 

[spasticus]

try putting the code in the afterupdate sub of event.

so...

Private Sub EventCombo_AfterUpdate()
tboLocation.text = DLookUp("[LocationName]","tblLocation","[LocationID] =" & [EventLocation])
End Sub

 

[WayneRyan]

SW,

spasticus is right, but don't use --> tboLocation.text

The .Text is only available while a control is being edited.
Use --> tboLocation.Value or just tboLocation

And tboLocation should have an empty control source, not even a DLookup.

With the appropriate use of the Nz function, both inside and outside
your current DLookUp, you could make it work, but this is simpler.

hth,
Wayne

 

[RuralGuy]

I would fill *both* TextBoxes with code in the AfterUpdate event of the Event ComboBox. Remove the ControlSource from both TextBoxes and rely on the ComboBox alone. BTW, TextBoxes have a ControlSource but *no* RowSource.