By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Is this Possible?

100+
P: 147
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
Feb 14 '08 #1
Share this Question
Share on Google+
15 Replies


Scott Price
Expert 100+
P: 1,384
Yes.

Regards,
Scott
Feb 14 '08 #2

Scott Price
Expert 100+
P: 1,384
'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
Feb 14 '08 #3

100+
P: 147
'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
Feb 14 '08 #4

100+
P: 147
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
Feb 14 '08 #5

100+
P: 147
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
Feb 14 '08 #6

Scott Price
Expert 100+
P: 1,384
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
Feb 14 '08 #7

100+
P: 147
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
Feb 14 '08 #8

Scott Price
Expert 100+
P: 1,384
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboAbsenteeAssign_AfterUpdate()
  2.  
  3. Me.EmployeeID = Me.cboAbsenteeAssign
  4.  
  5. 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
Feb 14 '08 #9

100+
P: 147
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboAbsenteeAssign_AfterUpdate()
  2.  
  3. Me.EmployeeID = Me.cboAbsenteeAssign
  4.  
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. > Private Sub txtPolicyNumber_BeforeUpdate(Cancel As Integer)
  2. > Dim db As DAO.Database, rs As DAO.Recordset
  3. > Dim intnewrec As Integer
  4. > Set db = CurrentDb()
  5. > Set rs = db.OpenRecordset("Select PolicyNumber FROM tblClientSurveys
  6. > WHERE Policynumber ='" & Me.txtPolicyNumber & "'")
  7. > intnewrec = Me.NewRecord
  8.  
  9. If rs.RecordCount <> 0 And Me.Newrecord Then
  10. MsgBox "Policy Number Already Exists!"
  11. Cancel = True
  12. Me.Undo
  13. End If
  14. End Sub
  15.  
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
Feb 14 '08 #10

Scott Price
Expert 100+
P: 1,384
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_AfterUpdate()
  2.  
  3. Dim db As Database, rs As Recordset, idx As Index
  4. Dim empID As Integer
  5.  
  6. empID = Me.Text0
  7. DoCmd.SetWarnings False
  8. DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin")
  9. DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)")
  10.  
  11. Set db = CurrentDb()
  12. Set rs = db.OpenRecordset("tblAbsenteeAssign")
  13.  
  14. With rs
  15.     .Index = "EmployeeID"
  16.     .Seek ">=", empID
  17.     If .NoMatch Then
  18.         MsgBox ("The requested employee is not on shift today.")
  19.     Else
  20.         Me.Text1 = empID
  21.     End If
  22. End With
  23.  
  24. rs.Close
  25. db.TableDefs.Delete "tblAbsenteeAssign"
  26. DoCmd.SetWarnings True
  27. End Sub
  28.  
Regards,
Scott
Feb 15 '08 #11

100+
P: 147
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_AfterUpdate()
  2.  
  3. Dim db As Database, rs As Recordset, idx As Index
  4. Dim empID As Integer
  5.  
  6. empID = Me.Text0
  7. DoCmd.SetWarnings False
  8. DoCmd.RunSQL ("SELECT EmployeeID INTO tblAbsenteeAssign FROM tblAdmin")
  9. DoCmd.RunSQL ("Create Index EmployeeID ON tblAbsenteeAssign(EmployeeID)")
  10.  
  11. Set db = CurrentDb()
  12. Set rs = db.OpenRecordset("tblAbsenteeAssign")
  13.  
  14. With rs
  15.     .Index = "EmployeeID"
  16.     .Seek ">=", empID
  17.     If .NoMatch Then
  18.         MsgBox ("The requested employee is not on shift today.")
  19.     Else
  20.         Me.Text1 = empID
  21.     End If
  22. End With
  23.  
  24. rs.Close
  25. db.TableDefs.Delete "tblAbsenteeAssign"
  26. DoCmd.SetWarnings True
  27. End Sub
  28.  
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
Feb 15 '08 #12

Scott Price
Expert 100+
P: 1,384
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
Feb 15 '08 #13

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_AfterUpdate()
  2.  
  3. 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.
  4. Dim empID As Integer 'variable to hold the employee ID number you are testing
  5.  
  6. empID = Me.Text0 ' sets the variable to reflect the number you enter in a text box in this case
  7. DoCmd.SetWarnings False ' turns off the annoying messages that come from the following sql action queries
  8. 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
  9. 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
  10.  
  11. Set db = CurrentDb()
  12. Set rs = db.OpenRecordset("tblAbsenteeAssign") ' opens the new table as your recordset to search
  13.  
  14. With rs
  15.     .Index = "EmployeeID" ' the seek method requires you to set the index
  16.     .Seek ">=", empID ' seek the employee ID you are testing
  17.     If .NoMatch Then ' if the employee ID is not found, that means they aren't working, so show the message box
  18.         MsgBox ("The requested employee is not on shift today.")
  19.     Else
  20.         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
  21.     End If
  22. End With
  23.  
  24. rs.Close ' release memory by closing the recordset.
  25. db.TableDefs.Delete "tblAbsenteeAssign" ' delete the temporary table you have created.
  26. DoCmd.SetWarnings True ' turn the annoying messages back on 
  27. End Sub
Feb 15 '08 #14

100+
P: 147
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_AfterUpdate()
  2.  
  3. 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.
  4. Dim empID As Integer 'variable to hold the employee ID number you are testing
  5.  
  6. empID = Me.Text0 ' sets the variable to reflect the number you enter in a text box in this case
  7. DoCmd.SetWarnings False ' turns off the annoying messages that come from the following sql action queries
  8. 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
  9. 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
  10.  
  11. Set db = CurrentDb()
  12. Set rs = db.OpenRecordset("tblAbsenteeAssign") ' opens the new table as your recordset to search
  13.  
  14. With rs
  15.     .Index = "EmployeeID" ' the seek method requires you to set the index
  16.     .Seek ">=", empID ' seek the employee ID you are testing
  17.     If .NoMatch Then ' if the employee ID is not found, that means they aren't working, so show the message box
  18.         MsgBox ("The requested employee is not on shift today.")
  19.     Else
  20.         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
  21.     End If
  22. End With
  23.  
  24. rs.Close ' release memory by closing the recordset.
  25. db.TableDefs.Delete "tblAbsenteeAssign" ' delete the temporary table you have created.
  26. DoCmd.SetWarnings True ' turn the annoying messages back on 
  27. End Sub
Thank you very much This works great.
Dan
Feb 22 '08 #15

Scott Price
Expert 100+
P: 1,384
Glad it works for you, Dan, and thanks for posting back!

Regards,
Scott
Feb 22 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.