VBA query help - I've never done this before...

[Opal]

I am trying to run a query from a VBA module in the OnOpen
event of my main form. Basically, if the day of the week is
a Friday, then the following appears in the OnOpen Event:

If Forms![frmMain]![cboDayofWeek] = 6 Then
qryAudit
End If

Then to:

Function qryAudit()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qd = db.QueryDefs("qryAudit")
strSQL = qd.SQL

If "Audit.[AuditTime]" < Date And "Audit.[AuditDate]" = Date Then
SendManagementrpt
End If

End Function

the SQL statement for the qryAudit is as follows:

SELECT Audit.AuditDate, Audit.AuditDay, Audit.AuditTime
FROM Audit
WHERE (((Audit.AuditDay)=6));

So Basically, if its Friday and there is no record for today
then send the management report, if there is a record for
today, then don't send the management report.

Can anyone help me sort this out? Thank you.

 

[Michel Walsh]

I assume the query returns many records.


=================================
Sub onQryAudit() ' avoid having two objects with the same name
' a function and a query.
' Since no result is to be returned, I used a sub, rather than
Function.

Dim db As DAO.Database
Set db = CurrentDb()


Dim rst AS DAO.Recordset
Set rst=db.OpenRecordset("qryAudit")

While Not rst.EOF ' as long as there is a record not visited yet

if rst!AuditTime< Date() AND rst!AuditDate = Date() Then
SendManagementReport rst ' I add the actual recordset as
argument
end if

rst.MoveNext ' next record, if any
Wend


End Sub
=====================================


Note: you can call fields from the recordset using either

rst!AuditTime

, either using

rst.Fields("AuditTime")




Hoping it may help,
Vanderghast, Access MVP

 

[Opal]

I assume the query returns many records.

=================================
Sub onQryAudit()  ' avoid having two objects with the same name
        ' a function and a query.
        ' Since no result is to be returned, I used a sub, rather than
Function.

    Dim db As DAO.Database
        Set db = CurrentDb()

    Dim rst AS DAO.Recordset
        Set rst=db.OpenRecordset("qryAudit")

        While Not rst.EOF ' as long as there is a record not visited yet

            if rst!AuditTime< Date()  AND rst!AuditDate = Date() Then
                SendManagementReport rst   ' I add the actual recordset as
argument
            end if

            rst.MoveNext    ' next record, if any
        Wend

End Sub
=====================================

Note:  you can call fields from the recordset using either

       rst!AuditTime

, either using

         rst.Fields("AuditTime")

Hoping it may help,
Vanderghast, Access MVP

I am trying to run a query from a VBA module in the OnOpen
event of my main form.  Basically, if the day of the week is
a Friday,  then the following appears in the OnOpen Event:

If Forms![frmMain]![cboDayofWeek] = 6 Then
   qryAudit
   End If

Then to:

Function qryAudit()

   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim strSQL As String
   Set db = CurrentDb()
   Set qd = db.QueryDefs("qryAudit")
   strSQL = qd.SQL

   If "Audit.[AuditTime]" < Date And "Audit.[AuditDate]" = Date Then
       SendManagementrpt
       End If

End Function

the SQL statement for the qryAudit is as follows:

SELECT Audit.AuditDate, Audit.AuditDay, Audit.AuditTime
FROM Audit
WHERE (((Audit.AuditDay)=6));

So Basically, if its Friday and there is no record for today
then send the management report, if there is a record for
today, then don't send the management report.

Can anyone help me sort this out?  Thank you.- Hide quoted text -

- Show quoted text -

Actually, Michel.. I have been re-thinking my logic and the query
should only return one result, not several.... So I was re-thinking
how I should approach the results..... I re-wrote the SQL for the
query
to:

SELECT Audit.AuditDate, Audit.AuditDay, Audit.AuditTime
FROM Audit
WHERE (((Audit.AuditDate)=Date()) AND ((Audit.AuditDay)=6));

since the record is written only once to the table when the report is
sent, there should not be multiple instances in the table or query
as this Sub is to be written to prevent that, i.e. the report is only
sent once on Friday the first time the database is opened. It
will be opened multiple times through the course of the day by
multiple users. So, I thought a statement in the Sub along the
following lines would be more appropriate:

If 0 = DCount("*", "qryAudit") Then
SendManagementrpt
End If

(I found this after some digging in a response you gave in 2001!)