Combo box sync

  • Thread starter Thread starter redFred
  • Start date Start date
R

redFred

I found this code a while back and cannot get it right. Please help!

Private Sub cboClient_AfterUpdate()
Dim sRS As String

If IsNull(Me.cboClient.Value) Then
sRS = ""
Else
sRS = "Select InvNum,Service,StartDate,EndDate,JobLabor from [tbl 2
Job] " & "Where ClientID = " & Me.cboClient
End If

Me.cboInvNum.RowSource = sRS

End Sub

When I select the client, it populates that box correctly, Then, when I
click into the invoice box to select I get the Access Input Box, with the
selected client in the message area and its asking me -- something? If I
input the client code I get the results correctly in the second box.

ClientID is text, box is cboClient
InvNum is numeric, box in cboInvNum


So, my surmise is I am not telling it which client -- at least not doing so
correctly.

Can anyone see what I have done wrong?


Thanks,
 
redFred?

What is the RowSource (SQL statement) for cboClient? If there is more than
one field in this SQL, what is the bound column, and what is the data type of
that column? My guess is that this includes 2 fields, a ClientName and a
ClientID, and that you are presenting the ClientName to the user, and hiding
the ClientID.

Have you tried stepping through your code to see what is being passed when
you refer to me.cboClient? If not, either do that, or add a line following
that line that prints a message to the immediate window. Something like:

debug.print me.sRS

If they are in fact text, then you SQL statement needs to wrap the value of
me.cboClient in quotes, like:

sRS = "Select InvNum,Service,StartDate,EndDate,JobLabor " _
& "from [tbl 2 Job] " _
& "Where ClientID = '" & Me.cboClient & "'"

You will note that the list line of this has an equal sign, followed by a
single quote, followed by a double quote. Then, at the end of the line, I've
wrapped a single quote in double quotes.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Thanks Dale. Looks like my issue was text vs number. When I put the
single/double quotes correctly per type, works as advertised.

Thanks so much.

yes, redFred!

Dale Fye said:
redFred?

What is the RowSource (SQL statement) for cboClient? If there is more than
one field in this SQL, what is the bound column, and what is the data type of
that column? My guess is that this includes 2 fields, a ClientName and a
ClientID, and that you are presenting the ClientName to the user, and hiding
the ClientID.

Have you tried stepping through your code to see what is being passed when
you refer to me.cboClient? If not, either do that, or add a line following
that line that prints a message to the immediate window. Something like:

debug.print me.sRS

If they are in fact text, then you SQL statement needs to wrap the value of
me.cboClient in quotes, like:

sRS = "Select InvNum,Service,StartDate,EndDate,JobLabor " _
& "from [tbl 2 Job] " _
& "Where ClientID = '" & Me.cboClient & "'"

You will note that the list line of this has an equal sign, followed by a
single quote, followed by a double quote. Then, at the end of the line, I've
wrapped a single quote in double quotes.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



redFred said:
I found this code a while back and cannot get it right. Please help!

Private Sub cboClient_AfterUpdate()
Dim sRS As String

If IsNull(Me.cboClient.Value) Then
sRS = ""
Else
sRS = "Select InvNum,Service,StartDate,EndDate,JobLabor from [tbl 2
Job] " & "Where ClientID = " & Me.cboClient
End If

Me.cboInvNum.RowSource = sRS

End Sub

When I select the client, it populates that box correctly, Then, when I
click into the invoice box to select I get the Access Input Box, with the
selected client in the message area and its asking me -- something? If I
input the client code I get the results correctly in the second box.

ClientID is text, box is cboClient
InvNum is numeric, box in cboInvNum


So, my surmise is I am not telling it which client -- at least not doing so
correctly.

Can anyone see what I have done wrong?


Thanks,
Click to expand...
Click to expand...
 
Back
Top