ComboBox refreshing subform.

[Garrett!]

Hi. I am trying to use a combo box to refresh a subform. I am working with Access 2007 as my front end and SQL 2014 on the back end. On my main form called frmDealers I have an unbound combobox where the user selects a dealer and then text boxes are populated with address and phone number. I have it unbound because I don't want the user to make changes to the dealership at this point. On the subform I have people that work at the dealership. That form is called sfrmDealerContacts. The subform could have multiple contacts for each dealership. The subform is bound to a query called qryDealerContacts. I have it bound to a query because I want email hyperlinks in the table too. I tried searching the forum and found this thread number 279791. Sorry, I don't have enough posts to post a link to the thread.


After searching I added:
Form_sfrmDealerContacts.Form.RecordSource = "SELECT * FROM dbo_DealerContact WHERE DealerID = " & Me![cboDealerName].Column(1)

But this brings up an input box so I must have something wrong. My code is:

Private Sub cboDealerName_AfterUpdate()

    Me.txtDealerAddress = Me![cboDealerName].Column(2)
    Me.txtDealerAddress2 = Me![cboDealerName].Column(3)
    Me.txtDealerCity = Me![cboDealerName].Column(4)
    Me.txtDealerState = Me![cboDealerName].Column(5)
    Me.txtDealerZip = Me![cboDealerName].Column(6)
    Me.txtDealerPhone = Me![cboDealerName].Column(7)
    Me.txtDealerFax = Me![cboDealerName].Column(8)
    Me.cboPrimaryPlant = Me![cboDealerName].Column(9)
    Me.cboBrands = Me![cboDealerName].Column(10)
    Me.txtNotes = Me![cboDealerName].Column(11)
    Me.chkDealerCanceled = Me![cboDealerName].Column(12)

    Form_sfrmDealerContacts.Form.RecordSource = "SELECT * FROM  dbo_DealerContact WHERE DealerID = " & Me![cboDealerName].Column(1)
    
End Sub

Hopefully I was clear on this.

Thanks!

 

[spikepl]

You've used improper (experts' only) syntax to refer to an embedded subform.

Use

Forms!MainFormName!SubformcontrolName.Form.RecordSource

Me!sfrmDealerContacts.Form.RecordSource

assuming the subformcontrol ( the thing containing the form) is named sfrmDealerContacts

 

[Garrett!]

Thanks. That did the trick. Of course I've developed a new problem. I only ever see record 1 in my Contacts table. I must have a problem with a join somewhere. Thanks again!

 

[zzoemie]

Hi,

With this method i get error 3008