Solved Sync between Combo Box and SubForm (1 Viewer)

[mloucel]

Hello everyone,
I've been working on a simple form, but I can't determine why it's out of sync when it first opens.
For some reason, the selected record in my sub-form doesn't synchronize with the combo box record, but this only occurs upon opening.
If I reselect the record or any other record, it functions as intended; the issue is only at the start when the sub-form doesn't sync.
I've attached a test database, hoping someone can explain why and identify my mistake.

The Form I open is:
SpecialtySpecialistMaintF1

 

[MarkK]

Link the Subform.SpecialtyID field to the value of the MainForm.SpecialtyCombo.
1) open the main form in design view
2) open the properties window of the subform control
3) set Data Tab->LinkMasterFields to the name of the control, which is "SpecialtyCombo", not "SpecialtyID"
4) delete the AfterUpdate handler, because you no longer need it.
Now the rows presented in the subform will be automatically filtered to match the value in the selection control.

 

[arnelgp]

based on your original code, you need to modify SpecialtyCombo_AfterUpdate() event:

Private Sub SpecialtyCombo_AfterUpdate()
On Error GoTo SpecialtyCombo_AfterUpdate_Err

    
    DoCmd.SearchForRecord , "", acFirst, "[SpecialtyID] = " & Me.SpecialtyCombo

SpecialtyCombo_AfterUpdate_Exit:
    Exit Sub

SpecialtyCombo_AfterUpdate_Err:
    MsgBox Error$
    Resume SpecialtyCombo_AfterUpdate_Exit

End Sub

then you call this event when the Form Loads (Load event):

Private Sub Form_Load()
    Me.SpecialtyCombo.Requery
    Me.SpecialtyCombo = Me.SpecialtyCombo.ItemData(0)
    
    Call SpecialtyCombo_AfterUpdate
    
    '
End Sub

 

[mloucel]

based on your original code, you need to modify SpecialtyCombo_AfterUpdate() event:

Private Sub SpecialtyCombo_AfterUpdate()
On Error GoTo SpecialtyCombo_AfterUpdate_Err

   
    DoCmd.SearchForRecord , "", acFirst, "[SpecialtyID] = " & Me.SpecialtyCombo

SpecialtyCombo_AfterUpdate_Exit:
    Exit Sub

SpecialtyCombo_AfterUpdate_Err:
    MsgBox Error$
    Resume SpecialtyCombo_AfterUpdate_Exit

End Sub

then you call this event when the Form Loads (Load event):

Private Sub Form_Load()
    Me.SpecialtyCombo.Requery
    Me.SpecialtyCombo = Me.SpecialtyCombo.ItemData(0)
   
    Call SpecialtyCombo_AfterUpdate
   
    '
End Sub

Like Always, right on point, thou I have to be honest, I would have never figure that one out.
Thanks @arnelgp again..

 

[mloucel]

Link the Subform.SpecialtyID field to the value of the MainForm.SpecialtyCombo.
1) open the main form in design view
2) open the properties window of the subform control
3) set Data Tab->LinkMasterFields to the name of the control, which is "SpecialtyCombo", not "SpecialtyID"
4) delete the AfterUpdate handler, because you no longer need it.
Now the rows presented in the subform will be automatically filtered to match the value in the selection control.

Thank you but you cannot use the name of the control for the master field, has to be the PK ID, but thanks for your help.

 

[MarkK]

you cannot use the name of the control

Yes, you can. All the best.