Button with Code to add Multiple Records?

[galvinjaf]

Good Morning,

I have a sub form that hold pay period ending data for my employees. I also have a button on this form that creates a new record for the employee where I go in and add the new pay period ending date....

Is there a way to be able to make this button add that SAME pay period ending date but for all of my employees, rather than having to add the pay period ending date to everyone every-time?

Attached is what my sub form and button look like...Any ideas?

I did attempt to create an update query, but I can't seem to get the query to 'Add a new record with date X' to each record....Instead, it changed the pay period ending date to the be the same date all across the board....

 

[plog]

I think your failed attempt used an UPDATE query not an INSERT query. UPDATE changes existing records, INSERT adds new records.

You didn't provide specific table names, so I will have to tell you how to do this in general. You want to add a new record with a specific date to your PayPeriod table. To do this you need an Employees table that lists every employee you want to add data for. You would build a query like so:

SELECT EmployeeID, "4/1/2016" AS PayPeriodStart FROM Employees;

Run that and it will show you what data will go into PayPeriods. After its correct, you go to the ribbon, click on the Append item and then select 'PayPeriods' as the talbe to append to. Back in the query there will be a new row under each field labeled 'Append To:', for each field you then select which field in PayPeriods that field's data needs to go into.

Last and most important, do this first in a copy of your database to make sure you understand what's hapening and get it right. Then you can implement it in your actual database.

 

[galvinjaf]

What you said makes total sense after I went in and tinkered with which tables I play with and adjust. I now have the right set up (thanks to your advice) and I know I'm in the right direction, but when it goes to append the 42 records with the new additional pay period date..I get the following error? Any ideas?

 

[plog]

Sounds like you are either putting a value into a field not designed to hold that value (text into a Date, Text into a number) or you might have a required field on that table which isn't getting a value.

 

[jdraw]

Looks like Key violations.
Do you have a field or fields in your table with the No Duplicates allowed set?

 

[galvinjaf]

I went through my employee table, and have no required fields. I then went through my subform table (tblAIB) which is the table I'm appending to and that has a foreign key field that is required (I can't remember why...) and attached is a copy of what that looks like. Could that be my issue? What would I do?

 

[galvinjaf]

The only item in my table that doesn't allow duplicates is the ID (AutoNumber)

 

[jdraw]

But you don't enter a value for the ID field, that gets taken care of by Access.

Can you post the SQL for the Append Query you ran?

 

[plog]

Can you post a copy of your database? You can strip out personal information. If that's too hard, you could even delete all the data--just not the tables.

 

[galvinjaf]

Here is a copy..I had to delete quite a few tables/forms etc to get it to upload.

The form is under frmEmployee (the form where my subform for tblAIB is)

tblAIB is the table which I want to run the update query on... right now, I have a button to add a new record (pay period ending date) to the subform...

Let me know what you think/find...

 

[plog]

You are appending tblEmployee.ID to the wrong field in tblAIB. It should go into tblAIB.EmployeeID. This is why you shouldn't name every ID field 'ID', prefix it with the table name.

 

[galvinjaf]

Below is the sql code:

INSERT INTO tblAIB ( ID, Period_Ending )
SELECT tblEmployee.ID, "5/2/2016" AS Period_Ending
FROM tblEmployee;

What should change? I'm confused.

 

[plog]

INSERT INTO tblAIB ( ID, Period_Ending )

Should be

INSERT INTO tblAIB ( EmployeeID, Period_Ending )

 

[galvinjaf]

It worked! Perfect. I do want to understand, but why EmployeeID? Because it's EmployeeID on my tablAIB that I'm trying to match?

Edit: Also, is there a button I could create that I could press that would prompt me for the date I'd like to append?

 

[plog]

1. If you renamed all your ID fields with a prefix relating to the table this would be more apparent. Your two fields named ID in tblAIB and tblEmployee have nothing to do with each other. The ID value in tblEmployee is used as a foreign key (https://en.wikipedia.org/wiki/Foreign_key) in tblAIB. Specifically, it relates to the EmployeeID in tblAIB. So when you want to put data into tblAIB and reference an Employee, you need to put that Employee's ID number into the EmployeeID field of tblAIB. That's what the changes to your SQL do.

2. Yes you can add that button. First, I would change your query to this:

INSERT INTO tblAIB ( ID, Period_Ending )
SELECT tblEmployee.ID, [Enter Period Ending] AS Period_Ending
FROM tblEmployee;

That will make the query prompt you for the Period_Ending value. Next, you can use the form wizard to add a button to your form and have it open that query when you click the button.

 

[galvinjaf]

Thank you for your time and explanations. Again, learn something new everyday.