Synchronising two combo boxes

  • Thread starter Thread starter ashley90
  • Start date Start date
A

ashley90

Hiya

I have tried everything to make this code work in my database but it just
won't!

Can anyone give me any advice? It is only based around one table (there
isn't two tables like all the samples keep showing on here)

Heres the code....

Private Sub cboSession_AfterUpdate()

Me.cboCoursePeriod.RowSource = "Select Course Period FROM" & _
"Course Period WHERE CP Ref = " & Me.cboSession & _
"ORDER BY Course Period"
Me.cboCoursePeriod = Me.cboCoursePeriod.ItemData(0)

End Sub

Thanks
Ashley
 
Isn't working, doesn't say much! What are you wishing to do and what isn't
working?

Have you tested your SQL string in the QBE? I'm pretty sure it won't work.
Start there. Once you know your SQL string is valid then you can move on.

When you have a 'space' in a field name (which you shouldn't), you must
enclose the field name in []. Furthermore, if CP Ref is a number, all is
good, but if it is a string the you must enclose the value between ''.
Finally, you are missing spaces! Your current code for intance gives:

Select Course Period FROMCourse Period WHERE [CP Ref] = YourValueORDER BY
Course Period

you are missing spaces after FROM, before ORDER,...

Try something more like:

Me.cboCoursePeriod.RowSource = "Select Course Period FROM" & _
" Course Period WHERE [CP Ref] = '" & Me.cboSession & _
"' ORDER BY [Course Period]"
OR
Me.cboCoursePeriod.RowSource = "Select Course Period FROM" & _
" Course Period WHERE [CP Ref] = " & Me.cboSession & _
" ORDER BY [Course Period]"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Daniel, thank you for your reply.

I did try both your alternatives but neither were successful unfortunately.

I have only just taking over the admin work of this database. Trying to find
ways of making things more efficient.

I want for the user to be able to select a session (i.e. Session 2008/2009)
from the first combo box and this will allow the user to select a course that
ran that year for the second combo box.

CP Ref is a mix of numbers and letters (e.g. FGHU-G10E)

So far with the codes i've put in and the ones you gave me, all it is doing
is showing me names from the table in the first combo box and nothing in the
second combo box.

To be honest, I have never worked with combo boxes before, therefore i'm
just taking shots in the dark and really need help.

Thanks
Ashley
 
So if I understand you correctly, you wish to make the second combo box
dependant on the selection made in the first combo box. This is know as
Cascading Combo boxes.

So if I understand your code, your 2nd cbo is named 'cboCoursePeriod'. What
is the name of your first cbo?

You code would need to go in the after_update event of your 1st cbo. Then
your SQL would need to pull your [CP Ref] WHERE the Session = 1stCboName. So
if the above logic makes sense, your current SQL is off.

Me.cboCoursePeriod.RowSource = "Select [CP Ref] FROM" & _
" YourTableName WHERE [YourSessionFieldName] = '" &
Me.cboSession & _
"' ORDER BY [CP Ref]"

The above is just a general idea, as I am not familiar with your table.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Back
Top