Same question different day

[doublecypher]

Hello all,
I first would like to apologize as I am sure this question has been asked in the past. I have found many many posts addressing similar problems however , none seem to hit exactly what I am trying to accomplish. My very limited programming knowledge compounds the issue and makes it hard to apply other solutions that may be close but not exact.
I have a form with five combo boxes all set up to search for information in different fields on the same table (5 fields in 1 table). The screen capture I included is the form I am working on and it’s intended function is to simply look up a “client” with any one field (combo box). The current macro does this for me but for each combo box independently and they need to be linked. I will always need the “Band Name” combo box (table key) to be populated and would like the others to auto populate once the desired record is found . Example: if the only piece of information I have on a client is say the “First name”, i currently can find his record using the frist name in its respective combo box, but once found it needs to populate all the other combo boxes with that records information.
Hope this makes sense and any help is greatly appreciated.

 

[CameronM]

I think you may need to rethink your search process. Firstly, the easiest way to have a form display data related to a specific record is to make the form data-bound. That means that every combobox relates to a field in a table. It also means that when you select a value from one combobox you are actually changing the underlying record (not always a good idea).

It is usual that a search form, such as the one you are creating is not bound to a table, but processes the user's selection and returns the required results, perhaps in a sub-form or a seperate form.

You can certainly 'filter' the different comboboxes in the search form so that if the user selects Firstname = Bob, the Lastname combobox shows only the Lastnames of people call Bob.

This is achieved by setting the RowSource for the Lastname combobox to something like

SELECT Lastname FROM Clients WHERE FirstName = [Forms]![Form1]![cboFirstName]

You then write code in the AfterUpdate event of the FirstName combobox that requeries the Lastname combo

Me.cboLastName.Requery

When the user has entered as many fields as they can, they can hit a button labelled 'Find' to run a query and find the records - remembering there may be more than one Bob Smith, so we may need to show the user all the matching records so they can select the correct one.

 

[doublecypher]

Thank you for the reply. I am trying out what you suggested now and will let you know if I get it to work .
To the reference of rethinking my search process, what would you recommend?
The intent here is to be able to pull a desired record from any single known field so I am not chained to needing the “key field” data (don’t you hate it when you are dealing with a bank or similar institution and ask them “I don’t have that with me but can you pull up the record with my social or transaction number or any one of a million other possibilities” and they reply” no, only with the X number, my system won’t let me any other way”). I am trying to keep this to one form and not use sub forms. And most importantly you may have noticed the “go to client profile” button; it uses a filter based off the “band name” field to populate a new form with information from a different but related table. I am envisioning something to the effect of 1. clicking the combo box containing the information I have on a client 2. (instead of a button labeled find) “on mouse move” as it highlights that information it also momentarily populates the other combo boxes and helps me find the correct record (me as the user becomes a part of the “query” process evaluating the data presented in the other fields) 3. “onclick” permanently populates the fields and the form is now set up so when the “go to profile” button is hit those filters associated with it populates the next form. I have this concept working with a similar form set up to “create new clients”. You populate the same 5 fields and when you hit the “go to profile” button it creates the record, applies the filter and proceeds to the next form correctly populated. It seems clean and intuitive, at least in my mind but I may be asking too much from access.
Thank you again for all the help!

 

[CameronM]

Do you have a reason why you do not want to use a subform? I still think that it will be more efficient for the end-user to enter the information they have, such as a firstname, and see the matching results on the screen so that they can choose the correct record. For example, if 2 Bob Smiths are found, the subform could show the address as well so that the user can ask Bob where he lives to make the match.

I would set up my combos to requery the subform using AfterUpdate, that way as the user enters/selects values in each combo, the results become narrower - getting close to the person they actually want.

You say you want to make it easy to find records, yet at the moment I see your search form as being very restrictive and if you have more than a few records, it will be an absolute nightmare to find anyone. BUT, it's your database.