Dependent combo box problems...

[commanderrico]

Ok, I have two tables in my db. One has different turning lines and each line has the specific machines that are on the line. My other table is for the downtime of those machines where the user will input the date, employee, shift, problem, what line it was on, what machine, etc. So what I want to do is have combo boxes on the form to choose the line and the machine. The first combo box would have the line in it and then the next would, depending on what line you picked, bring up the machines on that line. In other words, the combo boxes are referencing the information in the first table and I want the information they reference to be entered in the second table. The code in the first and second combo boxes are as follows:

First Combo Box:
SELECT [Mac Dept LO/TO Proc].Line FROM [Mac Dept LO/TO Proc] GROUP BY [Mac Dept LO/TO Proc].Line ORDER BY [Mac Dept LO/TO Proc].Line;
Second Combo Box:
SELECT [Mac Dept LO/TO Proc].[Specific Equipment] FROM [Mac Dept LO/TO Proc] GROUP BY [Mac Dept LO/TO Proc].[Specific Equipment], [Mac Dept LO/TO Proc].[Specific Equipment] HAVING (((Mac Dept LO/TO Proc.Line)=[Forms]![Data Entry]![cbxLine])) ORDER BY [Mac Dept LO/TO Proc].[Specific Equipment];

When I try to run the form as it is it asks for the parameters for [Mac Dept LO/TO Proc.Line] How can I make the combo boxes reference one table but enter the information in another? Thanks for your help.

Rico

 

[scriverb]

Use this as your SQL for the second combo box:
Second Combo Box:
SELECT [Mac Dept LO/TO Proc].[Specific Equipment] FROM [Mac Dept LO/TO Proc] GROUP BY [Mac Dept LO/TO Proc].[Specific Equipment], [Mac Dept LO/TO Proc].[Specific Equipment] HAVING ((([Mac Dept LO/TO Proc].[Line])=[Forms]![Data Entry]![cbxLine])) ORDER BY [Mac Dept LO/TO Proc].[Specific Equipment];

Make sure that you perform a requery of the second combobox in the AfterUpdate event procedure of the first combobox.

Post back if you have any questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[commanderrico]

I reworked the SQL like you said aboove and I requeried the second combo box afterupdate on the first as follows:

Private Sub cbxLine_AfterUpdate()
cbxMachine.Requery
End Sub

But when I run the form it still asks me for parameter values for Mac Dept LO/TO Proc.Line. The name of my other table is Machining Downtime. Do I need to put that somewhere in the programming? Are there any other event procedures I need to do? Machining Downtime is where I want the info to end up while the combo boxes reference the other table. Thanks for you help.

Rico

 

[scriverb]

I am sorry, I didn't even look at the specifics of the SQL in my first review. I was only looking for the syntax problems. But, in your second combobox SQL you want to be accessing your table [Machine Downtime]. The value from the combobox1 pick will be used in the WHERE clause. You will have to rework the second SQL to have the FROM clause be this table and the fields displayed should be from this table also.

Take a try at redoing the SQL and post back with the SQL pasted in. I will then take a look at that time.





Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[commanderrico]

Here's the SQL from the second combo box, not sure if this is right, this is my first time trying to do this:

SELECT [Mac Dept LO/TO Proc].[Specific Equipment] FROM [Machining Downtime] GROUP BY [Machining Department].[Machine], [Mac Dept LO/TO Proc].[Specific Equipment] HAVING ((([Mac Dept LO/TO Proc].[Line])=[Forms]![Data Entry]![cbxLine])) ORDER BY [Mac Dept LO/TO Proc].[Specific Equipment];

Do I need to change anything in the first combo box?
Thanks for your help

Rico

 

[scriverb]

SELECT [Mac Dept LO/TO Proc].[Specific Equipment] FROM [Machining Downtime] GROUP BY [Machining Department].[Machine], [Mac Dept LO/TO Proc].[Specific Equipment] HAVING ((([Mac Dept LO/TO Proc].[Line])=[Forms]![Data Entry]![cbxLine])) ORDER BY [Mac Dept LO/TO Proc].[Specific Equipment];

In this SQL you are only reading one table. [Machine Downtime] You therefore cannot reference the fields of another table. Thus the red code above. You must create this query solely from the fields of the table [Machine Downtime] without any reference to the other table. You can reference the combobox value to select the appropriate records which is what you ant to do here. Wh don't you post the key fields from table [Machine Downtime]. That will help.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[commanderrico]

I got it. I searched throught the forum and found an old post and tried it a different way and combined what you had told me with that info and it worked. Thanks for your help.

Rico