Linking Combo boxes

[striker]

I want to put two combo boxes on a form which are linked to different fields in the same table so that you can filter the table.

What I actually want to achieve is that if I select an item in combo box A then combo box B displays the correct corresponding data from the table, and if I then subsequently select an item with combo box B then combo box A shows the correct corresponding data and not some spurious rubbish from the last filter.

I have seen this done on before but can't remember where or how.

Any ideas?

 

[David R]

Look up 'cascad combo box' in the archives.

 

[llkhoutx]

One data in one combobox is used as the criteria in a query for the second.

Requery the second, after updating the first.

 

[striker]

I may not have explained myself very well. The two combo boxes are based on fields which have unique values. The value selected in combobox A will only have one record in the table.

What I actually want to happen is that when I select the value in Combobox A the value shown in combobox B updates to match the value from the underlying table. I also want this to happen to combobox A when I select a value from Combobox B.

Thanks again.

 

[David R]

Okay, that's a slightly different question. I'm not sure I understand the why-and-wherefore of it, but...

You should be able to use the code for cascading combo boxes in both directions, since Access doesn't care what order they're in. Make sure you don't get stuck in a code loop though (before you start your code, check to see if they match already, otherwise changing A will change B which will change A which will change B which will...you get the idea).

My other question, and maybe an example case will clarify this, is are you storing both of these pieces of data? If one determines the other you should only store one of them, but maybe you know that already. Or consider using a multi-field combo box, with both fields in it and display the second in an unbound text box. Post back if you think I'm speaking Gaelic.

 

[striker]

an example of what I want to do.

I have an internal phone book table which comprises three pieces of data. Surname, Post Title and Phone number. I want to be able to find the phone number by using a dropdown list on either surname or post title. However if I use the drop down on post title I want the surname field to show the correct name. I also want it to work the other way round, ie select the surname from drop down list and the post title and phone number update to show the correct values.

I know it can be done I seen it.

 

[David R]

Okay, looking up a record is a completely different situation from updating a record.

Have you worked with recordsets and bookmarks before? You can create lookup fields on your form to search any of those fields. You could use Ctrl-F to use the Find dialog to look for them. You could use a popup form and filter your records down to just the one you want.

Each one has advantages and disadvantages. If you have a preference but can't find examples, post back and someone will be able to help you.

 

[striker]

David,

I've created the popup form because I hate using ctrl F its two many key strokes or clicks and just looks naff.

I'd prefer to do it with the combo boxes. Ive created the combo boxes but all I want is the info in the combo box I didn't use to select the data to keep track with the combo box that I did use.

I do have some limited experience with using recordsets and bookmarks.

 

[David R]

I think that you're not really wanting to update the fields, but instead jump to the desired record. In that case, try this topic, particularly the Knowledge Base article, "How to Use a Bound Field to Find a Record". http://www.access-programmers.co.uk/ubb/Forum4/HTML/005371.html