Typing SQL statements in VBA

[dylcon]

Hello, I am trying to cascade some comboboxes for some forms, but I need to enter a string in, which is SQL, but it keeps coming up with a syntax error. I am just wondering what exactly I am doing wrong for this statement to keep getting flagged. Also, is there anyway to run a command on more than one line in Access vba?

Here is my code:

Private Sub CustomerNamecbo_AfterUpdate()    Dim strSource As String
    strSource = "SELECT tblPartInfo.PartNumber, tblPartInfo.PartNumber " & _
                   "FROM tblPartInfo " & _
                   "WHERE CustomerName = '" & Me.CustomerNamecbo & "' _
                        ORDER BY tblPartInfo.PartNumber"


    Me.PartNumbercbo.RowSource = strSource
    Me.PartNumbercbo = vbNullString




End Sub

Ignore the '_' at the end of some of the lines. I know that is wrong, I just put them here so it would be easier to view.

[orange]

The _ is a line continuation character in vba.

You must open and close your quotes (") on the same line. Make a complete string.

eg: you have

"WHERE CustomerName = '" & Me.CustomerNamecbo & "' _
ORDER BY tblPartInfo.PartNumber"

which should be (untested)

"WHERE CustomerName = '" & Me.CustomerNamecbo & "'" _
& " ORDER BY tblPartInfo.PartNumber"

In effect you are building string segments on a line and concatenating these with &

[dylcon]

That helps very much, thank you. Now I currently have:

Private Sub CustomerNamecbo_AfterUpdate()    
    Dim strSource As String
    strSource = "SELECT tblPartInfo.PartNumber, tblPartInfo.PartNumber " & _
                   "FROM tblPartInfo " & _
                   "WHERE CustomerName = '" & Me.CustomerNamecbo & "'" _
                    & " ORDER BY tblPartInfo.PartNumber"


    Me.PartNumbercbo.RowSource = strSource
    Me.PartNumbercbo = vbNullString


End Sub

but the cascadin combo box is not working. It asks to enter the paramater CustomerName and then does not update the next combo box. Any ideas?

P.S. should i start a new thread for this question?

[orange]

We haven't seen anything of your combo, but here are 2 videos related to cascading combos
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

[dylcon]

My first combo box is not bound to the form, which I think is causing my problem. Is it still possible to let the criteria for the second combo box (which is bound to the form) be dependent upon the first, unbounded, combobox? I can either get nothing to show up in the second one or all of them, but not based upon the criteria from the first cb.

[dylcon]

New Compressed (zipped) Folder.zip

Here is my document in case anyone wanted to see for themselves. I have tried doing it both in VBA, in query builder, and SQL within the query builder.

[Missinglinq]

...My first combo box is not bound to the form, which I think is causing my problem...

That's not the problem. In point of fact, these Comboboxes are almost always Unbound!

You need a semi-colon at the end of your SQL Statement:

& " ORDER BY tblPartInfo.PartNumber;"

so you end up with

Private Sub CustomerNamecbo_AfterUpdate()
    Dim strSource As String
    strSource = "SELECT tblPartInfo.PartNumber, tblPartInfo.PartNumber " & _
                   "FROM tblPartInfo " & _
                   "WHERE CustomerName = '" & Me.CustomerNamecbo & "'" _
                    & " ORDER BY tblPartInfo.PartNumber;"


    Me.PartNumbercbo.RowSource = strSource
    Me.PartNumbercbo = vbNullString


End Sub

Linq ;0)>

[pbaldy]

The semi-colon is not required; I never include it when writing SQL in code. In the query, you use "Me." which you can't do, you have to use the full form reference there. Also, you're comparing the ID returned by the first combo to a name, so you never get a match.

[pbaldy]

This code version works:

    Dim strSource As String
    strSource = "SELECT tblPartInfo.PartNumber, tblPartInfo.PartNumber " & _
                   "FROM tblPartInfo " & _
                   "WHERE CustomerID = " & Me.CustomerNamecbo _
                    & " ORDER BY tblPartInfo.PartNumber"
    Debug.Print strSource
 
    Me.PartNumbercbo.RowSource = strSource
    Me.PartNumbercbo = vbNullString

[June7]

The semi-colon would be required if opening an ADODB recordset.

Why is PartNumber in the query twice? Should there be PartName? Why not just put the sql directly in the RowSource:

SELECT PartNumber, PartName FROM tblPartInfo WHERE CustomerID=" & [CustomerNamecbo];

Then in CustomerNamecbo AfterUpdate:

Me.PartNumbercbo.Requery

Are these comboboxes both bound or unbound? Are they on a continuous or datasheet form?

What do you mean by 'run a command on more than one line'?

[dylcon]

PBaldy,
I have the comboboxes working appropriately now (thanks to your code), but I need that information to be inserted as the ID, not the actual PartNumber. How would I go about doing this?

[pbaldy]

I just noticed that you had the part number field twice. One should probably be the ID field.

[dylcon]

Yeah sorry, I should have edited my previous post. I realized that right after I asked the question. Thanks again PBaldy.

[pbaldy]

No problem.