result query in msgbox

[sven2]

Hello,

I have made a strSQL that returns a textfield.

My question is:

* how can I place te result of this query in a msgbox?
* if the result of this SQL is zero records how can I cancel this msgbox?

Thanks in advance,
Sven.

 

[jal]

Hello,

I have made a strSQL that returns a textfield.

My question is:

* how can I place te result of this query in a msgbox?
* if the result of this SQL is zero records how can I cancel this msgbox?

Thanks in advance,
Sven.

I'm not sure where you are executing your query. In many situations you can use DLOOKUP to put a query result into a variable. Then you can call Msgbox(variable1). Here's my notes:


The Dlookup function is much like VB.Net's Command.ExecuteScalar function. It looks up one value in a desired table and therefore is a SELECT that returns a single value - you will therefore neeed to provide a WHERE clause geared to return a single value.
SELECT FirstName FROM Customers WHERE CustID = 55
The format is this (note you can lookup a value in a query-output, not just lookup a value in a table).
Dlookup(ColumnName, TableName or QueryName, WHERE clause)
and thus the original query
SELECT FirstName FROM Customers WHERE CustID = 55
translates into this:
Dlookup("FirstName", "Customers", "CustID=55")
be sure to use quotes for strings
SELECT FirstName FROM Customers WHERE LastName LIKE 'Smith'
which is
Dlookup("FirstName", "Customers", "LastName LIKE 'Smith' ")
Again, the WHERE clause must be set up as to return a single value from the column ColumnName. If the WHERE clause is a bad one that returns zero records, the result is Null, which you can detect like this:
Dim var1 As Variant
var1 = DLookup("StudentID", "Students", "Name LIKE 'John' ")
If IsNull(var1) Then MsgBox ("null")
On the other hand, if the WHERE clause is bad in the sense of returning multiple records, you'll get the first record
Dim var1 As Variant
var1 = DLookup("StudentID", "Students", "") '<--- bad where clause
DLOOKUp can be used to verify that a table exists because the MySysobjects table has a list of all tables.
var1 = DLookUp("Name","MSysObjects", "Type = 1 AND Name = 'tblCustomers'")
If IsNull(var1) Then MsgBox ("The table does not exist.")
Although DLookup is convenient, it is a bit limited in that you can't use a complex SELECT query. So instead I sometimes prefer to use:
Private Function ExecuteScalar(ByVal query As String, ByVal ColName As String) As Variant
Dim rs As New ADODB.Recordset
rs.Open query, CurrentProject.Connection
Dim recCount as Long
Do While Not rs.EOF
recCount = recCount + 1
rs.MoveNext
Loop
If recCount > 1 Then
Err.Raise "Your custom Execute Scalar method has returned more than one value."
ElseIf recCount = 0 Then
ExecuteScalar = Null
Else: rs.MoveFirst
ExecuteScalar = rs(ColName)
End If
rs.Close
Set rs = Nothing
End Function