Combo box dependant on another combo box - Re faq702-4289 2

[cloverdog]

I have a requirement to develop a database that has a Master form with a combo box and a detail form with a second combo box. This arrangement reflects the one to many relationship of the tables underlying the forms.

I have been trying to understand the mechanics of combo boxes in making this arrangement work but just can’t get it and I have created the database given in the excellent post faq702-4289. Thank you to the author for providing this working sample!

Much as I have tried I cannot make the dependant combo work when I put it in a Subform (frmSub) on a Master form (frmMaster). I believe from what I have read that in this case the child form needs to Link directly to the Control on the Master form rather than to the form itself so that any changes on the Master form will be immediately reflected in the subform.

I would be grateful for any help in adjusting the post faq702-4289 to work in a Master-Detail way as described. I think the problem may be to do with addressing forms and controls and setting the form links. I am developing on Access 2003 and have attached the db as 2CBOboxesV2MAsterDetail.mdb.

Many thanks.

 

[MajP]

The after update of your parent form cboManufacturer, needs an event procedure. Get rid of what you have.

Private Sub cboManufacturer_AfterUpdate()
  Me.FrmSub.Form.CboModel.Requery
End Sub

the subform needs to reference the parent form

Private Sub CboModel_GotFocus()
    If Trim(Me.Parent.cboManufacturer & "") = "" Then
        MsgBox "Please Specify Manufacturer first"
        me.parent.cboManufacturer.SetFocus
    Else
        CboModel.Requery
    End If
End Sub

 

[cloverdog]

Thank you for your reply. I have added those changes and it is nearly there.

The only problem is on clicking CboManufacturer in the Master form I get the following message:

Microsoft Office Access can't find the macro 'Me.'
The macro doesn't exist...

and I notice CboModel does not update its displayed value until I click CboModel.

Any further ideas to help with this final step would be appreciated.

Regards.

 

[PHV]

needs an [highlight #FCE94F]event procedure[/highlight]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You can set an event property for a form, report, or control to [Event Procedure] to run code in response to an event. Microsoft Access creates the event procedure template for you. You can then add the code you want to run in response to the particular event.

Open a form or report in Design view.
Display the property sheet for the form or report, or for a section or control on the form or report.
Click the Event tab.
Click the event property for the event that you want to trigger the procedure. For example, to display the event procedure for the Change event, click the OnChange property.
Click Build Button image next to the property box to display the Choose Builder dialog box.
Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module or report module. These statements define, or declare, the event procedure.
Microsoft Access automatically declares event procedures for each object and event in a form or report module by using the Private keyword to indicate that the procedure can be accessed only by other procedures in that module.

Add the code to the event procedure that you want to run when the event occurs. For example, to produce a sound through the computer's speaker when data in the CompanyName text box changes, add a Beep statement to the CompanyName_Change event procedure, as follows:
Private Sub CompanyName_Change()

Beep

End Sub

The event procedure runs each time the Change event occurs for the object.

 

[cloverdog]

Thank you Majp for your time in getting this working and MH for highlighting my error.

It is working perfectly now and I have learned something new

Best regards

Cloverdog

 

[dhookom]

cloverdog,
Please make sure you click the "Like this post? Star it!" link to recognize correct answers.

Duane
Hook'D on Access
MS Access MVP