# Solved: ms access select record with combo box not working!



## trcstuart (May 12, 2014)

Hi All,

I have a combo box on a form header that I'd like to set up so the user can select a specific record to be displayed in the form. I have successfully set these up in the past, but recently I added a sub form that required me to change the Record Source of the form so it uses two tables, not one. Since then I noticed my Combo box no longer works. I've searched the internet for specifics on who to fix this but come up short! Any help would be immensely appreciated - I'm so close to finishing the project that I can spit on the finish line!

Here are the deets:
Main form is called: 'Update Employee Profile' --> Data accessed by this form is stored on table 'Employee Information' --> Primary Key is 'Crew ID'
Subform is called 'Company Issue Equipment' --> Data accessed by this form is stored on table 'Company Issue Equipment' --> Primary Key is 'Electronic Equipment ID' (one to one relationship to 'Crew ID')

Currently the record Source on the main form is:

SELECT [Employee Information].*, [Company Issue Equipment].*
FROM [Employee Information] INNER JOIN [Company Issue Equipment] ON [Employee Information].[Crew ID] = [Company Issue Equipment].[Electronic Equipment ID];

I'd like the combo box list to show the following headings: Last Name, First Name, Classification - all found on the 'Employee Information Table' . I currently have a query set up with those three headings as well as 'Crew ID' from the 'Employee Information' table and 'Electronic Equipment ID' from the 'Company Issue Equipment' table.

Lastly, in the After Update Event of the Combo Box I have the SearchForRecord macro with the where condition: ="[Crew ID] = " & Str(Nz([Screen].[ActiveControl],0))
I think the condition is where I'm going wrong - but I'm not certain.

Again, thanks in advance for any help you can give!!

Cheers, 
Tyler


----------



## OBP (Mar 8, 2005)

Tyler, the easiest way to solve this is to create a new combo with the comb wizard and choose the last option.


----------



## trcstuart (May 12, 2014)

Hi OBP,

Thanks for your quick reply.

Unfortunately that won't accomplish what I'm looking for. That means I would have to redundantly and manually type in ALL the records (for the few fields that I need) that are already going to be in the database. What I want is for the user to be able to select a record from a drop down menu that populates itself from the existing records in the database. As I mentioned, I've set it up before with the wizard and it worked (using the first option), only difference is the record select was only retrieving information from one table and now my form (via a subform) needs to retrieve information from two tables. 

Currently, if I use the wizard and select the first option, and use my query, the list doesn't populate and is just blank. I think it's either my query that is wrong or the SearchForRecord Macro?

Thanks again for your help, 

Tyler


----------



## OBP (Mar 8, 2005)

Tyler, I am not sure why you would want to list from 2 tables, surely the list should only be on the Master table.
have you actually run the combo query to see how many records it returns?
What data does the second table hold?


----------



## trcstuart (May 12, 2014)

Hi OBP, 

I'm sorry for the long delay in my reply. 

For clarity, I don't need to list from 2 tables. The list all comes from the main table called 'Employee Information'. But once the appropriate record is selected, I need the form to then access the data from two different tables: 'Employee Information' and 'Company Issue Equipment'. The second table holds data on which equipment (laptop, cell phones, keys) an employee has been issued. 

I ran the query as posted above and it doesn't return any records which I why I'm pretty certain that it's my query that is wrong...

Thanks! 

Tyler


----------



## OBP (Mar 8, 2005)

Tyler, your query for the combo should only have the Employee Information Table in it, that table should be the Record Source for your Main Form.
The Company Issue Equipment data should be displayed on a Subform.

So your combo selects an Employee, which is displayed on the main form and the equipment for that employee is displayed on the subform.


----------



## trcstuart (May 12, 2014)

Ok! I think I see where the disconnect is. 
I have a form with a subform on it already. Both the form and MOST of the subform tabs store data in the same table ('Employee Information'). The issue is ONE tab of the subform, the 'Company Issue Equipment' tab, needs to store data in the 'Company Issue Equipment' table. So yes, my combo box only needs to show first names, last names and classification (which are all stored on one table), BUT once that record is selected, the forms need to access information from 2 tables. 

Can I do this? 

If so what might my query look like? I think that is where I am going wrong.
I've attached a screen cap to show you what I mean. 

Tyler


----------



## OBP (Mar 8, 2005)

The resolution to your equipment subform problem is not a query, it is the Master/Child links on the Subform.
The subform must have the EmployeeID field (or whatever the foreign key field is called) and the Master link is the ID on the Main form and Child link is the ID on the Subform.
So each time the main form record changes Access automatically changes the subfrom records to match.


----------



## trcstuart (May 12, 2014)

Hi OBP, 

Thanks so much for this solution. I'm sorry it took so long to reply (life happened - new baby, moving). With your help, after understanding this a little better I decided that the better solution would be to have all the data on one table. The data is close enough related to each other to justify it, and it makes life considerably easier going forward with reports etc. 

As always, thanks for your help. 
Cheers, 

Tyler


----------

