URGENT: Access VBA Error (1 Viewer)

Georgenetwork

Registered User.
Local time
Today, 04:18
Joined
Nov 1, 2019
Messages
21
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
 

Attachments

  • Untitled.png
    Untitled.png
    42.9 KB · Views: 43
  • Untitled1.png
    Untitled1.png
    22.4 KB · Views: 50

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:18
Joined
May 21, 2018
Messages
8,463
you cannot set a value in the before update, but you can cancel the before update

provide the message then
cancel = true
 

Georgenetwork

Registered User.
Local time
Today, 04:18
Joined
Nov 1, 2019
Messages
21
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

Enthusiastic Amateur
Local time
Today, 11:18
Joined
Sep 21, 2011
Messages
14,038
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

Registered User.
Local time
Today, 04:18
Joined
Nov 1, 2019
Messages
21
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

You've got your good things, and you've got mine.
Local time
Today, 07:18
Joined
May 21, 2018
Messages
8,463
As I said, you cannot set the value of the control. You can cancel it
Cannot do below
Code:
Me.BaselineScheduleDate = Null
you can do
cancel = true
me.undo

Maybe
Code:
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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 11:18
Joined
Sep 21, 2011
Messages
14,038
You have not changed anything.? :confused:

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)
 

Users who are viewing this thread

Top Bottom