I have an Employee Table that houses the employee name, shift, availability, hours scheduled etc. I want to add 2 additional items to that form. One being a combo box that lets the user select a particular job their employee worked and the other a command button that when clicked will take the employee, shift, job selected from the combo box and enter that information onto a new table called TblJobWorked and enter the date into a Date Worked field.
I need it to create a new record every time that button is selected so the user can pull a report that shows employee a worked job b on this date to create a historical reference.
If you know of an easier way to accomplish this please let me know. The Employee Table is built with approximately 46 employees so I thought it would be better to have the select information I need for the report pulled to a different table.
Please help as I have inherited this DB to enhance and I am still in the learning stages.
It sounds reasonable to me. Are you having a problem doing it? Simply create the combo box using the wizard. It should look up a value in a table (jobs) and retain the value. When you click the command button, have VBA code to add the various fields into the work table.
It certainly makes sense to isolate the work table and jobs table from the employee table. It's called normalization of data, and is to be encouraged.
Don't be afraid to use the Help file for explanation of any of the terms or steps I mentioned. If you are new to Access, and plan to use it for a while, it's better to familiarize yourself with these basic concepts earlier rather than later.
I am getting familiar with some of the basic aspects of Access - I have been going thru the Help section of access and have been reviewing a couple of books, forums & web tutorials. I am however, having difficulty in coding the VBA. I am not well versed in that yet
I have the combo box set to select the job that the employee worked. The supervisor will go into the employee - add their hours worked, select the job
worked and then I would like them to hit the command button to pull that information and put it into the new table.
How do I code the VBA to have the command box do the following:
copy Employee from Employee Information Table to Employee field in TblJobWorked
copy Shift from Employee Information Table to Shift field in TblJobWorked
take the job selection from the combo box in Employee Information and put that information in the Job Worked field on TblJobWorked
add a date stamp to Date Worked field on TblJobWorked (there is no date field on Employee Information table)
Thanks again for taking the time to view and answer my questions.
I added a zip file of the DB.
Last edited by paankadu; 06-29-11 at 18:05.
Reason: password for file is coop
How about if I give you a prose-y answer, and give you the satisfaction of sweating out the actual code.
When the command button is clicked, you want VBA to open the TblJobWorked table as a table-type recordset. You want to add the values of the various variables, including the combo box value, into the table fields and save the record. You then want to close the table.
You will want to use the following objects/methods/properties (I hope you know now the definitions of all three terms): Set, dbOpenTable, DoCmd, .Recordset, .Addnew, .Update, = (variable assignments), .Value
You may use some more as well, but these are the main ones that come to mind.
Tell you what. If the code doesn't work the way you expect, post it here and we'll critique it.
P.S. I didn't view the upload. I have a poor track record with db's uploaded to the forum. For some reason they don't unzip properly on my computer.
I appreciate the advice you have given me thus far. I have been browsing thru different sites and books and have attempted some code. I am not well versed in putting the pieces together yet, but am learning different things - just not what I am looking for for this project.
Here is some attempted code
Private Sub Command223_Click()
On Error GoTo Err_Command223_Click
Dim db As Database
Dim RS As Recordset
DoCmd.GoToRecord , , acNewRec
Set db = CurrentDb
Set RS = db.OpenRecordset("TblJobWorked", dbOpenTable)
RS!Employee = "Employee"
RS!Shift = "Shift"
If I set the requirment for Job Worked to no it pastes the words Employee and Shift into the appropriate fields. It doesn't carry the actual name and shift of the employee over. I have tried various things to get the names to pull over but it keeps going to record 29 which is a blank new record for table 1. When I hit the add record button it advances the form (table 1) one record which blanks everything out and then says the field TblJobWorked.Job_Worked cannot contain a Null value because required property for the field is set to true.
How do I get it to pull the name, shift and job worked (selected from the combo box) over and add a date to the new table?
I wouldn't let a computer-generated date stamp rule the roost. What happens if the supervisor is on vacation and the entry is made a day or two later? I would allow for seperate date entry, a la
Me.WorkDate = InputBox("Please Enter the Work Date", ,Date())
This provides the current date as the default, the correct date format, and allows the user to change the date as well.
This is only an added suggestion. I said we'd critique. To start, you seem to get the concepts pretty well. You only have to tweak your methodology.
For example, the "Shift" and "Employee" controls are not strings; they are controls, and as such, they should be used with the Me object, which denotes a control on a "Me", which in this case is the form, as in
RS!Shift = Me.Shift.
You object naming is also not clear, such as Command223. That's only the name Access generates for the 223rd object to be added to the form, which happens to be a command button. It's up to you to change the name in the Property Sheet to something you'll understand later, such as "cmdAddRecord".
I have yet to figure out why Access provides for the DoCmd.GoToRecord,,acNewRec command. Since the .AddNew method is mandatory, the other is completely redundant. I have never used it, and I never had a problem. As you saw, it causes incomprehensible error messages. My advice: don't use it. In fact, I never use any form of the GoTo method of the DoCmd object. If I need a particular record, I locate the record through logic, and glean whatever information I need from the record.
Let me give you some sample code, and see if it will give you a better start on your needs. To begin with, let's say your combobox is called cmbJobs. Let's begin.
Private Sub cmdAddRecord_Click()
Dim RS as DAO.Recordset, WorkDate As Date
On Error GoTo Err_cmdAddRecord_Click
Me.WorkDate = InputBox("Please Enter the Work Date", ,Date())
Set RS = CurrentDb.OpenRecordset("TblJobWorked", dbOpenTable)
!Employee = Me.Employee
!WorkDate = Me.WorkDate 'I took the liberty of adding to the structure
!Shift = Me.Shift ' of the table
!JobWorked = Me.cmbJobs.Value
If there is anything you don't understand about the code, see the Help file. If you still need help, we'll be there for you.
Last edited by Sam Landy; 07-10-11 at 15:59.
Reason: didn't finish