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
        Me![Added to Schedule] = False
        Me![Added to Schedule].Locked = True
    End If
End Sub

Don't worry, I've got it! I prefixed it with:

Set rst = Me.RecordsetClone
On Error Resume Next
On Error GoTo 0
Me.txtCount.Value = rst.RecordCount
intCount = Me.txtCount.Value

Then if the value was zero, it would skip the check code.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.