Msgbox message from a Query or Table

[Thomas Schoberl]

Hello all,

I was wondering if this was possible. I am trying to create a message box
vbOKOnly with a message in the line. I have a table with a list of
"Affirmations" in it, and a randomizing query based on it. What I need is
for a random message to appear from the table in the text line of the message
box. Below is my VBA coding that I have been attempting, but I cant seem to
see what the issue is. Any help would be greatly appreciated.

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim rand As String
rand = "SELECT TOP 1 Affirmations.* FROM Affirmations " _
& "ORDER BY rnd(isnull(Affirmations.Affirmation) * 0 + 1);"
MsgBox rand, vbOKOnly, Affirmation

Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

 

[John W. Vinson]

Hello all,

I was wondering if this was possible. I am trying to create a message box
vbOKOnly with a message in the line. I have a table with a list of
"Affirmations" in it, and a randomizing query based on it. What I need is
for a random message to appear from the table in the text line of the message
box. Below is my VBA coding that I have been attempting, but I cant seem to
see what the issue is. Any help would be greatly appreciated.

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click

Dim rand As String
rand = "SELECT TOP 1 Affirmations.* FROM Affirmations " _
& "ORDER BY rnd(isnull(Affirmations.Affirmation) * 0 + 1);"
MsgBox rand, vbOKOnly, Affirmation

Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

This will - if anything - display the SQL string that you're constructing.

I think what you want is to use DLookUp to look up a random record. Create a
new Query using this SQL; name it qryRandAffirmation; and use

MsgBox(DLookUp("Affirmation"), "qryRandAffirmation", vbOKOnly, "Affirmation")

John W. Vinson [MVP]

 

[Thomas Schoberl]

This will - if anything - display the SQL string that you're constructing.

I think what you want is to use DLookUp to look up a random record. Create a
new Query using this SQL; name it qryRandAffirmation; and use

MsgBox(DLookUp("Affirmation"), "qryRandAffirmation", vbOKOnly, "Affirmation")

John W. Vinson [MVP]

MsgBox(DLookup("Affirmation", "qryAffirmation"), vbOKOnly, "Affirmation") =
vbOK

I wrote in the coding into the Click option, but now I am getting an
interesting error. The first one I got was that there was a syntax error. I
found it where the DLookup is (had to move a parenthesis). Then an error
stating " Expected =", so I put the = vbOK at the end. NOW, I am receiving
"Function call on left-hand side of assignment must return Variant or Object"
compile error. This is complicated.

 

[John W. Vinson]

MsgBox(DLookup("Affirmation", "qryAffirmation"), vbOKOnly, "Affirmation") =
vbOK

I wrote in the coding into the Click option, but now I am getting an
interesting error. The first one I got was that there was a syntax error. I
found it where the DLookup is (had to move a parenthesis). Then an error
stating " Expected =", so I put the = vbOK at the end. NOW, I am receiving
"Function call on left-hand side of assignment must return Variant or Object"
compile error. This is complicated.

Apologies! Post in haste, repent at leisure...

I posted using the Function form of MsgBox (which returns a value). You don't
need the value, so you can just use the in-line msgbox:

MsgBox DLookUp("Affirmation"), "qryRandAffirmation", vbOKOnly, "Affirmation"


John W. Vinson [MVP]

 

[Thomas [PBD]]

Thanks John, that was it.

Apologies! Post in haste, repent at leisure...

I posted using the Function form of MsgBox (which returns a value). You don't
need the value, so you can just use the in-line msgbox:

MsgBox DLookUp("Affirmation"), "qryRandAffirmation", vbOKOnly, "Affirmation"


John W. Vinson [MVP]

 

[A Paid Observer]

I'm trying to perform a similar operation, and your code was a tremendous
help! However, I would like for the same message to display for the entire
day whenever the button is clicked, then pull a different random message from
the table after midnight. Does anyone know how to do this?

 

[Thomas [PBD]]

I know this is quite late, but just FYI:

For the way that this was working, it is built into the VBA coding,
therefore you can simply add an additional statement to state what time it is
currently and if the time is between one and another time, then return the
value posted and if not return from the query.

Say:
Public Sub Msgs()
If Format(Now(), "hh") < 16 Then 'set to 4pm currently
MsgBox "Your Message", vbOKOnly, "Title"
Else
MsgBox DLookup("Affirmation"), "qryRandAffirmation", vbOKOnly, "Affirmation"
End If
End Sub