URGENT: Access VBA Error

[Georgenetwork]

I have a code to disallow more than 3 people from being scheduled on any given date. It's working find whenever you scheduling, but when you reach the schedule limit, it throw an error. Someone please help. I have attached copies of the error

 

[MajP]

you cannot set a value in the before update, but you can cancel the before update

provide the message then
cancel = true

 

[Georgenetwork]

Thank, but still not working and I am finding difficulties. Can i upload my db or can you kindly assist me remotely (teamviewer)? Please, this is urgent

 

[Gasman]

If your count is > 3 then it is too late I would have thought?, you already have more than you require?

I've fallen into this trap before, as the dlookup will not take into account your current record if it is not saved, and of course you are testing to see if you can save it.?

I am assuming that the query is referring to the form date for criteria.?

 

[Georgenetwork]

Can I give you teamviewer access please. This is urgent

 

[Gasman]

Can I give you teamviewer access please. This is urgent

I'm afraid not.
Please post your code as you have it now.

 

[Georgenetwork]

Private Sub BaselineScheduleDate_BeforeUpdate(Cancel As Integer)
Dim pIDWeek As Integer
pIDWeek = Weekday(BaselineScheduleDate)
If pIDWeek = 1 Or pIDWeek = 7 Or pIDWeek = 6 Then '1=sunday, 6=friday, 7=saturday'
Cancel = True 'Set to true to abort update'
MsgBox "Participant's scheduled date can't be on Friday or weekend"
End If


Dim intSched1 As Integer

intSched1 = Nz(DLookup("[SumOfDateCount]", "Qry1BaselineSum"), 0)

If intSched1 > 3 Then

Me.BaselineScheduleDate = Null
MsgBox "There are already " & intSched1 & " participants scheduled for this date!", vbOKOnly, "Cant Schedule"

Else

If IsNull(Me.BaselineScheduleDate) Then

Else

MsgBox "There are " & intSched1 & " Participant scheduled for this date!", vbOKOnly, "Ok To Schedule"

End If

End If

End Sub

Private Sub BaselineStatus_BeforeUpdate(Cancel As Integer)

 

[MajP]

As I said, you cannot set the value of the control. You can cancel it
Cannot do below

Me.BaselineScheduleDate = Null

you can do
cancel = true
me.undo

Maybe

Private Sub BaselineScheduleDate_BeforeUpdate(Cancel As Integer)
Dim pIDWeek As Integer
Dim intSched1 As Integer

pIDWeek = Weekday(BaselineScheduleDate)
If pIDWeek = 1 Or pIDWeek = 7 Or pIDWeek = 6 Then '1=sunday, 6=friday, 7=saturday'
  MsgBox "Participant's scheduled date can't be on Friday or weekend"
  Cancel = True 'Set to true to abort update'
  me.undo
  exit sub
End If

intSched1 = Nz(DLookup("[SumOfDateCount]", "Qry1BaselineSum"), 0)
If intSched1 > 3 Then
  MsgBox "There are already " & intSched1 & " participants scheduled for this date!", vbOKOnly, "Cant Schedule"
  cancel = true
  me.undo
Else
  If IsNull(Me.BaselineScheduleDate) Then
    'something missing
  Else
    MsgBox "There are " & intSched1 & " Participant scheduled for this date!", vbOKOnly, "Ok To Schedule"
  End If
End If
End Sub

 

[Gasman]

You have not changed anything.?

Private Sub BaselineScheduleDate_BeforeUpdate(Cancel As Integer)
Dim pIDWeek As Integer
pIDWeek = Weekday(BaselineScheduleDate)
If pIDWeek = 1 Or pIDWeek = 7 Or pIDWeek = 6 Then '1=sunday, 6=friday, 7=saturday'
Cancel = True 'Set to true to abort update'
MsgBox "Participant's scheduled date can't be on Friday or weekend"
End If


Dim intSched1 As Integer

intSched1 = Nz(DLookup("[SumOfDateCount]", "Qry1BaselineSum"), 0)

If intSched1 > 3 Then

Me.BaselineScheduleDate = Null
MsgBox "There are already " & intSched1 & " participants scheduled for this date!", vbOKOnly, "Cant Schedule"

Else

If IsNull(Me.BaselineScheduleDate) Then

Else

MsgBox "There are " & intSched1 & " Participant scheduled for this date!", vbOKOnly, "Ok To Schedule"

End If

End If

End Sub

Private Sub BaselineStatus_BeforeUpdate(Cancel As Integer)

 

[Georgenetwork]

Thank you all so much. It's working perfectly. I humbly appreciate