You are here:

Using MS Access/Run query but not display it for a user

Advertisement


Question
Hi Scott<

I need to execute/run a Select query (values Hour1-0to15... are calculated) in such a way that:
1. It stays hidden for a user (is not displayed)
2. If statement is executed on the results of the query
3. When condition of If statement is met a message box pops up otherwise nothing happens (there is a further code to execute).
4. All the above is done when user clicks a command button

I have the following VBA code written but it does not work. It only results in opening the query:
----------------------------------
Private Sub Command11_Click()

Dim dbs As DAO.Database
Set dbs = CurrentDb

DoCmd.OpenQuery "qryCheck"
If Hour1-0to15 OR Hour1-16to30 OR Hour1-31to45 OR Hour1-46to60 > 1 Then
MsgBox "Data Entry Error: You CANNOT enter 1 more than once for the same time. You can only report the delivery of one type of service for a 15-min time interval. Please correct an error before clicking Submit.", vbOKOnly
Else
GoTo EM
End If

'there is a further code here

EM:

End Sub

Answer
First get rid of the OpenQuery method, its not necessary. Second, does qryCheck produce more than one row? If it only produces 1 row, then add a column to the query:

CheckHours: Hour1-0to15 + Hour1-16to30 + Hour1-31to45 + Hour1-46to60

Then use the following code:

If DLookup("[CheckHours]","qryCheck") > 1 Then

MsgBox "Data Entry Error: You CANNOT enter 1 more than once for the same time. You can only report          the delivery of one type of service for a 15-min time interval. Please correct an error before clicking Submit.", vbOKOnly

Else

GoTo EM

End If  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.