Run query on form open then display msg box (1 Viewer)

[ianward]

Hi All,

I wonder if anyone out there can help with a huge headache i am getting -
We have a database which logs patient contacts & requests and have a reminder system within which requires users to run a report to see if they have any actions that are required.

What I would like to do is when the database is opened and the opening screen is loaded run some code which checks a query for active reminders then depending on the answer activate a msgbox to warn users they have active reminders and allow them to click "Yes" to view them - I have tried using the following to acheive this but it feels like i am banging my head against a brick wall....

Private Sub Command4_Click()

Dim Answer As Integer
Dim Result As Double
Dim SQLstr As String

SQLstr = "SELECT ReminderCount FROM qryReminderCount"
DoCmd.RunSQL SQLstr

Result = SQLstr

If Result >= 1 Then

    Answer = MsgBox("You have active reminders, Do you wish to view them?", vbYesNo, "Reminder Warning")
    If Answer = vbYes Then
    DoCmd.OpenReport "rptPersonalReminders", acViewPreview
    Else
    
End If
End If


End Sub

I have used the onClick for testing the code and this would ultimatley be onLoad

Could anyone give any assistance at all with this - is this even possible?

Many Thanks

Ian

 

[Rich]

Use DlookUp or DCount on the query

 

[ianward]

Hi Rich,

Many thanks for pointing me in the right direction - it works a treat.

I have posted the working code below if it is of any use to anyone else

Dim Answer As Integer
Dim intX As Integer

intX = DCount("*", "qryReminderCount")


If intX >= 1 Then

    Answer = MsgBox("You have " & intX & " active reminders, Do you wish to view them?", vbYesNo, "Reminder Warning")
    If Answer = vbYes Then
    DoCmd.OpenReport "rptReminderByAdminNext7", acViewPreview
    Else
    
End If
End If

Thanks again

Ian