Retrieve string from an SQL query within VBA.

[olspookishmagus]

Hello once more, I could use your help and thanks in advance for it.

There's a table named Customers that has as a primary key, an autonumbering field named Customer_ID, a text filed named Surname and a text field named Name.

Whenever you wish to see a customer's history (like completed or pending orders) I wanted to make the form History that appears to display on it's caption. History: along with the customer's surname followed by his name.

Keep in mind that whenever you try to call the History form, you call it from withing the Customers form that displays the customer's details and therefore you can pass the Customer_ID to the new form.

My objective is to be able retrieve stings throught SQL queries from within the VBA without the need of using additional macros or queries. Keep in mind that this is about Microsoft Office 2000 SP-3 and Microsoft Visual Basic 6.0.

And here's what I've done so far for this.

In the History form, I have created two string variables named Surname_Name and SQL_String within the Form_Load event like this:

Private Sub Form_Load

Dim Surname_Name As String
Dim SQL_String As String
...

Then, I assing the SQL_String variable the following value, like this:

SQL_String = "SELECT [Customers]![Surname]+' '+[Customers]![Name] As Surname_Name FROM Customers WHERE (((Customers.Customer_ID)=1));)"

And then I try to assing the output of a desired SQL query to Surname_Name and finally get it to be displayed to the form's caption, like this:

Surname_Name = DoCmd.RunSQL(SQL_String)

Me.Form.Caption = "History of customer: " & Surname_Name

But whenever this happens I get an error dialog box that reads:

Compile Error:
Expected function or variable

and then the RunSQL command gets highlighted and the execution breaks.

 

[stopher]

The RunSQL method is designed to run an Action query and it does not return any value afaik. You are trying to get it to run a select query and then trying to return some value - hence an error.

Since you have the Customer_ID, I would suggest using the DLOOKUP function to get further customer data.

hth
Chris

 

[olspookishmagus]

You were correct!

RunSQL method is indeed used only for Action Queries like UPDATE, DELETE, APPEND.

I did what I wished with the use of the DLOOKUP function.

And here's the corrected part of the Form_Load event of the form:

Private Sub Form_Load()

Dim Surname_Name As String

Surname_Name = DLookup("[Surname]", "Customers", "[Customer_ID] = " & Me.Customer_ID) & _
	" " & _
	DLookup("[Name]", "Customers", "[Customer_ID] = " & Me.Customer_ID)

Me.Form.Caption = "Customer history: " & Surname_Name

End Sub

Thanks a lot, stopher!

 

[namliam]

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

You have fallen to the curse of the DLookup.
If you use it for a single DLookup on a table, you can use it... but doing the exact smae DLookup on one table twice.... to slow...
AirCode & the "missing" peaces you need:

Dim rs as dao.recordset
set rs = currentdb.openrecordset(SQL_String )
Surname_Name = rs!Surname_Name
rs.close
set rs = nothing

 

[stopher]

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

You have fallen to the curse of the DLookup.
If you use it for a single DLookup on a table, you can use it... but doing the exact smae DLookup on one table twice.... to slow...

Very good point. Particularly if you were to scale this up with more DLookups or looping.

In this instance though, I doubt the user will notice.

Chris

 

[namliam]

A double DLookup on a table containing a good number of records on an average network??
Believe you me, the user will notice....

As a general rule tho I tend to NOT use the DLookups, except for very specific tasks... VERY specific tasks... in REAL exceptions....