table won't load into the Recordset

[operator]

Hope someone can help me on this. I am trying to load a table into a RecordSet, but it seems I cannot load the data. I can get the column headers but not the values.

I had some code to MsgBox the values of the table but it's all blank so I don't think the table is loaded into the RecordSet.

Here is my code. Can someone take a quick look for me? Much appreciated!!
--------------------------------------
Dim records As ADODB.Recordset
Dim recordsField As ADODB.Field
Dim vendor As String

Set records = New ADODB.Recordset
vendor = Me.Combo214

Me!List212.RowSource = "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = " & vendor & ";"

records.Open "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = "" & vendor & "";", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

For Each recordsField In records.Fields
MsgBox (recordsField.Name)
Next

For Each recordsField In records.Fields
MsgBox (recordsField.Value)
Next

--------------------------------------------------

 

[operator]

I am not sure which one I did is numeric.
"vendor" is a String that I am trying to pass in.

The following statement works when I use it.
--------------------------------------------
Me!List212.RowSource = "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = " & vendor & ";"
--------------------------------------------
However, it does prompt a dialog box ask me to enter parameter value though. Is it because the way I quote the vendor String?

If I change the quoting method in the following statment
-------------------------------------------
records.Open "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = "" & vendor & "";", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

to

records.Open "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = " & vendor & ";", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

-------------------------------------------
It gives me an error message as "No value given for one or more required parameters."

 

[operator]

I guess I should ask this quetsion "What's the proper syntax to enclose a variable in SQL statement??"

 

[Bogzla]

Try
-------------------------------------------
Me!List212.RowSource = "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = '" & vendor & "';"

records.Open "SELECT record.ID, record.vendor_code, record.invoice_date, record.period_covered, record.amount FROM record WHERE vendor_code = '" & vendor & "';", Application.CodeProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
--------------------------------------------
note single quotes around vendor variable, should give you an output like:

......WHERE vendor_code = 'SomeValue'

hopefully....

hth,
Bogzla

 

[operator]

Oh...it worked!

It must be the spacing because I tried the single quotes at first and it didn't work.
Then I just tried again by copying an pasting the codes you editted.

Thanks a bunch, Bogzla!! Appreciated!!!

 

[Bogzla]

Glad I could help