Combo box based on another combo but repeats initial input

[Korrel]

I'm trying to create a time registration database. For that the user selects
himself on a form plus a date entered. Then on a subform they can enter the
date they worked and what they did. The work is specified by first using a
WorkType (Combo A) and next a WorkCode (Combo B). I have the two combo boxes
on the subform based on each other. Selecting a value from Combo A gives me a
specific list in Combo B.
Now when I fill in the first record, a second record is immediately created
and fills in the same WorkType as on the previous record. Changing the
WorkType on the second record changes also the first record to the same
Worktype and so on.

 

[Sprinks]

Korrel,

Is your combo box Bound to an underlying field? In a continuous subform, an
unbound control can only have a single value for all records.

However, you have another issue of using cascading combo boxes on a
continuous subform. A combo box can only have a single RowSource property.
Say you are filtering Cities by the State they are in. You enter MA in the
first combo, and Boston in the second. In the next record, say you enter IL.
Your AfterUpdate code changes the RowSource property of the 2nd combo box.
Assuming there is no Boston, IL, the RowSource doesn't include this city in
its drop-down list, and therefore doesn't no what to do with the previous
record, so the data disappears!

The workaround is to pop up a form with the second combo box, and write the
selection to a textbox on your form.

Hope that helps.
Sprinks

 

[Korrel]

Thanks Sprinks,

I'll look into the possibility for a pop up, never done that before.

Cheers,

Ronald Korrel

 

[Sprinks]

Korrel,

I forgot to mention that the pop-up should be opened in dialog mode. I do
it in the OnGotFocus event of the textbox. After the selection is made, the
code continues at the last line, setting the focus to the next control:

DoCmd.OpenForm _
FormName:="YourPopupForm", _
View:=acNormal, _
WindowMode:=acDialog
Me!YourNextControl.SetFocus

Set the textbox value on your main form in the popup combo's AfterUpdate
event and close the popup:

Forms!YourForm!YourTextbox = Me!YourPopupCombo
DoCmd.Close

Hope that helps.
Sprinks