Combobox wizard glitch

[Mark M Simonian]

The combobox wizard is confusing me. I am using a multiple tabbed form. One
tab works beautifully but I can't repeat the lookup combowizard to duplicate
its action on another tab.

I am running Access 2002 and trying to use the combox wizard to match an
entry in the dropdown and update the corresponding record in the form. My
form's record source is a query called qryMemberRecord.

Using the third wizard choice to find a record on the form I select the last
name, first name and ID number which is a unique number (text field). It
works perfectly but when there is a duplicate last name it stops at the
first choice. I need to bind the property to the unique ID number which is a
text field but I can't figure out how and get the wizard to update the form.

I have done this on another tab in the same form and I can see that the
bound field is the ID text field number. If I repeat the wizard on the first
tab on the form and change the bound properties to the ID number in the
combo box instead of the default Last name on the wizard it doesn't update
the form at all.

If I pick the first wizard to find a unique record then the form doesn't
update. It was so long ago that I did this I can't remember how I got this
to work. The wizard works fine as long as the last name is unique but if
there are duplicate last name it doesn't update the form beyond the first
duplicate last name. The answer here is simple I am missing it.

Can anyone jog my memory?
-----------------------------------------------------
Mark M Simonian MD
Medical Director, ChildNet Medical Assoc.
681 Clovis Medical Center Drive West #106
Clovis, CA 93611
(559) 325-6850
(e-mail address removed)
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.

 

[John Vinson]

The combobox wizard is confusing me. I am using a multiple tabbed form. One
tab works beautifully but I can't repeat the lookup combowizard to duplicate
its action on another tab.

It may be that the tab control is confusing you. The Tab control is
just a way to make controls share screen space - it has NO logical
connection with the data; a combo box on a tab control is just a combo
box on the Form in almost all cases (the tab order is about the only
exception).

I am running Access 2002 and trying to use the combox wizard to match an
entry in the dropdown and update the corresponding record in the form. My
form's record source is a query called qryMemberRecord.

Using the third wizard choice to find a record on the form I select the last
name, first name and ID number which is a unique number (text field). It
works perfectly but when there is a duplicate last name it stops at the
first choice. I need to bind the property to the unique ID number which is a
text field but I can't figure out how and get the wizard to update the form.

Please post your code. What's the Control Source of the combo? (It
should be the field you're trying to update). What's the Row Source
query (post the SQL)? What's the Bound Column of the combo?

I have done this on another tab in the same form and I can see that the
bound field is the ID text field number.

I'm confused; is the ID a text field (containing numeric characters)
or is it a Number field?

If I repeat the wizard on the first
tab on the form and change the bound properties to the ID number in the
combo box instead of the default Last name on the wizard it doesn't update
the form at all.

Do you get an error message?

If I pick the first wizard to find a unique record then the form doesn't
update.

It isn't designed to do so: if you want to *FIND* a record, then you
don't want to update the record that happens to be on the screen when
you start the find!

 

[Mark M Simonian]

Thanks for trying to help. I have used the combobox wizard many times on
tabbed forms previously where I wanted the user to find the correct record-
usually a last name on multiple tabs with each tab having different
information for the member. So I needed to use the wizard on each tab that I
wanted to look up the last name.

Row Source of the unbound Combobox:
SELECT qryMemberRoster.LastName, qryMemberRoster.FirstName,
qryMemberRoster.RecordID
FROM qryMemberRoster;
These comboboxes are generally unbound looking to use the update property
where the user Clicks to find something that will be updated on the form.

Sometimes I use a number field as the key ID but in this case it is a text
field.

There is no error message with its use. If there are three Jones it always
picks the first Jones even though each query includes a unique ID text value
writen as a number 123654 I used a text field because the user might want to
use a temporary ID until they find out the real ID for the user.

In the end, one tab had a lookup combobox that worked still. I used the
wizard but copied the [Event procedure] in the After update which looks like
this:

Private Sub Combo469_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = '" & Me![Combo469] & "'"
Me.Bookmark = rs.Bookmark
End Sub

I just updated the control naming Combo464_AfterUpdate()
and updated it to the new control number 469 =Combo464_AfterUpdate()
It worked. I am just not sure why the wizard hadn't worked this time when it
always has in the past.
-----------------------------------------------------
Mark M Simonian MD
Medical Director, ChildNet Medical Assoc.
681 Clovis Medical Center Drive West #106
Clovis, CA 93611
(559) 325-6850
(e-mail address removed)
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.