Combo boxes

[Philbot]

I have read the cascading combo box solution and would like to know if this would help me with my specific solution.

I have a subform that is used to populate a table which is later used to cost each row by linking to a costing table (see later).

3 off the columns in this form have been set up as combo boxes e.g.

(1)Supplier/(2)Product type/(3)Product 2 is dependant on 1 and 3 is dependant on 1 & 2

These combo boxes currently refer to my costing table to ensure that the user can only enter a combination that is in this table so that when I compare the data entered in my subform table these combinations will be present in the costing table.

I get the 2nd combo box to show only the product types available for the selected supplier by afterupdate me![Product type]requery on the 1st combo box

The 3rd combo box is dependant on both 1 & 2 so I have applied afterupdate me![product]requery which has filters in the query as follows
Forms![subform]![Supplier]
Forms![subform]![Product type]

This seems to work ok when entering data initially into the form which places it directly into my table

The problem I have is when someone later goes into a record and changes combo box 1 and the data in combo 2 & 3 may now not be an option so how can I force them to go and re-visit these filed before exiting the form?

The second problem is if they go to the next record in the form and select the 3rd combo box to edit the field the qry does not show the correct option and shows the ones for the previous row?

Any suggestions please..

 

[MStef]

Hello Philbot!
Look at Demo4CBoxesA97.mdb
(MStef alias Štef)

 

[Philbot]

This look very helpful to me as it clears the fields in the combo box but will it also clear the field from my table that is used to populate the form when it opens?

Basically I have combo boxes attached to the table data and a separate table is used to populate the list in combo boxes i.e. I have a control source also on these fields that is how the data re-appears when the form is opened.

Everything is fine when entering each row of data from the initial input but users may re-visit the form and select one of the rows to update.
Its ok if they start at combo 1 and I clear contents of 2 & 3 but I am actually looking at table data in 2 and 3 on a re-visit to form?

And I think I will need a check if either 1,2,,3 ="" then msg not populated before exiting form.

Sorry its quite difficult to explain really ...

 

[MStef]

Hello Philbot!
I understand what youe mean, but it's normal.
If a user change data in Combo1, he must enter
new data in Combo2 and Combo3, data which
correspond to new data in Comob1.

 

[Philbot]

Got it sorted now thank you had to use this for combo box 1 always get mixed up as to when to use Me! or Me. I generally jsut try both until it works lol but much appreciated.

Private Sub Pipe_Cat_AfterUpdate()
Me![Sch Size] = " "
Me![Surface Cat] = " "
Me![Sch Size].Requery
Me![Surface Cat].Requery
End Sub

 

[JimiH]

Hello

I changed the data in table1 in Demo4CBoxesA97.mdb, now when I've selected data from Combo1 & Combo2 there is only 1 selection left within
combo3 & combo4. So I would like to change the combo's to textboxes
and have them auto populate based on the selection in combo1 & 2.

I hope this makes sense

Geoff