I have had great success connecting to a access file through vba! However I am at a point where the column that I and querying could change. I would like some help altering me code. Thank you!
Dim cnn As New ADODB.connection
Dim rst As New ADODB.Recordset
Dim companyid As String
companyid = Application.Transpose(Range("myid").Value) & "P"
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" mydatasource"
'This part works fine
rst.Open "SELECT [" & companyid & "],[UM] FROM tblLineItems WHERE Item ='" & ListBox1.Value & "' ;", _
cnn, adOpenStatic
rst.MoveFirst
With Me
.itemlbl.Text = ListBox1.Value
'This part doesn't
.pricelbl.Text = Format(rst! ["'" &companyid & "'"], "$0.00")
End With
'......
I have also tried
.pricelbl.Text = Format(rst!['companyid'], "$0.00")
but it didn't work either
See attached for the error message
Just so you know if I don't use a variable in
.pricelbl.Text = Format(rst![222193P], "$0.00")
It works fine.
So I really need to know how to put a variable in there!
Bookmarks