Unbound combobox doesn't work on first AfterUpdate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm unsuccessfully trying to implement a quick search on an unbound form for
finding a client's record that was previously entered. (The form is unbound
for better control of new client records and data checking before comitting
the data.) [MS Office Access 2003 SP2; Microsoft.Jet.OLEDB.4.0]

The ComboBox Row Source is
SELECT [Client Data].[Client AutoNum], [Last Name] & ", " & [First Name] AS
lastFirstName FROM [Client Data] ORDER BY [Last Name] & ", " & [First Name];
(with a blank Control Source)
Bound Column is set to 1

There is an After Update event procedure that basically uses the current
value of the combo box to locate the matching record in the Client Data table.

Set curDB = CurrentDb
cnn.Open Me.Controls("xProvider") & "data source=" & curDB.Name
rst.Open "Select * From [Client Data] Order by [Client AutoNum]", cnn,
adOpenStatic
rst.Find "[Client AutoNum] = " &
Str(Me.Controls("SearchClientName").Recordset.Fields.Item("Client AutoNum"))

What is strange is the very first time I open the form containing this combo
box, the Client AutoNum value returned is bogus, but then it works every time
after that.

I've tried various things to try to "prime the pump" to get past the first
bogus value, such as doing a requery on the combo box from the form load
routine, manually closing and reopening the recordset for the combo box
(again from the form routines). Sometimes these techniques had problems
because the recordset object didn't seem to exist right away. [To try and
gain some understanding I added a button to the form which did the combo box
requery. This actually worked to get past the first bogus value by pressing
it when the form was first opened, BUT it only worked if I first clicked on
the drop down arrow of the combo box before pressing the requery button. (My
guess here is the recordset is not actually created until the first time
someone clicks on the combo box--but that may not be quite true because the
combo box has keyboard focus when the form is first opened and if I start
typing a client's last name the auto complete has valid names in it.)

I'm stumped and looking for suggestions to fix the bogus value on the first
use of the combo box.

Thanks
 
Some further information...

The combo box item that appears to be selected the FIRST time the
AfterUpdate is called is ALWAYS the first combo box item, regardless of which
item appears to be selected when the AfterUpdate is triggered. It works every
time after that.
 
Back
Top