POPULATE COMBOBOX WITH sql data

[samohtwerdna]

hello again, I'm back with another questions for the masters. :hi:

I have a form that I would like to populate a combo Box with the results of a qry. So on the OnOpen event I have:

Private Sub Form_Open(Cancel As Integer)
Dim rstEmail As DAO.Recordset
Dim strDealer As String, sql As String

'SampleCheck
strDealer = Me.txtDealer

sql = "SELECT tblDealerEmail.email, tblDealers.Dealer" & vbNewLine
sql = sql & "FROM tblDealers INNER JOIN tblDealerEmail ON tblDealers.DealerID = tblDealerEmail.DealerID" & vbNewLine
sql = sql & "WHERE (((tblDealers.Dealer)= '" & strDealer & "'));"

Set rstEmail = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

'Me.cboEmailperson = rstEmail![Email]


End Sub


Me.cboEmailperson = rstEmail![Email] returns only the first record so I figured I needed a "for each" or a while loop to gather all the records for the dealer into my combo box - but I am not sure how to do that.

Any Ideas??

[Norie]

Why don't you just set the Control Source for the combobox to the SQL?

[samohtwerdna]

I think because i have a variable of "dealer" to be selected. Each dealer has several email addresses and I want to limit the comboBox to just the selection s for that dealer.

If I set up a query with the sql statement above I would replace strDealer with the Dealer code I need. The form is pulling the dealer code from the previous forms cboDealer so If I try to set my criteria to Forms!frmMain.cboDealer and then place in the Control Source for the combo Box =qryEmail.email - I get nothing??

I am I doing it wrong?

[Norie]

I mean use the SQL you have created.

Me.cboEmailperson.ControlSource = sql

[GaryB]

Hi,

Would something like, SELECT [cbodealer].[dealercode] FROM (the name of table used), in the row source work? I have something similar for account numbers in a combo box and it works pretty well. I may have not given you the exact code needed, but, the general idea. In my form this leads back to the table that stores customer information.

GaryB