I have a checkbox called 'Added to Schedule' on a form (booking sheet) which is supposed to be ticked if the job appears on another table (schedule). It usually works but sometimes if the job is then unscheduled or moved around, this checkbox does not get unticked. It can also be vice versa, where the box is not ticked even though the job is on the schedule.
So I wrote an On Load event to check if the job is on the schedule (using the unique job number in a DLookup). If it finds the job on the schedule, it ticks the box. If it doesn't, it unticks.
It works well, but the only problem is, when I look at a booking sheet that has no jobs added to it yet. The booking sheet record has several required fields, such as 'Date' and 'Specific Job No' (between 1 and 99).
When the On Load event runs, it seems to recognise the top empty line as a record to check, and Access then recognises it as an incomplete record. When you then try to close the booking sheet or click somewhere else, it's waiting for you to enter info in the rest of the fields and gives you a validation error ('please enter the date', etc).
How do I get around this? I don't want my Form_Load event to run if there are no lines on the current booking sheet (which is a subform on a form which holds some of the information, such as the account manager).
Private Sub Form_Load() Dim strJob As String Dim strSpec As String strSpec = Format(Me.Specific_Job_No.Value, "00") strJob = Left(Me.Parent.JobRef.Value, 18) + strSpec If DCount("*", "tblWebSchedule", "[JobNo] = " & """" & strJob & """") = 1 Then Me![Added to Schedule] = True Me![Added to Schedule].Locked = True Else Me![Added to Schedule] = False Me![Added to Schedule].Locked = True End If End Sub