Link listbox to form current record

  • Thread starter Thread starter Richard M. Hartman
  • Start date Start date
R

Richard M. Hartman

I've found examples that do this one way, with
either lists or combo boxes. For example, this:
http://allenbrowne.com/ser-03.html

Which clones the form recordset, finds the
item in it based on the selection, then
sets the form bookmart to match the clone
recordset bookmark.

Ok, that's half the job. When you select
something on the listbox, the form moves
to the matching record.

How about t'other way 'round? When you
used the record navigation buttons on the
form how do you update the current selection
in the listbox (or combobox)?
 
Something like the following in the Form_CurrentEvent (untested):
Me.MyListbox = ItemFromCurrentRecord.Value

HTH,
 
Richard,

The rowsourse of the listbox(or combobox) needs to be a query or SQL with a
criteria in one of the fields that relates to a field on your form like
Forms!MyForm!MyField. When you use the record navigation buttons on the
form, the selections in the listbox (or combobox) will automatically change.
 
Sorry to be slow on the uptake, but its Friday :-)

How exactly would that prevent the "list" from being reduced to a single
item whenever the record changes (and breaking the functionality described
in the 1st part of the post)?
 
Say you have a listbox that lists customers by state. For the row source you
need something like:
Select CustomerID, CustomerName, State From TblMyCustomers Where _
State = Forms!MyForm!State

Then if you navigate to a record that has State as PA, the listbox will
requery and list only customers in PA. If Clicking the next navigation
button grings up a record where State is FL, the listbox will requery again
and list custoers only in FL. So it depends on how many customers are in
TblMyCustomers in the State that comes up as to whether you get one
selection in the listbox or multiple selections to choose from.
 
I still think you are answering a different question than what was asked.
Please re-read the OP.

Lets say the user has an unbound listbox with all Cust Numbers. When a
CustNum is selected, RecordsetClone & Bookmarks are used to move the form to
the specified customer record. **The OP has this part working**. The OP
wants to know how to do the inverse: when moving to the "next" record via
navigation, how to have the unbound Listbox reflect the movement to the
"current" customer.

You are changing the list of customers. I don't see that the OP wants that
list changed, just wants the "selected" item synchronized with the form.

....but your answer would be great for a different question :-)
 
Sorry I didn't check over the weekend. You guys have
been having a nice discussion without me.

Yes, George, you have it correct.

Moreover, if the listbox and form source are in sync,
you would not be able to "next" from a customer in
CA to a customer in FL anyway.

The listbox and form have the same dataset (I actually
have a text field that I allow the user to enter a filter
in, so that they can type "G" then "e" then "o" and narrow
down the listed records to make it easier to find "George".
As that text field changes I requery both the listbox and
the form to reload the list. But if there are 17 records in
the listbox, there will also be 17 for the form to traverse
with the navigation buttons.

Your initial response makes it seem that the "Current"
event is the one I need to trigger off of to update the
form selection. I will try this. Thank you.
 
Back
Top