Coping records that have same value in field

[pbrown2]

Currently when the user enters the form if there is a record they want to copy they go to that record and click the "Copy Record" button. However, what is happening is that they are having to go to several records and click the button. Each of the records have a common value in the [Month] field. Is there any way to have the button say something like:

Look at the field [Month] & [Plant]. Search the table and copy all records' whos [Month] & [Plant] match the [Month] & [Plant] of the open record. And automatically change the month to the next month.

Example if the record is showing [plant] = car & [Month] = march, the button would search the table "Data" and copy all records with [plant] = car & [Month] = march and automatically cange [month] to equal "April"


I know this seems strange but there are many fields (97) in the records and only a few fields will need to be changed month to month. Instead of having every one re-enter all (97) fields for each record (everyone is responsible for between 5 and 7 records) it would be nice to have something to automatically copy last month's records and that would allow the users to go in and update only the couple fields and couple records that need it each month.

I have suggested simply overwritting the existing data, however, a historical record needs to be maintained.

Any ideas????? This seems like it would be rather simple.

Thank you for any and all help,

PBrown

 

[LonnieJohnson]

You could create an append query. Then set it to run from a command button. The criteria of the query can be a reference to the form that the user is working in. The value that gets populated can also be an expression that adds one to the month from in question.

That's one way.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[pbrown2]

The append query is performing the "Copy" function I need however it is copying too much.

Currently in order to have the append query automatically add 1 to the month the [Month] field in the query is:

Month: [Forms]![mprsummary]![Month]+1

However, this causes the query to copy all the months.

I.E.
for month 1 there are 4 records and for month 2 there are 4 records. When the query runs it copies all 8 records and assings them with the month of 3.

I need to have the query only copy the records associated with the Highest "Month Number". Therefore it would copy all records that have a month number of 2 and the 4 new records should have a month number of 3.

Any ideas??

Thank you for any and all help,

PBrown

 

[KenReay]

Hi

Do you have a WHERE clause (ie criteria) on the query, you need WHERE MyMonth = [Forms]![mprsummary]![Month]

also, can I caution you against using Month as a column name, theer is an Access function Month() so month is a reserved word, same applies to Day() and Year(). Using reserved words as column names will end in tears!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pbrown2]

I have gone in and changed the column (field) name from Month to NoMonth throughout the DB.

As for the where clause, I have tried placing:

[Forms]![mprsummary]![Month] in the criteria for what is now:

NoMonth: [Forms]![mprsummary]![NoMonth]+1

However, now when the append query runs it does not append any records.

Also I tried replacing my criteri with the one (Copied: MyMonth = [Forms]![mprsummary]![NoMonth] (changed Month to new field name of NoMonth) suggested by Ken but once again, no records are appended..


Any other suggestions?? It seems soooo close....

Thank you for any and all help,

PBrown

 

[KenReay]

Hi

NoMonth: [Forms]![mprsummary]![NoMonth]+1

putting the criteria on here clearly will not work, think about it, this is the value youi wish to assign to the new rows.

You need the criteria on the month column as it is, I cannot tell from your posts so far what that will be

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pbrown2]

Basically what I need to do is copy the records that have the highest (Max) number in the [NoMonth] field. However, when they are copied, they need to be pasted with the [NoMonth] field increased by 1 so that it is distinguishable that they are for the next month.

I was using a "duplicate record" button on the form, however since there is the potential for one are to have many records for the month I was hoping to have access look for the Max number (highest value) in the NoMonth field and copy all records that have that value. This would allow the user to simply go in and update the numbers for the next month instead of having to reenter all 97 fields. Simply because 9 out of 10 times they only need to update one or two fields from month to month.

Originally I thought of just having the user change the month number for their records, but we would loose historical data that way.

Does this help?

Thank you for any and all help,

PBrown