Copy fields from one table to another table and more

[paankadu]

I have an employee database that I need to extract certain information from one table and place it into another table.

Table 1 has the following fields
Employee
Shift
Availability
Telephone
Job Worked (which is a combo box of job choices)
Hours Scheduled
Hours Worked and several other fields

Table 2
Employee
Shift
Job Worked
Date Worked

I want to use an add record button on the form so when selected it will copy the Employee, Shift and the Job Worked selected from the combo box to the appropriate fields on table 2. It will then add a date stamp to the date worked field on table 2.

Each day a supervisor will go into the form to populate hours which will feed into table 1. When they do this I need them to select the job worked and then have that information go into table 2 and date stamp.

I am just learning Access and VBA so made an attempt at coding the button to do this. When I select the button now it advances my form (table 1) one record which blanks out the employee and then tells me it can't accept a null value because it is a required field.

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.AddNew
RS!Employee = Employee.Value
RS!Shift = Shift.Value

RS.Update
RS.Close
Exit_Command223_Click:
Exit Sub

Err_Command223_Click:
MsgBox Err.Description
Resume Exit_Command223_Click

I would appreciate any help that you can provide me with.
Thanks in advance
Angie

 

[Gammachaser]

Your biggest problem is the DoCmd.GotoRecord,, acNewRecord. That clears any bound Controls on the form and makes them ready for input of a new record. That is why Employee.Value and Shift.Value are empty. So, eliminate that code.

Your next problem is that you are not assigning anything to Job Worked or Date Worked... those fields will be empty. You need to add code to fill those fields.

RS.AddNew
RS!Employee = Employee.Value
RS!Shift = Shift.Value
RS![Job Worked] = [i]somejob[/i]
RS![Date Worked] = [i]somedate[/i]
RS.Update
RS.Close

The next thing is: Why does table 1 (I hope that is not really the name of the table) have a field for Shift? That is in table 2. If it is a preferred shift, then name it PreferredShift, not just Shift. Having the same field in two tables is redundant or at least confusing.

You do not need the '.Value' for the fields since that is the default property. It does not hurt to have it, but it does not need to be there.

A couple of other things that are not necessary but will serve you well in the future if you do more programming:

Name your CommandButton something logical like cmdAddRecord(i.e. NOT Command223). It will help when troubleshooting.

Use prefixes to identify variables and Controls like txtJobWorked for a text box, cmdAddRecord for a command button or strMyString for a string variable.

Avoid spaces in your table names and field names. Job Worked should be JobWorked or Job_Worked if you need a visual space to read it. Access does not like spaces. Whithout the spaces you can lose the brackets.

There are many other things you will learn as you go along that will help you get things working (and be able to go back later and remember what it was that you did.)

 

[paankadu]

Thank you so much for your help. The tables are actually named Employee Information and TblJobWorked. I inherited this DB and the previous owner named it Employee Information and I saw where you shouldn't use spaces so named the new one as such. Without having to go in and rework all his macros I just left the name as was.

I made the changes you suggested and I get a compile error: variable not defined on the Job_Worked.Value portion of the RS!Job_Worked = Job_Worked.Value

This is a combo box that gives the user a choice of 7 different jobs to select from. Do I need to do something different for that variable. Also, the Date_Worked needs to just date stamp - I tried using Date() as a value but it doesn't seem to like that either.

Angie

 

[Gammachaser]

Hmmm... the only thing I can come up with (without all of your code) is that something is misspelled -- either the field name was mistyped or the ComboBox name is wrong. Make sure that the Field Name in the table is spelled exactly the way the code says (Job_Worked) and that the ComboBox where the job is picked is also spelled right (Job_Worked). As a amatter of fact, you may see why I like prefixes on controls... it is a bit confusing to have a field called Job_Worked and a Control with exactly the same name. It might be easier to see errors in the code if the ComboBox was cboJob_Worked. You can still see they go together, but you can also tell them apart.

 

[paankadu]

I have attached a link to my zip file. What I have added is the TblJobWorked table, the Job Worked combo list and the Add Record command button. All else was done previously by someone else.

I truly appreciate any assistance you can give me.

The reason there is a shift in both tables is the employee works a specific shift but they may work a different shift on OT so they want to capture the employee the shift worked, the job worked and the date worked on the new table so they can pull a report to see when Yogi Bear worked what jobs on what shifts and what dates.

Thanks again
angie

 

[paankadu]

i forgot the password is coop to the zip file