How do I correctly bind a combobox on a subform to changes on the main form

[WallyZ]

Given my following Task database design. Where each task has many Stages. I have used the form wizard to build a simple form and subform.





I have added a combobox to the subform to allow selection of the required stage. However regardless of the task chosen on the main form the combobox on the subform
always contains the set of stages for the first Task record. The first task 59615 contains stages 1,2,4. Task 60015 & 61015 only contain stage 1. Regardless of what task
I select the combobox always shows stages 1,2,4. As shown here:



The Control Source is unbound because I'm not sure what to bind it to! And there is no requirement for any change in the combobox to be written back to the DB.



The Record Source is based on qryTaskStage. Three columns with hidden first column and unshown third column for criteria.



And the criteria I have chosen is the txtTaskNo control from the main form.



How should I actually do this correctly so any change on the Task No on the main form returns the correct records for the combobox on the subform?

[ranman256]

If your subform.combo is dependent on the Master form, then you must requery the combo on each change in MAster record.

if master form gets master.id = 60015
me.ONCURRENT () event
sub.combobox.requery 'refresh combo


combobox sql =
select [code] from table where [event] = " & masterform.code

THE ABOVE CODE, will reload the combobox with new data since it is depending on a value from the master (or child) . This will limit the values in the combo with new data.
Everytime you go to a new MASTER record, the ON CURRENT event will fires a refresh of the combo data.

[WallyZ]

Ok done that now the moment the refresh is called I get this

Run time error 3022:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change he data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Surprising since I'm selecting an existing value from the combobox that I know already exists!!! What new record does Access think I'm trying to add/create???

It seems that Access requires an extraordinary amount of background coding to support a combo box. In Filemaker all I did was drag the equivalent onto the form and it just worked. This very simple example took <5 minutes on Filemaker to work. Two weeks later the same example in Access isn't going. And by the way the final solution in Filemaker is sexy, the best I can say about Access is very retro 80's dude!

[WallyZ]

How I fixed this:

step 1) Unbind the ComboBoxName Control Source = ""
step 2) ComboBoxName After Update = Event Procedure...; Me.Recordset.FindFirst "IDnamegoeshere = " & CStr(Me.ComboBoxName.Value)
step 3) Subform On Enter = Event Procedure...;
dim cb as ComboBox
subFormName.Form.Requery
subFormName.Form.Refresh
set cb = subFormName.Form.Controls("ComboBoxName")
if cb.ListCount >= 0 then cb.Value = cb.ItemData(0) ! Optional - Selects first item in ComboBox