Using checkbox values from one form to validate field on another form

[jmcdonald]

Hi...
I'm new to seeking help from this group, but I have appreciated reading
your help to others for some time. I am developing an Access 2000 db.
I have a FORM which makes assignments of courses to rooms for a school.
On this form the fields CourseID and RoomID are foreign keys to the
Courses and Rooms tables. After selecting a CourseID on the form and
then a RoomID, I would like to enter code into the LostFocus(?) or
OnChange(?) property of the RoomID to check and see if a required
component for the Course is available in the Room. The way I want to do
this is for the code to go to the Courses TABLE and compare the value
of a checkbox there to the value of a checkbox in the Rooms TABLE. If
the Course checkbox = TRUE for needing the component and the Rooms
checkbox = FALSE for having the component, then I want to generate an
error message saying that that Course cannot be assigned to that room.
I suppose I would also need to deal with NULL values in either table. I
know SQL pretty well, but very little vb. Can anyone help me??? Thanks.

 

[Guest]

The place to do this would be in the Before Update event of RoomsID. The
Change event of any control should be used carefully as it fires after each
keystroke. The LostFocus event or the After Update event would be too late,
because you would want to know if the room is available before you update the
value in the RoomsID. I would suggest a pair of DLookup functions - one for
each table.
When you say a check box in a table, I assume you are actually referring to
a Yes/No data type field. Tables don't really have check boxes. The
advantage of this approach is that the Before Update event can be canceled so
the value in the field is not changed, and the cursor will stay in the
RoomsID control.

Dim blnCourseNeedsRoom as Boolean
Dim blnRoomAvailable as Boolean

blnCourseNeedsRoom = Nz(DLookup("[chkboxfieldname]", "CourseTable", _
"[CourseID] = '" & Me.CourseID & "'"),False)
If blnCourseNeedsRoom = False Then
'What do you do if the course doesn't need a room?
Else
blnRoomAvailable = Nz(DLookup("[chkboxfieldname]", "RoomsTable", _
"[RoomsID] = '" & Me.RoomsID & "'"), False)
If blnCourseNeedsRoom = True and blnRoomAvailable = False Then
MsgBox "Room is Not Available"
Cancel = True
End If
End If

The Nulls are handled with the Nz function. If the course or room is not
found in the table or if the value of the field is Null, the Nz function will
return False.