Hello,
I am working on an employee scheduling database. The key functions are to have one job where many employees are scheduled through the day and month.
I need validation where an employee can not be scheduled more than once a day no matter how many jobs there are.
My main form is the the event with the Customer Name, Event Date and EventID. In the main form I have several subforms which are games they will be working. These game sumbforms are either visible or not controlled by a check box. I have all the validation done except it only validates the employees on the current subform.
My problem is that I do not know how to see what other forms are visible using VB. I need to find what forms are visible and open the recordset to do my validation criteria to see if the employee is scheduled on a different game for the same date or that specific event.
Here is my code I have so far which seems to work flawless but only for the current game. I have not worked with Access in over 6 years and I do not claim to be an expert programmer. Any help would greatly be appreciated though. I have used info from this board to get some of the code working below!
Once I determine each visible form, I can then move forward and check each table for a duplicate scheduling.
Private Sub Dealer_Name_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim Dealer1 As Variant, Dealer2 As Variant
Dim EventDate1 As Variant, EventDate2 As Variant
Dim EventID1 As Integer, EventID2 As Integer
Dim Counter As Integer, I As Integer
Dim Customer As Variant, Frm As Form
' Return reference to current database.
Set dbs = CurrentDb
' Create a dynaset-type Recordset object based on Events Table.
Set rst = dbs.OpenRecordset("BjDealers", dbOpenDynaset)
'Get Data on Events Form
EventDate1 = Forms.EventInfoEntry.[event date]
EventID1 = Forms.EventInfoEntry.[EventID]
Dealer1 = Me.Dealer_Name
Counter = 0
Me.Customer_Name = Forms.EventInfoEntry.[Customer Name]
Me.Event_Date = Forms.EventInfoEntry.[event date]
'Get Record Count
With rst
On Error Resume Next
.MoveLast
Counter = .RecordCount
'Check if Dealer is scheduled for same event
If Counter = 0 Then
Exit Sub
'Check the number of instances of the dealer and return the value
Else
GoSub ThisEventDealer
GoSub OtherEventDealer
End If
End With
Exit Sub
ThisEventDealer:
With rst
On Error Resume Next
.MoveFirst
For I = 1 To Counter
Dealer2 = ![Dealer Name]
EventID2 = ![EventID]
If Dealer1 = Dealer2 And EventID1 = EventID2 Then
MsgBox Dealer2 & " Is Alread Scheduled For This Event!", vbExclamation, _
"BLACKJACK DEALERS"
'Cancels Update
Me.Dealer_Name.SetFocus
Me.Undo
Exit Sub
Else
.MoveNext
End If
Next
End With
Return
Exit Sub
OtherEventDealer:
For Each Frm In Forms
' Print name of form.
Debug.Print Frm.Name
'MsgBox Frm.Name
Next Frm
With rst
On Error Resume Next
.MoveFirst
For I = 1 To Counter
Dealer2 = ![Dealer Name]
EventID2 = ![EventID]
EventDate1 = Forms.EventInfoEntry.[event date]
EventDate2 = ![event date]
If Dealer1 = Dealer2 And EventDate1 = EventDate2 Then
MsgBox Dealer2 & " Is Alread Scheduled For " & _
EventDate1 & " With " & ![Customer Name] & _
" Event ID " & EventID2, vbExclamation, _
"BLACKJACK DEALERS"
'Cancels Update
Me.Dealer_Name.SetFocus
Me.Undo
Exit Sub
Else
.MoveNext
End If
Next
End With
End Sub
I am working on an employee scheduling database. The key functions are to have one job where many employees are scheduled through the day and month.
I need validation where an employee can not be scheduled more than once a day no matter how many jobs there are.
My main form is the the event with the Customer Name, Event Date and EventID. In the main form I have several subforms which are games they will be working. These game sumbforms are either visible or not controlled by a check box. I have all the validation done except it only validates the employees on the current subform.
My problem is that I do not know how to see what other forms are visible using VB. I need to find what forms are visible and open the recordset to do my validation criteria to see if the employee is scheduled on a different game for the same date or that specific event.
Here is my code I have so far which seems to work flawless but only for the current game. I have not worked with Access in over 6 years and I do not claim to be an expert programmer. Any help would greatly be appreciated though. I have used info from this board to get some of the code working below!
Once I determine each visible form, I can then move forward and check each table for a duplicate scheduling.
Private Sub Dealer_Name_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim Dealer1 As Variant, Dealer2 As Variant
Dim EventDate1 As Variant, EventDate2 As Variant
Dim EventID1 As Integer, EventID2 As Integer
Dim Counter As Integer, I As Integer
Dim Customer As Variant, Frm As Form
' Return reference to current database.
Set dbs = CurrentDb
' Create a dynaset-type Recordset object based on Events Table.
Set rst = dbs.OpenRecordset("BjDealers", dbOpenDynaset)
'Get Data on Events Form
EventDate1 = Forms.EventInfoEntry.[event date]
EventID1 = Forms.EventInfoEntry.[EventID]
Dealer1 = Me.Dealer_Name
Counter = 0
Me.Customer_Name = Forms.EventInfoEntry.[Customer Name]
Me.Event_Date = Forms.EventInfoEntry.[event date]
'Get Record Count
With rst
On Error Resume Next
.MoveLast
Counter = .RecordCount
'Check if Dealer is scheduled for same event
If Counter = 0 Then
Exit Sub
'Check the number of instances of the dealer and return the value
Else
GoSub ThisEventDealer
GoSub OtherEventDealer
End If
End With
Exit Sub
ThisEventDealer:
With rst
On Error Resume Next
.MoveFirst
For I = 1 To Counter
Dealer2 = ![Dealer Name]
EventID2 = ![EventID]
If Dealer1 = Dealer2 And EventID1 = EventID2 Then
MsgBox Dealer2 & " Is Alread Scheduled For This Event!", vbExclamation, _
"BLACKJACK DEALERS"
'Cancels Update
Me.Dealer_Name.SetFocus
Me.Undo
Exit Sub
Else
.MoveNext
End If
Next
End With
Return
Exit Sub
OtherEventDealer:
For Each Frm In Forms
' Print name of form.
Debug.Print Frm.Name
'MsgBox Frm.Name
Next Frm
With rst
On Error Resume Next
.MoveFirst
For I = 1 To Counter
Dealer2 = ![Dealer Name]
EventID2 = ![EventID]
EventDate1 = Forms.EventInfoEntry.[event date]
EventDate2 = ![event date]
If Dealer1 = Dealer2 And EventDate1 = EventDate2 Then
MsgBox Dealer2 & " Is Alread Scheduled For " & _
EventDate1 & " With " & ![Customer Name] & _
" Event ID " & EventID2, vbExclamation, _
"BLACKJACK DEALERS"
'Cancels Update
Me.Dealer_Name.SetFocus
Me.Undo
Exit Sub
Else
.MoveNext
End If
Next
End With
End Sub
