Synchronised Combo Boxes - Code Doesn't Work

C

Chris Lines

Guys - To date, I have resolved all my questions through research on this
fabulous forum, but I can't get to the bottom of my latest issue. I would
appreciate help, so I can finally get some sleep ...

I'm trying to synchronise 4 cbo on a single form called frmTasks. Here are
the tables and cbo names in order for synchronisation:

tblCCPMFunction ==> cboCCPMFunction
tblCCPMCrimeType ==> cboCCPMCrimeType
tblCCPMIncidentType ==> cboCCPMIncidentType
tbleCCPMIncidentDescription ==> cboCCPMIncidentDescription

Bound col for cboFunction is CCPMFunctionID, which is auto number data type.
Row source for cboCCPMFunction is:

SELECT [tblCCPMFunction].CCPMFunctionID, [tblCCPMFunction].CCPMFunction FROM
[tblCCPMFunction] ORDER BY [tblCCPMFunction].CCPMFunction;

CCPMFunction works and lists the data I want. I have found variations on the
code to synchronise the 2nd cbo (using the AfterUpdate Event in
cboCCPMFunction), but have used the following because I understand it the
best:

Option Compare Database
Option Explicit

Private Sub cboCCPMFunction_AfterUpdate()
'Update the row source of the cboCCPMCrimeType combo box
'when the user makes a selection in the cboCCPMFunction
'combo box.
Me.cboCCPMCrimeType.RowSource = "SELECT CCPMCrimeType FROM" & _
" tblCCPMCrimeType WHERE CCPMFunctionID = " & Me.cboCCPMFunction & _
" ORDER BY CCPMCrimeType"
Me.cboCCPMCrimeType = Me.cboCCPMCrimeType.ItemData(0)
Me.cboCCPMCrimeType.Requery
End Sub

The second cbo (cboCCPMCrimeType) does not provide a result ... the list is
blank. And since adding the requery line, I am now getting an error advising
that I have a syntax error in FROM clause. But my research tells me that I
need to requery the 2nd, 3rd, and 4th cbo. I have not yet tried writing any
code for the 3rd or 4th cbo.

Where am I going wrong? Is it because the data type is a number rather than
text? Or have I coded incorrectly? I just can't figure it out and would very
much appreciate help pls. Advice on how to make all four cbo would be ideal
if that's possible.

Thanks in advance - Chris
 
C

Chris Lines

June7

Thank you.

No, CCPMFunctionID is numeric. Thanks to your advice, I now understand what
I have to do for that line of code. I will remove the apostrophe delimiters.

I still cannot get the code to work. An error message tells me that I have
an invalid FROM clause. I don't understand why, because I pasted your code
straight in. I would appreciate more advice on where I continue to go wrong.

Yes, I think I should requery before setting the value. I assume that I
should put the requery statement in on-load. Again, I would appreciate your
advice on this one.

Thank you again, June7, for your help.

Regards - Chris

June7 via AccessMonster.com said:
Sometimes error messages on SQL strings don't accurately target the problem.
If you build the string in stages then you can better isolate the error. But
this is a simple query and should be easy to track down the problem.

Is CCPMFunctionID a text data type? Need apostrophe delimiters for text
variables (# for dates, nothing for numeric). And just to be safe, include
the semicolon at end.

Me.cboCCPMCrimeType.RowSource = "SELECT CCPMCrimeType " & _
"FROM tblCCPMCrimeType " & _
"WHERE CCPMFunctionID = '" & Me.cboCCPMFunction & "' ORDER BY CCPMCrimeType;"

Don't you want to requery before setting the value in the box?

Chris said:
Guys - To date, I have resolved all my questions through research on this
fabulous forum, but I can't get to the bottom of my latest issue. I would
appreciate help, so I can finally get some sleep ...

I'm trying to synchronise 4 cbo on a single form called frmTasks. Here are
the tables and cbo names in order for synchronisation:

tblCCPMFunction ==> cboCCPMFunction
tblCCPMCrimeType ==> cboCCPMCrimeType
tblCCPMIncidentType ==> cboCCPMIncidentType
tbleCCPMIncidentDescription ==> cboCCPMIncidentDescription

Bound col for cboFunction is CCPMFunctionID, which is auto number data type.
Row source for cboCCPMFunction is:

SELECT [tblCCPMFunction].CCPMFunctionID, [tblCCPMFunction].CCPMFunction FROM
[tblCCPMFunction] ORDER BY [tblCCPMFunction].CCPMFunction;

CCPMFunction works and lists the data I want. I have found variations on the
code to synchronise the 2nd cbo (using the AfterUpdate Event in
cboCCPMFunction), but have used the following because I understand it the
best:

Option Compare Database
Option Explicit

Private Sub cboCCPMFunction_AfterUpdate()
'Update the row source of the cboCCPMCrimeType combo box
'when the user makes a selection in the cboCCPMFunction
'combo box.
Me.cboCCPMCrimeType.RowSource = "SELECT CCPMCrimeType FROM" & _
" tblCCPMCrimeType WHERE CCPMFunctionID = " & Me.cboCCPMFunction & _
" ORDER BY CCPMCrimeType"
Me.cboCCPMCrimeType = Me.cboCCPMCrimeType.ItemData(0)
Me.cboCCPMCrimeType.Requery
End Sub

The second cbo (cboCCPMCrimeType) does not provide a result ... the list is
blank. And since adding the requery line, I am now getting an error advising
that I have a syntax error in FROM clause. But my research tells me that I
need to requery the 2nd, 3rd, and 4th cbo. I have not yet tried writing any
code for the 3rd or 4th cbo.

Where am I going wrong? Is it because the data type is a number rather than
text? Or have I coded incorrectly? I just can't figure it out and would very
much appreciate help pls. Advice on how to make all four cbo would be ideal
if that's possible.

Thanks in advance - Chris
Click to expand...
Click to expand...
 
J

John W. Vinson

I still cannot get the code to work. An error message tells me that I have
an invalid FROM clause. I don't understand why, because I pasted your code
straight in. I would appreciate more advice on where I continue to go wrong.
Click to expand...

Please post your *actual exact code*, and the names of the relevant tables and
fields.
 
C

Chris Lines

Thank you for replying John.

Actual code is:

Private Sub cboCCPMFunction_AfterUpdate()
'Update the row source of the cboCCPMCrimeType combo box
'when the user makes a selection in the cboCCPMFunction
'combo box.
Me.cboCCPMCrimeType.RowSource = "SELECT CCPMCrimeType " & _
"FROM tblCCPMCrimeType " & _
"WHERE CCPMFunctionID = " & Me.cboCCPMFunction & " ORDER BY
CCPMCrimeType;"
End Sub

tblCCPMFunction
CCPMFunctionID
CCPMFunction ==> cboCCPMFunction

tblCCPMCrimeType
CCPMCrimeTypeID
CCPMCrimeType ==> cboCCPMCrimeType
CCPMFunctionID

tblCCPMIncidentType
CCPMIncidentTypeID
CCPMIncidentType ==> cboCCPMIncidentType
CCPMCrimeTypeID

Bound cols are numeric. I hope this is enough for you to work on. Thank you
again John.
 
J

John W. Vinson

Thank you for replying John.

Actual code is:

Private Sub cboCCPMFunction_AfterUpdate()
'Update the row source of the cboCCPMCrimeType combo box
'when the user makes a selection in the cboCCPMFunction
'combo box.
Me.cboCCPMCrimeType.RowSource = "SELECT CCPMCrimeType " & _
"FROM tblCCPMCrimeType " & _
"WHERE CCPMFunctionID = " & Me.cboCCPMFunction & " ORDER BY
CCPMCrimeType;"
End Sub
Click to expand...

Odd. I don't see anything objectionable in this FROM clause - it's just a
tablename. At what point does the error message appear? What is the EXACT text
of the error message? It might be referring to some other query!
 
C

Chris Lines

John

I am very embarrassed to admit that I was using the wrong table ... I was
actually testing the code in a test table called tblCCPMCrimeType(New). Once
I used the correct tbl your advice worked. I have spent weeks trying to solve
this, and feel completely stupid over an answer that was simple in the end.
Because I'm frightened of losing my way, I've now got too many test tables
and end up getting confused. Lesson learned. Thank you again for your help. I
appreciate it.

Regards - Chris
 
Top