autofilling ComboBox based on another ComboBox selection

[graceusguide]

Hi,

I am trying to have 2 lookup fields on a switch. I have 2 combo box's 'cboGrantID' and 'cboGrantName'. I want the user to be able to select from either box and have the other one update automatically. Eg If I select the Grant ID 1234 in cboGrantID then I want the cboGrantName Combo box to autofill with the Grant Name that corresponds to GrantID '1234'. I want the user to be able to also select the name first and have the GrantID autofill in in the cboGrantID Combo box. Both the combo boxs are linked to the same table tblGrant, which holds both GrantID and GrantName.

Also, when you select one of the Grant Combo box's I have it so the user can select from a few command buttons to be taken to various form that corresponds with the selected grant. I want the user to be able to return to the switch and choose another form or clear all previous selections easily. I was thinking a clear button, yet am unsure how to do this.

 

[dcb]

pBaldy has done some great work here:
http://www.baldyweb.com/CascadingCombo.htm

Also have a look at this thread:

Both basically have the same row source and bound column, but the "names" combo hides the account number (zero column width). Code like this keeps them in sync:

Private Sub cboAcctName_AfterUpdate()
  Me.cboAcctNum = Me.cboAcctName
End Sub

Private Sub cboAcctNum_AfterUpdate()
  Me.cboAcctName = Me.cboAcctNum
End Sub

 

[graceusguide]

Thank you so much, it was exactly what I wanted! Perfect. Sometimes I think more complicated than I need to...

I also worked out the clear form solution which I finally found... Indesisiv had posted:
Dim ctl As Control
For Each ctl In Me.Controls

Select Case ctl.ControlType

Case acComboBox
ctl.Value = Null

Case acTextBox
ctl.SetFocus
ctl.Text = ""

End Select
Next ctl

'End Sub

Thanks
Have a great day

 

[dcb]

Dont thank me - PM pBaldy and thank him