msgbox with SQL result?

[JJman]

I know next to nothing about VBA, but can someone give me the syntax for making a msgbox pop up that displays the results of a SQL statement and has an "OK" button at the bottom?

Thanks!

 

[jrbarnett]

JJMan

An SQL statement can return 0, 1 or many records, each of which can consist of 1 or more than 1 field. Some SQL statements don't return records at all.
To produce VBA code to display the results of an SQL statement, we would need to know what the SQL statement is, and then loop through it from top to bottom to retrieve every row concatenating the results in a string variable as we go, then displaying it on the screen when it has finished.

John

 

[sfm6s524]

Or if it's an action query, you can use the Execute method, and capture the RecordsAffected property and display message like "X records updated."

 

[JJman]

Actually it's a simple query for a receiving process that just returns a number (Rec_Batch_Id) from a table (tbl_Receiving_Record) where the username (db_comment_by) exists and it's the most recent date/time (timestamp_received) under that username. At the bottom of the msgbox they would have a simple "Ok" button to click. This is the SQL:

SELECT tbl_Receiving_Record.Rec_Batch_Id
FROM tbl_Receiving_Record
WHERE (((tbl_Receiving_Record.timestamp_received)=(select max(timestamp_received) from tbl_Receiving_record where tbl_Receiving_record.db_comment_by = [forms]![frm_receiving]![db_comment_by])));

 

[sfm6s524]

Assuming you're using DAO:

Dim DB as DAO.Database
Dim RS as DAO.Recordset
Dim SQL as String

Set DB=CurrentDB()

SQL = "SELECT tbl_Receiving_Record.Rec_Batch_Id " & _
"FROM tbl_Receiving_Record " & _
"WHERE (((tbl_Receiving_Record.timestamp_received)=(select max(timestamp_received) " & _
"FROM tbl_Receiving_record where " & _
"tbl_Receiving_record.db_comment_by = [forms]![frm_receiving]![db_comment_by])));"

Set RS = DB.OpenRecordset(SQL)
If RS.EOF Then
MsgBox "No batch ID found.", vbOKOnly + vbInformation, "Last Batch"
Else
MsgBox "Last batch ID is " & RS![Sales Customer Code] & ".", vbOKOnly + vbInformation, "Last Batch"
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

 

[sfm6s524]

Oops,

If RS.EOF Then
MsgBox "No batch ID found.", vbOKOnly + vbInformation, "Last Batch"
Else
MsgBox "Last batch ID is " & RS![Rec_Batch_ID] & ".", vbOKOnly + vbInformation, "Last Batch"
End If

 

[JJman]

Thanks for your help!