Combo Linked To Combo To Open Table

[Aussie60]

Hi can someone tell me how I can Link 1 combo box to another to open a table.

I have got 1 combobox that has project numbers in it. What I would like to happen is once you select a project number you can then go to the next combo box and select which table you would like to open in dataview so it only shows data relating to that project number.

Also I have a query to populate a subform based on a project number selection from combo box (combo36) What is the formula to put in the criteria field of the project number column.

This is what I believe it should be Forms![frmProjectInfo]![combo36] but when I use this it asks for a Project Number parameter

 

[Aussie60]

What i am trying to do is have 1 combo box populate the second combo box which would then open a table based on the selection. Can someone help me achieve this

Combo box 1 has a selection of job numbers in it, combo box 2 would have a selection of tables which can be open to show records in dataview read only which are based on the selection of the job number in combo box 1

On the same form combo box 1 runs a query to populate a subform based on the project number selected. The formula i have used in the job number column in criteria is Forms![frmProjectInfo]![combo36].
But when I select a job number in the combo box it ask for the Forms![frmProjectInfo]![combo36] parameter I don't know what I am doing wrong

 

[John Big Booty]

You've asked essentially the same question in two threads using slightly different wording so I have merged them into the one thread.

First you need to set up a cascading combo box set.

Your criteria in your query should be correct but what you will need to do is force the Subform to Requery On Current and whenever your combo changes. The following should do the trick;

Me![COLOR="Magenta"]YourSubFormName[/COLOR].Form.Requery

Simply change the highlighted section to reflect the reality of your sub form Name.

Bookmark this link for future reference, it contains the correct syntax for referring to sub forms their properties and controls from various relative locations.

 

[Aussie60]

John

Where exactly do I insert this code

 

[John Big Booty]

The Form's On Current event and the Combo's On Change event.

Click in the event you want, and click on the button with the ellipsis (that's three full stops in a row) and paste the code into the VBA window that opens below the event you have chosen.

 

[Aussie60]

John

I have entered the code into the subforms (tblEmployeeHourssubform) (tblMaterialssubform) (tblContractorsAmount) which I have on my main form (frmProjectInfo) this all seems to work fine.

But when I enter the following code into the combo on change event, it comes up with error Run-time error '2465': Microsoft Office Access can't find the frmProjectInfo refered to in your expression

Private Sub Combo20_Change()
Me!frmProjectInfo.Form.Requery
End Sub

 

[John Big Booty]

Have you changed the name of the Sub-form holder?

Generally this will have the same name as the subform it holds. However if you have changed the name of the subform holder this is the name you will need in your code.

 

[Aussie60]

John

I am getting a bit confused so I have attached my DB so you can see what I am talking about.

I have several problems which I hope you can talk me through so I can make the changes so I can learn at the same time.

1. When clicking on the combobox it shows 2 projects however both projects are showing the same project number. When I created the first project I gave it project no 100 and the second project no 200. I can go into the tblProjectInfo and change it to 200 but it keeps changing back to 100.

2. How do I update the DLookup queries in text boxes CVO Total , CVO Labour, CVO Materials, CVO Profit, Actual Labour, Actual Materials, Actual Profit after additional information is added to the tables through forms.

If i need to post another thread for the second question please let me know.

 

[Bob Vickers]

This is the same problem as the one that appears in my posting. I get the "Run-time error '2465': Microsoft Office Access can't find ..." like Aussie.

I have cascading forms: MAIN which holds subfCategory which hold subfProduct. I have put the following in the ComboBox, On-change Box: Me!subfProduct.Form.Requery.

Not sure what a sub-form holder is and how do I check its name? I tried putting in subfCatagory as this the parent of subfProduct - but still get the 2465 error.

 

[John Big Booty]

Your Code should look something like;

Private Sub Combo20_Change()
Me!tblEmployeeHourssubform.Form.Requery
Me!tblMaterialssubform.Form.Requery
Me!tblContractorsAmount.Form.Requery
End Sub

As you want to requery the subforms not the main form.

 

[John Big Booty]

John

...

1. When clicking on the combobox it shows 2 projects however both projects are showing the same project number. When I created the first project I gave it project no 100 and the second project no 200. I can go into the tblProjectInfo and change it to 200 but it keeps changing back to 100.

...

I suspect you need to read up on Data Normalisation and also the operation of Combo boxes, as you current set up does not make a lot of sense in my eyes.

 

[Aussie60]

John

I placed the code in the combobox on change event but it is still showing the run-time error can't find tblEmployeeHourssubform.

In regards to my database not making a lot of sense, this is what I want to happen.

Can you help me get to this point.

Select a project number from the combo box that populates text boxes with the project number, project name, builders name, order no, and quoted amount.And also populate the subforms, tblEmployeeHourssubform, tblMaterialssubform and tblContractorsAmountsubform with data associated with that job number.

The other textbox data I will sort out later.