ComboBox

[RK]

Hello All & Greetings.

I am looking for some help with getting data into a combo box.

First, I have a SQL 2K Table linked to Access. The Table has 3 columns:
CarID, Make, Model. Each Make can have multiple Models. I have a form on
which I have 2 Combo Boxes. The first Combobox, will display all the makes
of various Cars (e.g. Ford, Chrysler, etc.) In the 2d Combobox, it should be
populated with the models for the particular Make [e.g. If Ford is selected
in the fist combobox, Escort, Taurus, etc should be displayed in the 2d
combobox; likewise, if Chrysler is selected, the 2d combobox should display,
Dodge, Plymout, etc.) I am trying to do this as an UpdateEvent for the first
combobox.

I hope this is clear and that someone can help me in this.

Thanks to all in advance.

RK

 

[John Vinson]

First, I have a SQL 2K Table linked to Access. The Table has 3 columns:
CarID, Make, Model. Each Make can have multiple Models. I have a form on
which I have 2 Combo Boxes. The first Combobox, will display all the makes
of various Cars (e.g. Ford, Chrysler, etc.) In the 2d Combobox, it should be
populated with the models for the particular Make [e.g. If Ford is selected
in the fist combobox, Escort, Taurus, etc should be displayed in the 2d
combobox; likewise, if Chrysler is selected, the 2d combobox should display,
Dodge, Plymout, etc.) I am trying to do this as an UpdateEvent for the first
combobox.

There are a couple of ways to do this. With SQL as the backend I'd
recommend the second (somewhat more involved) method. In the
AfterUpdate event of the Make combo box, construct a SQL string
selecting just models of that make and assign it to the RowSource of
the second combo:

Private Sub cboMake_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT Model FROM tablename" & _
" WHERE tablename.Make = '" & Me!cboMake & "'" & _
" ORDER BY Model;"
Me!cboModel.RowSource = strSQL
End Sub

 

[RK]

Hello John:

Greetings and thanks for the reply.

I have the following code:

Private Sub cboMake_AfterUpdate()

Me.cboModel.RowSource = "SELECT cd.model " & _
" FROM dbo_bgCarDetails cd " & _
" WHERE cd.make = " & Me.cboMake

Me.cboModel = Me.cboModel.RowSource

End Sub

However, when I try to view the form I have 2 issues:
1. First, after I select a Make from the Make combobox (The Fords,
Chryslers, etc.), it pops up a 'Enter the Parameter Value input box. I just
dont understand why this is the case.

2. Even after I enter the selection (Jeep or whatever - essentially entering
the selection I have already made in the cboMake combobox, its not to much
avail.) None of the items corresponding to Ford are listed / displayed in
the cboModel combobox.

I hope the above is clear and that someone will be able to explain and elp
what I amdoing wrong!

thanks to all in advance,

RK

First, I have a SQL 2K Table linked to Access. The Table has 3 columns:
CarID, Make, Model. Each Make can have multiple Models. I have a form on
which I have 2 Combo Boxes. The first Combobox, will display all the makes
of various Cars (e.g. Ford, Chrysler, etc.) In the 2d Combobox, it should be
populated with the models for the particular Make [e.g. If Ford is selected
in the fist combobox, Escort, Taurus, etc should be displayed in the 2d
combobox; likewise, if Chrysler is selected, the 2d combobox should display,
Dodge, Plymout, etc.) I am trying to do this as an UpdateEvent for the first
combobox.

There are a couple of ways to do this. With SQL as the backend I'd
recommend the second (somewhat more involved) method. In the
AfterUpdate event of the Make combo box, construct a SQL string
selecting just models of that make and assign it to the RowSource of
the second combo:

Private Sub cboMake_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT Model FROM tablename" & _
" WHERE tablename.Make = '" & Me!cboMake & "'" & _
" ORDER BY Model;"
Me!cboModel.RowSource = strSQL
End Sub

 

[John Vinson]

Hello John:

Greetings and thanks for the reply.

I have the following code:

Ok... two problems:

Private Sub cboMake_AfterUpdate()

Me.cboModel.RowSource = "SELECT cd.model " & _
" FROM dbo_bgCarDetails cd " & _
" WHERE cd.make = " & Me.cboMake

Since Make is a Text field you need the syntactically required quote
marks. Change this line to

" WHERE cd.Make = '" & Me.cboMake & "'"

Me.cboModel = Me.cboModel.RowSource

And this line is simply wrong - you've already set the Rowsource, you
don't want to set the value of the combo box to a SQL string! Instead,
use

Me.cboModel.Requery

if it's not showing anything (and it should not be necessary since
you're setting the Rowsource explicitly).