G
Guest
Hello. Since I am a beginner in VBA programming code, this is my first
question to ask for help.
How can I insert VBA codes to run a query to determine how many times “wardâ€
was selected previously and display a message if there are more than 5
“Wards†disposition selected by user? The message would display like this,
"Too many patients are scheduled for this day. Please schedule on anther
day.†Then a user will be forced to select another disposition field.
FYI – I will be using this vba code in Plastic and Orthopedic forms by using
Before Update event to determine the number of wards previously selected by
users. There should be no more than 5 wards in either forms at any given time
in a day.
How and Where do I insert query to verify the counter in the VBA code shown
below? Yes, I have already created a query to count the wards. The query is
written as:
SELECT Count([Surgical Procedures].Disposition) AS CountOfDisposition
FROM [Surgical Procedures]
WHERE ((([Surgical Procedures].Disposition)="ward"));
VBA code:
Private Sub Disposition_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindLast "[Disposition] = '" & Me!Disposition & "'" ' is this a
duplication?
If Not rs.NoMatch Then ' yes it is match
Cancel = True ' don't select Ward in the disposition field table
strMsg = "This Ward already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.Disposition.Undo ' just erase the Ward
End If
End If
End Sub
question to ask for help.
How can I insert VBA codes to run a query to determine how many times “wardâ€
was selected previously and display a message if there are more than 5
“Wards†disposition selected by user? The message would display like this,
"Too many patients are scheduled for this day. Please schedule on anther
day.†Then a user will be forced to select another disposition field.
FYI – I will be using this vba code in Plastic and Orthopedic forms by using
Before Update event to determine the number of wards previously selected by
users. There should be no more than 5 wards in either forms at any given time
in a day.
How and Where do I insert query to verify the counter in the VBA code shown
below? Yes, I have already created a query to count the wards. The query is
written as:
SELECT Count([Surgical Procedures].Disposition) AS CountOfDisposition
FROM [Surgical Procedures]
WHERE ((([Surgical Procedures].Disposition)="ward"));
VBA code:
Private Sub Disposition_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iAns As Integer
Dim rs As DAO.Recordset ' reserve a variable
Set rs = Me.RecordsetClone ' set it to this form's recordsource
rs.FindLast "[Disposition] = '" & Me!Disposition & "'" ' is this a
duplication?
If Not rs.NoMatch Then ' yes it is match
Cancel = True ' don't select Ward in the disposition field table
strMsg = "This Ward already exists!" & _
" Click Yes to go to it, Cancel to retry"
iAns = MsgBox(strMsg, vbYesCancel)
If iAns = vbYes Then
Me.Undo ' erase the data on the form
Me.Bookmark = rs.Bookmark ' move to the record
Else
Me.Disposition.Undo ' just erase the Ward
End If
End If
End Sub