filling a data combo control from database table at run-time 1

[kellyputty]

I am stumped. If I use a literal string in my SQL query below I get the correct result. If I use the value in a textbox, I get nothing. Not sure why.

Below: if instead of what I have in bold I insert 1001009, my dbcAccountID populates correctly, but not with txtCustomerID.text.

Any thoughts?

SQLCustomerAccount = "SELECT Customer.CustomerID, CustomerAccount.AccountID FROM Customer LEFT JOIN CustomerAccount ON Customer.CustomerID = CustomerAccount.CustomerID WHERE CustomerAccount.CustomerID =  [b]'" & txtCustomerID.Text & "'[/b]"

    With rsCustomerAccount
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open SQLCustomerAccount, conMB, , , adCmdText
    End With
    Set dbcAccountID.RowSource = rsCustomerAccount

 

[johnwm]

If it's a numeric field you don't need the apostrophes, but you should Trim any spaces and do a specific conversion to Int:

....WHERE CustomerAccount.CustomerID = " & Cint(Trim(txtCustomerID.Text))


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[BobRodes]

If you want to dig around, you can do things like checking len(txtCustomerID.Text) and the value of txtCustomerID.Text = "1001009". The latter is likely to be false, if your text box isn't returning a record and the literal is.