Is this Possible?

[DAHMB]

I have a form that tracks absentee and and reaassigning employees positions. The key fields in the form are EmployeeID, JobNumber, HiredEmployeeeID, DayOfWeek, ShiftID.

What we do is that when an emplyye calls out sick etc we will fill the vacancy wiith another employee from the shift who works a less important assignment.

I would like to be able to enter an employee into the HiredEmployeeId field have a check run to see if the employee is working the same shiftID and day according to a table I have called tblJobsPicked and then have that employees EmployeeID entered as a ne entry into the form after I complete my entry I started. Is this possible???

Thanks

[Scott Price]

Yes.

Regards,
Scott

[Scott Price]

'Scuse the joke, but your question demands a pat answer! :-)

What you are asking is indeed possible.

Just thinking off the top of my head, you'll need something like a little piece of code that tests if the employee is on the job or not, then if this evaluates true plug their employeeID into the sick employee's slot. How are you at writing VBA code?

Regards,
Scott

[DAHMB]

'Scuse the joke, but your question demands a pat answer! :-)

What you are asking is indeed possible.

Just thinking off the top of my head, you'll need something like a little piece of code that tests if the employee is on the job or not, then if this evaluates true plug their employeeID into the sick employee's slot. How are you at writing VBA code?

Regards,
Scott

Thank you for the reply.
I am losy at writing VBA code. I am self teaching myself as I go, and have not gotten too far. If you have any suggestions or starting ideas, I would be greatly in you debt.
Thank you
Dan

[DAHMB]

Thank you for the reply.
I am losy at writing VBA code. I am self teaching myself as I go, and have not gotten too far. If you have any suggestions or starting ideas, I would be greatly in you debt.
Thank you
Dan

I would also need to enter their JobId in the JobID slot as well
Thanks
Dan

[DAHMB]

I would also need to enter their JobId in the JobID slot as well
Thanks
Dan

oop sorry, Actually What I need to do is test if the employee is workin and if so enter their JobID into the JobID slot and "Open" into the HiredEmployee slot.
Thanks sorry for me confusion.
Dan

[Scott Price]

oop sorry, Actually What I need to do is test if the employee is workin and if so enter their JobID into the JobID slot and "Open" into the HiredEmployee slot.
Thanks sorry for me confusion.
Dan

What flag do you have to show whether the employee is working or not?

You could write a query using this flag as a WHERE clause to populate a combo box that shows available (i.e. on shift) employees, including a sort by/ranking system for their current activity. Then you could capture the selection from the combo box to populate your replacement needs.

I'm out the door for a couple of hours, but will check back in this afternoon.

Regards,
Scott

[DAHMB]

What flag do you have to show whether the employee is working or not?

You could write a query using this flag as a WHERE clause to populate a combo box that shows available (i.e. on shift) employees, including a sort by/ranking system for their current activity. Then you could capture the selection from the combo box to populate your replacement needs.

I'm out the door for a couple of hours, but will check back in this afternoon.

Regards,
Scott

The form I am working with is filtered by the day of week as entered in the DayOFWeek field in the form. This field also filters a query called qryAbsenteeFormAssignNumber this qry will then only show the employees scheduled to work on the day chosen and the query contains the JobID and the ShiftID. This tells who is working and when. (FYI the JobID is the real key as it is the Primary key in the table that indentifies the ShiftID as well).

I hope this anwsered the question.
Thanks again for your time and help
Dan

[Scott Price]

Your query identifies who SHOULD be working, but how do you identify which employees are actually at work? In other words, how do you filter out scheduled but missing employees on a particular shift?

Do you have a priority ranking system set up for their current job assignment?

You'll create a combo box on your form, using as it's row source an sql statement something like this:

SELECT EmployeeID, EmployeeName FROM qryAbsenteeFormAssignNumber ORDER BY JobPriority

It will be unbound. In the AfterUpdate event of your combo box you'll have some code that populates your empty job with the EmployeeID selection.

The code will look like:

Private Sub cboAbsenteeAssign_AfterUpdate()
 
Me.EmployeeID = Me.cboAbsenteeAssign
 
End Sub

This is making several assumptions, and you'll obviously have to change the names of the combo box, etc to reflect what is happening in your database.

Good luck, and post back to let me know how it goes!

Regards,
Scott

[DAHMB]

Your query identifies who SHOULD be working, but how do you identify which employees are actually at work? In other words, how do you filter out scheduled but missing employees on a particular shift?

Do you have a priority ranking system set up for their current job assignment?

You'll create a combo box on your form, using as it's row source an sql statement something like this:

SELECT EmployeeID, EmployeeName FROM qryAbsenteeFormAssignNumber ORDER BY JobPriority

It will be unbound. In the AfterUpdate event of your combo box you'll have some code that populates your empty job with the EmployeeID selection.

The code will look like:

Private Sub cboAbsenteeAssign_AfterUpdate()
 
Me.EmployeeID = Me.cboAbsenteeAssign
 
End Sub

This is making several assumptions, and you'll obviously have to change the names of the combo box, etc to reflect what is happening in your database.

Good luck, and post back to let me know how it goes!

Regards,
Scott

Who is actually working is done in a different query. All I need is to check the qryAbsenteeFormAssignNumber to see if the Employee I just entered into the HiredEmployeeID field is listed in the qryAbsenteeFormAssignNumber and if so does the ShiftID for the employee match the ShiftID that for the Job he is working under the HiredEmployee and if so I want to have a new record entered listing that employee his JobID entered in the new record. There are more fields to fll out as well but if you can get me started I can wing from there probably. I think I need something similar to this code I found But I can't figure out how to adapt it to my needs.

> Private Sub txtPolicyNumber_BeforeUpdate(Cancel As Integer)
> Dim db As DAO.Database, rs As DAO.Recordset
> Dim intnewrec As Integer
> 
> Set db = CurrentDb()
> Set rs = db.OpenRecordset("Select PolicyNumber FROM tblClientSurveys
> WHERE Policynumber ='" & Me.txtPolicyNumber & "'")
> 
> intnewrec = Me.NewRecord
 
If rs.RecordCount <> 0 And Me.Newrecord Then
MsgBox "Policy Number Already Exists!"
Cancel = True
Me.Undo
End If
End Sub
 

Not that the above would even be close but I think it is along the lines of what I am looking for, I could easily change the field names to mine if I just knew how to get it to work

Please help
Thanks
Dan

[Scott Price]

Hi Dan,

Excuse the wait, I was doing some testing on this code... It's a little inelegant since the .Seek method requires a table to search, which means we need to create one from the existing query, create a searchable index for it, then delete it at the end, but this should point you in the right direction. The text box names, etc reflect names I used in my test database, so you will need to change them to reflect your needs.

Private Sub Text0_AfterUpdate()
 
Dim db As Database, rs As Recordset, idx As Index
Dim empID As Integer
 
empID = Me.Text0
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin")
DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)")
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAbsenteeAssign")
 
With rs
    .Index = "EmployeeID"
    .Seek ">=", empID
    If .NoMatch Then
        MsgBox ("The requested employee is not on shift today.")
    Else
        Me.Text1 = empID
    End If
End With
 
rs.Close
db.TableDefs.Delete "tblAbsenteeAssign"
DoCmd.SetWarnings True
End Sub
 

Regards,
Scott

[DAHMB]

Hi Dan,

Excuse the wait, I was doing some testing on this code... It's a little inelegant since the .Seek method requires a table to search, which means we need to create one from the existing query, create a searchable index for it, then delete it at the end, but this should point you in the right direction. The text box names, etc reflect names I used in my test database, so you will need to change them to reflect your needs.

Private Sub Text0_AfterUpdate()
 
Dim db As Database, rs As Recordset, idx As Index
Dim empID As Integer
 
empID = Me.Text0
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin")
DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)")
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAbsenteeAssign")
 
With rs
    .Index = "EmployeeID"
    .Seek ">=", empID
    If .NoMatch Then
        MsgBox ("The requested employee is not on shift today.")
    Else
        Me.Text1 = empID
    End If
End With
 
rs.Close
db.TableDefs.Delete "tblAbsenteeAssign"
DoCmd.SetWarnings True
End Sub
 

Regards,
Scott

WOW! that looks great!. Thank you I will try it when I get into work later today, and let you know how it goes. I will take me a while to figure it all out as I do not know what most of the code means, but I am learning. Thank you.
Dan

[Scott Price]

I should have commented it out a bit better then! Let me do a commented version so it will be a bit easier to understand.

Regards,
Scott

[Scott Price]

Private Sub Text0_AfterUpdate()
 
Dim db As Database, rs As Recordset  'Not set as DAO (Data Access Objects), just normal database and recordset, either would likely work for this.
Dim empID As Integer 'variable to hold the employee ID number you are testing
 
empID = Me.Text0 ' sets the variable to reflect the number you enter in a text box in this case
DoCmd.SetWarnings False ' turns off the annoying messages that come from the following sql action queries
DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin") ' this is the make table query, it takes from your existing query/table and creates the new table called tblAbsenteeAssign.  The sql should reflect your existing query, just with the added INTO [NewTable] syntax
DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)") ' this sql creates the searchable index in your new table, using the column named EmployeeID as the index
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAbsenteeAssign") ' opens the new table as your recordset to search
 
With rs
    .Index = "EmployeeID" ' the seek method requires you to set the index
    .Seek ">=", empID ' seek the employee ID you are testing
    If .NoMatch Then ' if the employee ID is not found, that means they aren't working, so show the message box
        MsgBox ("The requested employee is not on shift today.")
    Else
        Me.Text1 = empID  ' if there is a match, then they are on shift, and we can plug their employee ID number into whereever you want to use it...  Textbox in this case
    End If
End With
 
rs.Close ' release memory by closing the recordset.
db.TableDefs.Delete "tblAbsenteeAssign" ' delete the temporary table you have created.
DoCmd.SetWarnings True ' turn the annoying messages back on 
End Sub

[DAHMB]

Private Sub Text0_AfterUpdate()
 
Dim db As Database, rs As Recordset  'Not set as DAO (Data Access Objects), just normal database and recordset, either would likely work for this.
Dim empID As Integer 'variable to hold the employee ID number you are testing
 
empID = Me.Text0 ' sets the variable to reflect the number you enter in a text box in this case
DoCmd.SetWarnings False ' turns off the annoying messages that come from the following sql action queries
DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin") ' this is the make table query, it takes from your existing query/table and creates the new table called tblAbsenteeAssign.  The sql should reflect your existing query, just with the added INTO [NewTable] syntax
DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)") ' this sql creates the searchable index in your new table, using the column named EmployeeID as the index
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAbsenteeAssign") ' opens the new table as your recordset to search
 
With rs
    .Index = "EmployeeID" ' the seek method requires you to set the index
    .Seek ">=", empID ' seek the employee ID you are testing
    If .NoMatch Then ' if the employee ID is not found, that means they aren't working, so show the message box
        MsgBox ("The requested employee is not on shift today.")
    Else
        Me.Text1 = empID  ' if there is a match, then they are on shift, and we can plug their employee ID number into whereever you want to use it...  Textbox in this case
    End If
End With
 
rs.Close ' release memory by closing the recordset.
db.TableDefs.Delete "tblAbsenteeAssign" ' delete the temporary table you have created.
DoCmd.SetWarnings True ' turn the annoying messages back on 
End Sub

Thank you very much This works great.
Dan

[Scott Price]

Glad it works for you, Dan, and thanks for posting back!

Regards,
Scott