Store SQL result as String Variable

[tt1611]

Hi All
Hopefully this is not too difficult.

I have a declared string

dim strModel as string

I am trying to run a simple SELECT statement

"SELECT column_name FROM table
WHERE combobox = column_name"

I am trying to store the results of that query in my string but, get this, when i run the code, Access VBA is store the exact string in strModel
ie as supposed to running the SQL query and store the result in strModel, its making

strModel = "SELECT column_name FROM table WHERE combobox = column_name"

i debugged this and tested the output in a textbox and this is how it is displayed.

I saw some notes online about setting a dao recordset and using that to run the code

dim dbs as DAO. Recordset
dim strqry as querydef

etc
I have tried this but to no avail (actually I'm not sure i know how to use this in my scenario)

Can someone please help with this.

Thanks

 

[boblarson]

You would either need to use a DLookup or a recordset to get it. To use a recordset (probably more efficient than a DLookup) you would use:

Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
 
strSQL = "SELECT column_name FROM table " & _
"WHERE combobox = column_name"

 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
strModel = rst!column_name
 
rst.Close
Set rst = Nothing

That is, if it returns a single record. I more than one you would have to do some other processing if needed.

 

[tt1611]

Hey Bob
The line

set rst = currentdb.openrecordset (strSQL)

Access is unable to find properties for currentdb - are we missing something here.

 

[boblarson]

Hey Bob
The line



Access is unable to find properties for currentdb - are we missing something here.

It should work fine - What is the actual string you are building for strSQL?

 

[tt1611]

Ok Bob
Sorry there was a typeo

When I run my code, I am getting a runtime error 3061 Too few parameters. Expected 1.

I am trying to store the model, manufacturer and serial (all of which exist on a table) of a device based on the user's selection. If the selection is a printer, the query should read of the printer table, else it should read of the PC table. My code is highlighted below. At the moment I am testing this just with the model to see how it works then will incorporate other variables in my code strSerial and strMan. These results I will need to display on another form.

Ignore the else part for now. I need to edit that piece of code for now I am testing where the selection is "printer"

Private Sub cmddesc_click()
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
 
If Me.cmbprob = "Printer" Then
strSQL = "SELECT model FROM network_printers WHERE device_name = cmbdevice"
Set rst = CurrentDb.OpenRecordset(strSQl)
'Set rst = CurrentDb.OpenRecordset(strSQl,,,)as Recordset
strModel = rst!model
 
Else
strModel = "SELECT model FROM PCs WHERE device_name = cmbdevice"
strMan = "SELECT manufacturer FROM PCs WHERE device_name = cmbdevice"
strSerial = "SELECT serial FROM PCs WHERE device_name = cmbdevice"
strUser = "SELECT Reg_User FROM PCs WHERE device_name = cmbdevice"
DoCmd.OpenForm "Desc_Select"
Form_Desc_Select.txtdesc = strModel
Me.Visible = False
End If

When it said too few parameters, i tried adding the rest to see if it would work in the line of code I have that I have struck out. Please let me know if you need clarity on the above

 

[namliam]

strSQL = "SELECT model FROM network_printers WHERE device_name = cmbdevice"

You have to substitute in the variables....
For String/text:
strSQL = "SELECT model FROM network_printers WHERE device_name = '" & cmbdevice & "'"
or:
strSQL = "SELECT model FROM network_printers WHERE device_name = """ & cmbdevice & """"
or:
strSQL = "SELECT model FROM network_printers WHERE device_name = " & chr(34) & cmbdevice & chr(34)

For numbers:
strSQL = "SELECT model FROM network_printers WHERE device_name = " & cmbdevice

For dates:
strSQL = "SELECT model FROM network_printers WHERE device_name = #" & cmbdevice & "#"

 

[tt1611]

namliam
You are da man/woman lol

Got my variable pulling the model now...ill try it with the other 3. I'll store these variables globally to make them available to other forms.

 

[namliam]

Do note that...

strSQL = "SELECT model FROM network_printers WHERE device_name = cmbdevice"
Set rst = CurrentDb.OpenRecordset(strSQl)
'Set rst = CurrentDb.OpenRecordset(strSQl,,,)as Recordset
strModel = rst!model
 
Else
strModel = "SELECT model FROM PCs WHERE device_name = cmbdevice"
strMan = "SELECT manufacturer FROM PCs WHERE device_name = cmbdevice"
strSerial = "SELECT serial FROM PCs WHERE device_name = cmbdevice"
strUser = "SELECT Reg_User FROM PCs WHERE device_name = cmbdevice"

Your strModel/man/serial/user should be using the same method as printer does... and no need to do it in 4 different queries...
You can do soemthing along the lines of:

strSQL = "SELECT model, manufacturer, serial, Reg_User FROM PCs WHERE device_name = cmbdevice"

 

[tt1611]

Yeah already did that Namliam but thanks for clarifying

By the way...Thanks Bob for your insight into opening the Recordset. Something new I have learnt as well.
Thank you

 

[tt1611]

Hi NamLiam or Bob or anybody

Just going off the above. If as an example

strModel = rst!model

rst!model was null in the table, it errors out as there is no data naturally. Is there a way to tell access that for example

if rst!model = "" OR isnull then

the associated string

strModel = "N/A"
 
else
strModel = rst!Model

Is this above possible.

 

[namliam]

check out the NZ function

 

[tt1611]

I did and it definitely checked out

Nice one again

 

[tt1611]

Hi All
I am sorry to keep coming back to this post but my debugging is going really well . I asked last week about a situation where my recordset column returns a null value and namliam mentioned the Nz function which was perfect for me last week. The issue I have now is that if the recordset runs the following query

SELECT columnA, columnB FROM tableA where column3 = '" & me.combo3 & "'"

if access goes to tableA and finds an associated record for column3 then it returns everything I need as it should. However if it doesnt find a record that satisfies my column3 ie there is no data in the table that equals combo3, it errors out saying

Run-time error '-2147352567 (80020009)':

no current record.

I am trying to find monitors in the monitor table that belong to a certain PC (combo3) and we havent yet entered all monitor information for every PC. Is there anyway to get access to return Null or "N/A" when it doesnt find this record as supposed to erroring out?

 

[JANR]

Test for rst.EOF.

Set rst = Currentdb.OpenRecordset(....)
 
If Not rst.EOF Then
    strModel = rst!Model
End If
...
...

JR

 

[tt1611]

You guys have really helped me out a lot while building this app. Thanks JANR, that worked perfectly.

Just what I needed. It returns null if rst.EOF is true otherwise it gives me what I need.