No value given...

[rickyoswaldiow]

Good evening everyone. I am learning some SQL this evening and I'm having trouble with this bit of code

    strSQL = "SELECT CustomerID, [First Name], [Last Name], Phone, Email " & vbCrLf & _
             "FROM tblCustomers " & vbCrLf & _
             "WHERE CustomerID = " & Me.cboCustomer.Text
             
    Set rsCustomers = New Recordset
    rsCustomers.Open strSQL, cnConn, adOpenDynamic, adLockOptimistic

The error message is "No value given for one or more required parameters.
The database is from

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

I have simply added a new form to start messing around with what I have learnt.

 

[Remou]

Do not use vbCrlf with SQL. It does not mix.

[tt]strSQL = "SELECT CustomerID, [First Name], [Last Name], Phone, Email " _
& "FROM tblCustomers " _
& "WHERE CustomerID = " & Me.cboCustomer.Text[/tt]

The breaks are for neatness, one long string would work just as well, but would be difficult to read.

 

[rickyoswaldiow]

*EDIT*
I have changed the SQL statement to:

strSQL = "SELECT * FROM tblCustomers WHERE [Last Name] = " & Me.cboCustomer.Text

 

[rickyoswaldiow]

Do not use vbCrlf with SQL. It does not mix.

That's peculiar. I am a trainee developer and the app I am working on uses it everywhere, that's where I got it from!

 

[rickyoswaldiow]

P.S. It still doesn't work

 

[rickyoswaldiow]

I've found the problem, firstly I was checking the CustomerID against their name as you can probably see, whoops. I'm not entierly sure why it was not working the second time...
However, I have changed the SQL statement to:

strSQL = "SELECT * FROM tblCustomers WHERE CustomerID = " & Me.cboCustomer.Column(2)

This works just fine

 

[Remou]

I doubt that they use vbcrlf within an SQL string in Accessn Jet. Elsewhere, vbCrLf is fine.

 

[rickyoswaldiow]

We aren't using Jet, my boss says - "the reason we use this is so when we paste it into the immediate window and enteprise manager the code is broken down nicely."
Thanks again for the help.