Listing values in a combo box depending on a previous combo box

[weircolin]

Hi

I apologise if this is quite vague, but not sure how to explain it
exactly.

I am making up a database and I want to use a combo box to select a
topic and then in a second combo box select a sub topic depending on
what the first topic is.

Best way I can describe it is if you were doing an online car insurance
quote, you have a drop down menu with makes of cars and then in a
second menu you have models of cars. Now depending on what you say in
the first one will determine what list is shown in the second one.

I have been trying various things but I can't seem to get the code
right for it, can anyone shed some light on this situation please?

Thanks

Colin

 

[Graham R Seach]

This will explain all:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Sounds great but I still get:
"The expression After Update you entered as the event property setting
produced the following error: Oject or class does not support the set of
events.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Private Sub JOBID_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cboInvoice, based on the
'value selected in cboCustomer.
sSQL = "SELECT dbo_Tasks.TASKID, dbo_Tasks.TASKNAME, dbo_Jobs.JOBID FROM
dbo_Tasks RIGHT JOIN dbo_Jobs ON dbo_Tasks.JOBKEY=dbo_Jobs.JOBKEY WHERE
dbo_Jobs.JOBID)=" & Me.JOBID & " ORDER BY dbo_Tasks.TASKID"

Me.TASKID.RowSource = sSQL
'The combo should requery on it's own, but if it doesn't,
'uncomment the next line.
'Me.cboCombo2.Requery 'Requery the combo

End Sub

What the hell is going on.

 

[Graham R Seach]

You said the code "...sets the RowSource of cboInvoice, based on the value
selected in cboCustomer". Er, no it doesn't! The procedure's name is
JOBID_AfterUpdate(), not cboCustomer_AfterUpdate(). Also, you're trying to
set the value of TASKID's RowSource, not cboInvoice's. Lastly, you code
talks about JOBID - there's no mention of either Customer or Invoice!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Sounds great but I still get:
"The expression After Update you entered as the event property setting
produced the following error: Oject or class does not support the set of
events.

* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Private Sub JOBID_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cboInvoice, based on the
'value selected in cboCustomer.
sSQL = "SELECT dbo_Tasks.TASKID, dbo_Tasks.TASKNAME, dbo_Jobs.JOBID
FROM
dbo_Tasks RIGHT JOIN dbo_Jobs ON dbo_Tasks.JOBKEY=dbo_Jobs.JOBKEY WHERE
dbo_Jobs.JOBID)=" & Me.JOBID & " ORDER BY dbo_Tasks.TASKID"

Me.TASKID.RowSource = sSQL
'The combo should requery on it's own, but if it doesn't,
'uncomment the next line.
'Me.cboCombo2.Requery 'Requery the combo

End Sub

What the hell is going on.

This will explain all:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[weircolin]

Hi Graham

Thanks for your reply. Have implemented that code but no no avail as
of yet. I will keep trying! I think I must be putting the wrong names
in from the tables I have. This is the code I am using.


Private Sub Issues_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of Issues, based on the
'value selected in cboIssues.
sSQL = "SELECT Category ID, Topic ID, " _
& " FROM tblissues WHERE Category = " & Me.cboissues _
& " ORDER BY Topic"

Me.cbotopics.RowSource = sSQL
'The combo should requery on it's own, but if it doesn't,
'uncomment the next line.
Me.qryissuesandconcerns.Requery 'Requery the combo

End Sub

In the table "tblissues" I have the following headings, "Category
ID"(AutoNumber), "Catagory" and "Description". In the table
"tbltopics" I have the headings "Topic ID"(AutoNumber), "Topic Name",
"Category" and "Description".

From this can you see anything I am doing wrong with the code?

Thanks again

Colin

 

[weircolin]

Hi again.

I've got the form working now, I wasn't linking the two tables with the
information.

I am now trying to save the data that is selected from the form in a
seperate table, does anyone have any information on this?

Colin

 

[Graham R Seach]

To save the value(s) to a separate table, build a SQL statement, and execute
it:
Dim strSQL As String

strSQL = "INSERT INTO tblMyTable (field1) " & _
"VALUES (" & Me!cboMyCombo & ")"

CurrentDb.Execute strSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[weircolin]

Hi

Got it working great now, thanks for all your help.

There is one wee thing that it is still doing that I am sure is
something silly that I am now overlooking but my head is too sore from
working on this for the past week.

When I am using a combo box that I use to get the values from the
following is coming up when using the forms.

"The Value you entered isn't valid for this field.

For example, you may have entered text in a numeric field or a number
that is larger than the fieldsize setting permits."

Any suggestions?

Colin

 

[weircolin]

Hi

Sorry, managed to solve it. Don't know what the problem was but
deleted the combo boxes and inserted them again and it's working great
now!

Thanks for all your help, I really appreciate it.

Colin

 

[Graham R Seach]

Colin,

Glad it's working.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------