Include query data in a message box

  • Thread starter Thread starter Steve Scott via AccessMonster.com
  • Start date Start date
S

Steve Scott via AccessMonster.com

You'd think this would be easy but I cannot seem to get the correct
approach. I have a button that closes a form, sets the "security level" of
the user, and opens a menu. If the user level is equal to "administrator"
(from a table not from Access Security) then I want to run a query that
returns a list of documents that are due for auditing. I have the query
working fine. I don't really want the user to see the query results, I
just want a message box with an "okay" that says: Document:
[DocumentNumber] & [DocumentName] & " is due for audit." There needs to be
one message box for each record returned by the query. I'm purposely not
including my code since I think I'm going about it all wrong. Any ideas?

Eternally grateful,
Steve
 
Hi Steve,

I would use something like this -

Dim rst as Recordset

Set rst = dbs.OpenRecordSet("QueryName")
If rst.RecordCount > 0 then
with rst
.MoveFirst
Do While Not .EOF
msgbox "Document: " & !DocumentNumber & " " & !DocumentName & _
" is due for audit", vbOKOnly
.MoveNext
Loop
End With
End If

Jim
 
,Steve,

For each row in you query, you could do something like this:
Dim strDocMsg as String

strDocMsg = rst.[Document Number] & " - " & rst.[Document Name ] & _
"Is ready for Auditing"

msgbox(strDocMsg,whatever options,...)

If you want to present all documents in one message box:

Loop through each row in your query results Like:

strDocMsg = ""
Do while not rst.EOF
strDocMsg = strDocMsg &rst.[Document Number] & " - " & _
rst.[Document Name ] & vbCrLf
Loop
strDocMsg = Left(strDocMsg, Len(strDocMsg)-1) ' Removes last line
feedmsgbox(strDocMsg,Options,"Documents Ready For Auditing")

The only danger here is that the MsgBox will only allow 255 characters.
 
Thanks for the help Jim & Klatuu...

I'm getting a runtime error (13) type mismatch on the following code:
(note: it's coming on the line beginning: Set rst = dbs... etc.)

Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qryPolicies_and_Procedures_Audit_Due")
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
MsgBox "Document: " & !DocumentNumber & " " & !DocumentName & "
is due for audit", vbOKOnly
.MoveNext
Loop
End With
End If

Any idea what's wrong?
 
Oops! figured it out myself. I left out the DAO.

Thanks for the great help guys. I had tunnel vision today.

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPolicies_and_Procedures_Audit_Due")

If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
MsgBox "Document: " & !DocumentNumber & " " & !DocumentName & "
is due for audit", vbOKOnly
.MoveNext
Loop
End With
End If
 
Back
Top