A Form-SubForm with nested combo boxes (1 Viewer)

[silversun]

Hi
I have a master/child form as invoice and invoice_details respectively. I've created a nested combo box that resides inside the subform. This pair of combo boxes are taking their values from two tables. combo_item shows a list of all items and combo_units lists measuring unit of each item. In combo_items AfterUpdate event I have a macro to re-query combo_units.
In query of combo_units I have item_id taken from combo_item in "Criteria" row ([Forms]![T_invoice_details subform]![combo_items]) in order to filter all units that have a related value in table items.
When I test this configuration in a standalone form it works fine but when this nested combo boxes are tested in my form/subform setup it gives me a wired error as you can see in the image bellow. I have this Parameter Value in my criteria row. Please see the second image.

Here is query criteria of combo_units

I hope you understand my writing. Please let me know if you need more info or if you know the trick... (
Thanks in advance

 

[arnelgp]

the T_Invoice_Details subform is now a Subform of theMainFormName, therefore
you change the parameter to

[Forms]![theMainFormName]![T_invoice_details subform].[Form]![combo_items]

 

[silversun]

the T_Invoice_Details subform is now a Subform of theMainFormName, therefore
you change the parameter to

[Forms]![theMainFormName]![T_invoice_details subform].[Form]![combo_items]

@ arnelgp
Thanks for your respond.
I changed my parameter accordingly and the error is not coming up anymore D) but my combo_units doesn't take any click. I mean when I click on filtered items listed in combo_units nothing happens.
Here is my modified parameter:
[Forms]![T_invoice]![T_invoice_details subform].[Form]![combo_items]
Please confirm that the "Form" in red bold is correct.
Thanks

 

[silversun]

the T_Invoice_Details subform is now a Subform of theMainFormName, therefore
you change the parameter to

[Forms]![theMainFormName]![T_invoice_details subform].[Form]![combo_items]

Hi,
Should I start a new thread?
I still have issues with my combobox. Please let me know.
Thanks

 

[June7]

This is called cascading combobox and is a very common topic.

Are you using a query object as source for combo_units? If you put SQL statement directly in RowSource property it can be:

SELECT ID, unit FROM Units WHERE Item_ID = [combo_Items];

This will work whether form is a subform or not. Still need code to Requery and might need it in several events.

Why does Units have ID and Unit_ID fields?

Be aware that cascading combobox with alias will not work nicely with continuous or datasheet form.

 

[arnelgp]

check this sample.

 

[silversun]

check this sample.

Thanks for the answer.
I used your method but unfortunately I couldn't make it work. I copied all your codes and modified its variables and objects accordingly to my tables and fields. The only step I skipped was the invoice date/number generator because I was writing the invoice date manually and had no invoice number in my parent form.
I have my database attached here. Could you please look at it and help me to solve the issue? I appreciate it.

 

[June7]

Can't have an expression in combobox ControlSource. Need unit_id field from T_Invoice_details in subform RecordSource. Could just reference table as RecordSource then you know all fields will be available.

Then bind combobox to that field.

Change TabIndex for subform controls.

Why have default value for combo_items?

Suggest not having default values set on fields in table.

T_invoice should not have total_paid field. This should be calculated from details when needed.

Subform is allowing record entry when there is no record on main form and invoice_ID is not captured by subform record. This should not happen. I don't understand why this is happening

 

[arnelgp]

here, test it.

 

[silversun]

here, test it.

It works perfectly fine. Thank you very much. I will have to spend time studying your codes because there are some parts that are different from your sample.
Anyhow, I appreciate your help.