Message Box Powered By A Query?

[Gojira999]

I have built an audit database using MS Access where users enter various items of data & a record is saved for review later.

At the offset, the auditor enters the reference for the file being audited, and presses a "Check History" button. This runs a query against the audit table to check if the file has been audited previously. If it has, then a pop up form is displayed showing the reference & some other data. If no record is found, the same form pops up, but showing its default value of "no matches."

What I would like to do is replace the pop up form with a message box, containing the same information.

Whilst I have built message boxes before, I have never done so where it is driven by a query.

Please can anybody offer me any pointers?

Thanks as always.

 

[KenReay]

Hi

Unless I completely miss the point:

Dim strMessage as String

(assuming DAO)

Set Rs = currentdb().querydef("queryname").openrecordset
If Rs.RecordCount < 1 Then
strMessage = "Not Previously Audited"
Else
strMessage = "Previously Audited"
end if
msgbox strMessage

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gojira999]

Thanks for the reply.

I have entered the snippet as follows:

Private Sub Command53_Click()
Dim strMessage As String
Set Rs = CurrentDb().QueryDef("ClaimDuplicateQry").OpenRecordset
If Rs.RecordCount < 1 Then
   strMessage = "Not Previously Audited"
Else
   strMessage = "Previously Audited"
End If
MsgBox strMessage
End Sub

But unfortunately without the desired result i.e. no message box. Strangely, the button property is not showing "event procedure" in the events field? I may be missing something obvious here

 

[KenReay]

Hi

Sounds like you do not have the code "linked" to the button

Open the code

Edit\Copy

Delete the code

go to button properties, and select on click

click the code builder and paste code in

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gojira999]

Thanks for the reply.

I have now successfully attached the code, but have been presented with the following:

Compile Error: Method or Data member not found

I am running the database on Access 97 - might that be the problem?

 

[KenReay]

Hi

It would help if you said on which line

also the code I posted was an approximate example, not working code, and I had not dimmed Rs, have you?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gojira999]

Sorry, still a bit (actually, very) green on vb! How do I define Dim's?

.QueryDef is highlighted.

 

[KenReay]

Hi

Dim strMessage As String
so

Dim Rs as DAO.Recordset

Also

Set Rs = CurrentDb().QueryDef("ClaimDuplicateQry").OpenRecordset

should be all on one line, it is just wrapped here becuase of the form size

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gojira999]

Thanks Ken.

I have posted the following code;

Private Sub Command53_Click()
Dim strMessage As String
Dim Rs As DAO.Recordset
Set Rs = CurrentDb().QueryDef("ClaimDuplicateQry").OpenRecordset

If Rs.RecordCount < 1 Then
   strMessage = "Not Previously Audited"
Else
   strMessage = "Previously Audited"
End If
MsgBox strMessage
End Sub

But unfortunately still get the same message.

I tried putting it in one line but got an "expected end of statement" message.

 

[KenReay]

Hi

it is

Set Rs = CurrentDb().QueryDefs("ClaimDuplicateQry").OpenRecordset

Note QuefyDefs, not QueryDef

You can deduce this quite easily using intellesense it will show you a list of possible entries as you type

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gojira999]

We are getting there!

Still an error, but this time:

Run-time error '3061':
Too few parameters. Expected 1.

 

[KenReay]

OK

So does "ClaimDuplicateQry" run if you run it from the db Window?

does it have parameters (ie criteria)?, if yes, you need to set the value of the parameters using the parameters collection look up paramters collection in help, it includes examples

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK