Prevent form from opening if no associated records?

[Pat Dools]

Hello,

I have a database with a Switchboard that has a 'Subject' drop-down list.
Once you pick a Subject, you can navigate either to the 'Subject_Info' (one
record per Subject) form or to the 'Subject_Attendance' form (one or more
records per Subject) via a Command button utlizing the 'DoCmd.OpenForm...'
function. If you have associated 'Attendance' records, then you will be
brought to them based on the Subject you chose on the Switchboard. If there
are no associated 'Attendance' records, I would like a 'Yes/No' message box
to pop up to say, 'This Subject has no Attendance Records. Would you like to
enter a new record?'. This way the user knows that they are on a new record
and then can choose whether or not to create that new record.

My guess is that I have to evaluate the 'SubjectID' associated w/ the name
in the drop-down box on the Switchboard and then look at the 'SubjectID'
field in the 'Attendance' table and see if there are any matching records.
What would the code be that I would put behind the 'Attendance' button on the
Switchboard to accomplish this?

Thank you.

 

[Ken Sheridan]

You can either check for any rows in the second form's recordset and cancel
its Open event, or use the DLookup function before opening the form to see if
the 'Attendance' table contains any rows which match the selected
'SubjectID', which is in essence what you have described. I'm assuming that
the combo box, cboSubject in the example below, in which you select the
subject has the SubjectID as its hidden bound column, and the SubjectID
fields are of long integer number data type, in which case the code would go
like this:

Const ConMESSAGE = _
"This Subject has no Attendance Records. " & _
"Would you like to enter a new record?"

Dim strCriteria As String
Dim lngSubjectID as Long

' first make sure a subject is selected
If Not IsNull(Me.cboSubject) Then
lngSubjectID = Me.cboSubject
strCriteria = "SubjectID = " & lngSubjectID
' check to see if any rows exist in Attendance table
If Not IsNull(DLookup("SubjectID", "Attendance", strCriteria)) Then
' open form filtered to selected subject
DoCmd.OpenForm "Subject_Attendance", _
WhereCondition:=strCriteria
Else
' get user confirmation to insert new record into Attendance table
If MsgBox(ConMESSAGE, vbQuestion + vbYesNo, _
"Add New Record") = vbYes Then
' open form at new record
DoCmd.OpenForm "Subject_Attendance", _
DataMode:=acFormAdd
' assign selected SubjectID to form
Forms("Subject_Attendance").SubjectID = lngSubjectID
End If
End If
Else
MsgBox "No subject selected.", vbExclamation, "Invalid Operation"
End If

Ken Sheridan
Stafford, England

 

[Pat Dools]

This code rocks! Thanks, Ken!

Patrick

You can either check for any rows in the second form's recordset and cancel
its Open event, or use the DLookup function before opening the form to see if
the 'Attendance' table contains any rows which match the selected
'SubjectID', which is in essence what you have described. I'm assuming that
the combo box, cboSubject in the example below, in which you select the
subject has the SubjectID as its hidden bound column, and the SubjectID
fields are of long integer number data type, in which case the code would go
like this:

Const ConMESSAGE = _
"This Subject has no Attendance Records. " & _
"Would you like to enter a new record?"

Dim strCriteria As String
Dim lngSubjectID as Long

' first make sure a subject is selected
If Not IsNull(Me.cboSubject) Then
lngSubjectID = Me.cboSubject
strCriteria = "SubjectID = " & lngSubjectID
' check to see if any rows exist in Attendance table
If Not IsNull(DLookup("SubjectID", "Attendance", strCriteria)) Then
' open form filtered to selected subject
DoCmd.OpenForm "Subject_Attendance", _
WhereCondition:=strCriteria
Else
' get user confirmation to insert new record into Attendance table
If MsgBox(ConMESSAGE, vbQuestion + vbYesNo, _
"Add New Record") = vbYes Then
' open form at new record
DoCmd.OpenForm "Subject_Attendance", _
DataMode:=acFormAdd
' assign selected SubjectID to form
Forms("Subject_Attendance").SubjectID = lngSubjectID
End If
End If
Else
MsgBox "No subject selected.", vbExclamation, "Invalid Operation"
End If

Ken Sheridan
Stafford, England