Populate variable using SQL

[Keith Nichols]

I want to use SQL to populate a variable that is used elsewhere in my code. none of the various methods I have tried work so I was wondering if this is possible in the first place and if it is, what is the syntax/structure of the code?

Dim DeptOrder As String

DeptOrder.DoCmd.RunSQL SQLText & WClause [COLOR="Green"]   'this gives invalid qualifier error[/COLOR]

DeptOrder = DoCmd.RunSQL SQLText & WClause [COLOR="green"]   'this gives syntax error[/COLOR]

DoCmd.RunSQL SQLText & WClause   [COLOR="green"]'this doesn't produce any errors, but where does the query result go to?[/COLOR]

 

[MStef]

Why you want to populate a variable via SQL ????
Do it via VBA.

 

[Bat17]

you could probably use Dlookup to fill your variable

peter

 

[Keith Nichols]

Thanks for the responses guys.

Why you want to populate a variable via SQL ????
Do it via VBA.

MStef,
I haven't made my situation clear - sorry. I am trying to run an SQL query in VBA.

Bat17,
The variable I want is not simply a value in a table. I am trying to pull the values from 2 fields in one table using an inner join and where clauses etc. I don't know, but I didn't think Dlookup can do anything more complex than pulling the value from a single field.

Having played with SQL in VBA a little for populating comboboxes and subforms, it seems that you must be able to do the same to set a variable that you then use elsewhere

If this isn't possible, I will have to try and create an Insert Into SQL routine, but it will be more complicated than gettng the variable separately and then inserting it. I suppose that it might be possibel to pull the individual fields into variables using Dlookup and then concatenate them but it all seems a long way round to something that should be firly straight forward.

Regards,

 

[Bat17]

you will need to use Recordsets then. Have a look in help. I find DAO easier to use than ADO

Peter

 

[Keith Nichols]

Hi Peter,

Thanks for responding. I haven't used ADO or DAO (other than when blindy cppying code). Could you give me a pointer as to what you are suggesting I try i.e. what sort of structure would my code take?

Thaks in advance for any tips etc.

 

[boblarson]

Keith:

Contrary to Bat17's preferences, I prefer ADO in these cases as it is very simple to write.

Dim rst As ADODB.Recordset

Set rst=New ADODB.Recordset
rst.Open strSQL,CurrentProject.Connection,adOpenDynamic,adLockOptimistic

If your strSQL is returning ONE record, then

strVariableHere = rst.Fields("YourFieldNameHere").Value

Otherwise you can loop through it, do lookups on the rst recordset, just like a form's recordset.

 

[Keith Nichols]

Thanks Bob, works a treat

For those that follow, here is the code I created based on Bob's pointer:

Dim  SectID, DptOrd As String

[COLOR="Green"]'Get criteria from column of combobox [/COLOR]
	SectID = cboAssignPersonnel.Column(6)

[COLOR="green"]'Prepare SQL to get DeptOrder from tblDeptSection[/COLOR]
    	SQLText = "SELECT tblDeptSection.DeptOrder, tblDeptSection.SectionID"
    	SQLText = SQLText & " FROM tblDeptSection"
    	SQLText = SQLText & " WHERE (((tblDeptSection.SectionID)= " & SectID & "));"

[COLOR="green"]'Define recordset[/COLOR]
    	Dim rst As ADODB.Recordset

[COLOR="green"]'Run SQL to retrieve recordset[/COLOR]
    	Set rst = New ADODB.Recordset
    	rst.Open SQLText, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

[COLOR="green"]'Assign value to variable[/COLOR]
    	DptOrd = rst.Fields("DeptOrder").Value

Very impressed - thanks again Bob.

 

[boblarson]

You're most welcome! Glad I could help.