Problem with recordset statement

[jekirksey]

I have a pop up form when my database opens that is actually opened by another form. I want the user to be able to check a box to turn off the pop up. I have a table with one record and two fields , the number 1 and a Yes/No field to determine if they get the popup (checked is "Show Pop Up") I have a check mark on my pop up that is bound to this single record. Here is the code I have on the On Open Event of my main form "WeeklyReminder" is always "1" and "ShowValue" is the checkbox.
'Declare Variables
Dim ShowEvents As String
Dim rsSet As Recordset

ShowEvents = "SELECT tblShowWeeklyReminder.ShowValue FROM tblShowWeeklyReminder WHERE (((tblShowWeeklyReminder.WeeklyReminder)=1));"
Set rsSet = dBase.OpenRecordset(ShowEvents)
If rsSet = 1 Then
DoCmd.OpenForm "frmThisWeeksAppts"
End If
End Sub

I get a type mismatch on If rsSet = 1 but I am not sure how this should be stated.
Any help would be appreciated.

 

[John.Woody]

Try the following:

ShowEvents = "SELECT tblShowWeeklyReminder.ShowValue FROM tblShowWeeklyReminder WHERE (((tblShowWeeklyReminder.WeeklyReminder)= " & 1 & "));"
Set rsSet = dBase.OpenRecordset(ShowEvents)
If rsSet = 1 Then
DoCmd.OpenForm "frmThisWeeksAppts"
End If
End Sub

HTH
John

 

[jekirksey]

I wound up giving up on this method, instead what I did was create a query
SELECT tblShowWeeklyReminder.WeeklyReminder, tblShowWeeklyReminder.ShowValue
FROM tblShowWeeklyReminder
WHERE (((tblShowWeeklyReminder.WeeklyReminder)=1) AND ((tblShowWeeklyReminder.ShowValue)=True));

and then use the VBA OnOpen Event for the main form to be

Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rsShowPopUp As Recordset

Set db = CurrentDb()
Set rsShowPopUp = db.OpenRecordset("qryShowPopUp")

If rsShowPopUp.RecordCount > 0 Then

DoCmd.OpenForm "frmThisWeeksAppts"
End If

End Sub

basically it checks to see if there are any records in the query and since the only time there can be records in the query is when the checkbox is checked, it works. Probably not the best method, but I'll take it.