After Combo1 Update - Set Source Combo2?

G

Guest

I have a combo that based on what is chosen, needs to trigger the row
source for the next combo.

The first combo is called FKPartyType and the second combo is called
cboParty.

This is the code that I have:
Code: 
Private Sub FKPartyType_AfterUpdate()
If Me.FKPartyType = 2 Then
With Me("cboParty")
..RowSource = "Select tblPartyA.PKPartyAID,
tblPartyA.txtFirstName,tblPartyA.txtMiddleInitial,
tblPartyA.txtLastName" _
& "From tblPartyA" _
& "Where (tblPartyA.CaseID = '" & Me!FKCaseID & "') "
..RowSourceType = "Table/Query"
..BoundColumn = 1
..ColumnCount = 4
..ColumnWidths = "0in;1.0in;.05in;1.0in"
..ListRows = 8
End With
Else
With Me("cboParty")
..RowSource = "Select tblPartyB.PKCaseDefendnatID,
tblBLookup.txtBLookup" _
& "From tblPartyB" _
& "Join tblBLookup on tblPartyB.FKBLookup = tblBLookup.PKBLookupID" _
& "Where (tblPartyB.CaseID = '" & Me!FKCaseID & "') "
..RowSourceType = "Table/Query"
..BoundColumn = 1
..ColumnCount = 2
..ColumnWidths = "0in;1.0in"
..ListRows = 8
End With
End If
End Sub

When I pick 1 of the values for FKPartyType combo, and go to the
cboParty combo, I get an error about the select statement. The
message details the sql that would pick the value for the right source
code.

So if the FKPartyType =2 the source of cboParty should be tblPartyA
and the SQL message I get says errors with that SQL statement. Same
thing if I chould 1 or 3. I get the other SQL statement in an error
box, that there are issues with that one. Can anyone please help me
straighten out the syntax?


Thanks!
 
P

paii, Ron

Salad said:
I have a combo that based on what is chosen, needs to trigger the row
source for the next combo.

The first combo is called FKPartyType and the second combo is called
cboParty.

This is the code that I have:
Code: 
Private Sub FKPartyType_AfterUpdate()
If Me.FKPartyType = 2 Then
With Me("cboParty")
.RowSource = "Select tblPartyA.PKPartyAID,
tblPartyA.txtFirstName,tblPartyA.txtMiddleInitial,
tblPartyA.txtLastName" _
& "From tblPartyA" _
& "Where (tblPartyA.CaseID = '" & Me!FKCaseID & "') "
.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 4
.ColumnWidths = "0in;1.0in;.05in;1.0in"
.ListRows = 8
End With
Else
With Me("cboParty")
.RowSource = "Select tblPartyB.PKCaseDefendnatID,
tblBLookup.txtBLookup" _
& "From tblPartyB" _
& "Join tblBLookup on tblPartyB.FKBLookup = tblBLookup.PKBLookupID" _
& "Where (tblPartyB.CaseID = '" & Me!FKCaseID & "') "
.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 2
.ColumnWidths = "0in;1.0in"
.ListRows = 8
End With
End If
End Sub

When I pick 1 of the values for FKPartyType combo, and go to the
cboParty combo, I get an error about the select statement. The
message details the sql that would pick the value for the right source
code.

So if the FKPartyType =2 the source of cboParty should be tblPartyA
and the SQL message I get says errors with that SQL statement. Same
thing if I chould 1 or 3. I get the other SQL statement in an error
box, that there are issues with that one. Can anyone please help me
straighten out the syntax?


Thanks!
Click to expand...
Put in a space between the lam field name and table name
"Field" & "Table" to "Field " & "Table"
Same with your From and where.
Click to expand...

To see the above problem add "debug.print .RowSource" after setting the row
source. Look in the immediate window after receiving the error to see what
RowSource was set to.
 
M

macrojunkie

OK. Thank you both for responses. I have looked at everything, added
debugs and such. I did see that adding the spaces made a
difference. This is what I have now:
Code: 
Private Sub FKPartyType_AfterUpdate()
If Me.FKPartyType = 2 Then
Me.cboParty.RowSource = "Select tblPartyA.PKPartyAID,
tblPartyA.txtFirstName, tblPartyA.txtMiddleInitial,
tblPartyA.txtLastName" _
& " From tblPartyA" _
& " WHERE Me.FKCase= '" & tblPartyA.FKCaseID & "'"
Me.cboParty.BoundColumn = 1
Me.cboParty.ColumnCount = 4
Me.cboParty.ColumnWidths = "0in;1.0in;.05in;1.0in"
Me.cboParty.ListRows = 8
Else
Me.cboParty.RowSource = "SELECT tblCasePartyB.pkCasePartyBid,
tblPartyB.txtPartyB" _
& " FROM tblCasePartyB LEFT JOIN tblPartyB ON tblCasePartyB.FKPartyB =
tblPartyB.PKPartyBID" _
& " where Me.FKCase = '" & tblcasePartyB.FKCaseID & "'"
Me.cboParty.BoundColumn = 1
Me.cboParty.ColumnCount = 2
Me.cboParty.ColumnWidths = "0.0in;1.0in"
Me.cboParty.ListRows = 8
End If
End Sub

I am now getting prompted whenever I change the FKPartyType to say
what the tblCasePartyB.FKCaseID or tblPartyA.FKCaseID is. If I don't
define that, then nothing will come up. Basically, this is the
situation:
I have a form with a combo. If the value chosen from that combo is 2,
then the source of the next combo needs tobe the PartyA table. If the
choice is anything else, it needs to be the PartyB table. (kind of
like choosing between a list of singers or recorders.) The thing is
that the singers and recoders are tied to the case, so each time I go
to pick them, I need them to be limited to the case that the form is
working on.

Actually writing this out is making me think that I should just make 2
combos and make the one that I want visible. That will be so much
easier than trying to dynamically set the row source for a second
combo. If that is a bad idea, please let me know. For now, it is the
direction I will go. lol.


I love talking problems out on a forum. Thanks!
 
B

Bob Quintal

:
OK. Thank you both for responses. I have looked at everything,
added debugs and such. I did see that adding the spaces made a
difference. This is what I have now:
Code: 
Private Sub FKPartyType_AfterUpdate()
If Me.FKPartyType = 2 Then
Me.cboParty.RowSource = "Select tblPartyA.PKPartyAID,
tblPartyA.txtFirstName, tblPartyA.txtMiddleInitial,
tblPartyA.txtLastName" _
& " From tblPartyA" _
& " WHERE Me.FKCase= '" & tblPartyA.FKCaseID & "'"
Me.cboParty.BoundColumn = 1
Me.cboParty.ColumnCount = 4
Me.cboParty.ColumnWidths = "0in;1.0in;.05in;1.0in"
Me.cboParty.ListRows = 8
Else
Me.cboParty.RowSource = "SELECT tblCasePartyB.pkCasePartyBid,
tblPartyB.txtPartyB" _
& " FROM tblCasePartyB LEFT JOIN tblPartyB ON
tblCasePartyB.FKPartyB = tblPartyB.PKPartyBID" _
& " where Me.FKCase = '" & tblcasePartyB.FKCaseID & "'"
Me.cboParty.BoundColumn = 1
Me.cboParty.ColumnCount = 2
Me.cboParty.ColumnWidths = "0.0in;1.0in"
Me.cboParty.ListRows = 8
End If
End Sub

I am now getting prompted whenever I change the FKPartyType to say
what the tblCasePartyB.FKCaseID or tblPartyA.FKCaseID is. If I
don't define that, then nothing will come up. Basically, this is
the situation:
I have a form with a combo. If the value chosen from that combo
is 2, then the source of the next combo needs tobe the PartyA
table. If the choice is anything else, it needs to be the PartyB
table. (kind of like choosing between a list of singers or
recorders.) The thing is that the singers and recoders are tied
to the case, so each time I go to pick them, I need them to be
limited to the case that the form is working on.

Actually writing this out is making me think that I should just
make 2 combos and make the one that I want visible. That will be
so much easier than trying to dynamically set the row source for a
second combo. If that is a bad idea, please let me know. For
now, it is the direction I will go. lol.


I love talking problems out on a forum. Thanks!
Click to expand...
Your problem is that you have mixed up the positions of the field and
variable in your query criteria.

try
& " WHERE tblPartyA.FKCaseID= '" & me.FKCase & "'"
& " where tblcasePartyB.FKCaseID = '" & Me.FKCase & "'"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

set rowsource on subform comboBox 4
Form filter to report - error 1
Print Search Listbox Results in Report Filter Preview ... HELP!!! 0

Top