Query Results in Message Box?

[wrek]

Forgive me, I've been away from Access for a few months, roadblocked....

Is there a way I can output the result of a query in a msgbox?

Namely, I've got a table with customers. If the new form's customer matches one of the customers in the table.

I want to output in my Msgbox ("Found:" {customer name). "Is this correct?") And then an OK Cancel option....

help?

 

[Fizzio]

The easiest way I know is by using a recordset (YourRecs) of the table you want to query. If you are searching via a txt field on a form, then set the criteria to the value of that text box. check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acmthFindFirstX.asp for the exact syntax.
You can then set up your msgbox to read MsgBox ("Found " & Forms!SearchForm!txtSearchBox, vbOkCancel, "Customer Search") following the If Not MyRecs.NoMatch property.

hope this helps !

 

[Chris RR]

Off the top of my head (where txtCustNo is the text box on your form), try this. The logic here is that you just need to display the form field, not what's on file:
Dim strMsg As String
Dim strResponse As String
Dim strStyle As String

'lots of stuff goes here...

strMsg = "Found Customer " & me.txtCustNo & "on the file already!"
strStyle = vbOKCancel + vbExclamation
strResponse = MsgBox(strMsg, strStyle, "You blew it")

[This message has been edited by Chris RR (edited 11-06-2001).]

 

[Fizzio]

Chris, what lots of stuff goes here? (Just for my own interest ;-)

 

[Chris RR]

Lots of stuff not directly related to the msgbox.

For my own convenience, I usually put these kinds of Dim statements right up at the top of the form module. They aren't specific to a single sub, can be defined once & used anywhere in this module.

So any other coding in this form could & maybe would fall in between the Dim statements and the actual building of this one msgbox. In this case, the Dlookup would surely be in there somewhere...

 

[Fizzio]

I've never actually used the DLookup. Maybe I'm missing something here!

 

[Chris RR]

OK, I was assuming that wrek would use Dlookup to figure out whether the customer name was already in the table.