Copy fields in Form to Other Records in Table (1 Viewer)

[kvar]

I will try to put this as succinctly as possible, but bear with me!

I have a table containing records (duh), and a Form that is used to display those records.
More than one record can, and usually does, have the same contract number (separate records for each line in the order).

What I'm attempting to accomplish is this:
When a user updates a records field in a form, i.e.-email address, comments, etc. Then I would like for the field that they updated (let's say it was comments) to copy to the rest of the records in the table WHERE the contract number and ProductID is the same.

It seems like that should be simple with VBA, but I'm having trouble figuring out where to begin!

There are only certain fields on the form that can be updated, most of them are locked, so I think it could be an On Change event for each field that "Can" be updated.
But I'm leaning more towards an maybe an After Update event on the Form itself.
Reason being, I want a msg box to ask the user if the want to apply the changes to all similar records.
This would be annoying if it popped up on you for every field, better to pop up once, like when you are trying to close the form, or just navigate to the next record.

If anyone has any ideas or suggestions I would REALLY appreciate it!
I can usually roll pretty well on my own as long as I can find a place to start or an example to grow off of.....in this case, I can't find anything. If I wanted it to copy to ALL records, I'd be set, but I don't!

Thanks in advance for any help or guidance!

 

[wiklendt]

sounds like you need to normalise your tables. that is to say, that there should be a separate table for clients, where you have their contact details there. a separate table for contracts, with each contract detail there, and a third table (called a junction table) to combine the two (i.e., the ClientID + ContractID + JunctionID).

then, set up your forms to have a main form and a sub form (a subform is basically a normal form inside another normal form). the main for can be your client, say. then you'd have the contracts as a subform. or the other way around, depending on whether your database is client-based or contract-based.

this means that no matter how many contracts a client has, if the client email changes, you only need to change one record - the client record, NOT every contract record that that client has.

hope that helps.

 

[kvar]

That doesn't really work in this instance.
There are millions of schools, school districts, and Home school parents all over the world.
Just because say, Jefferson Elementary places an order, that doesn't mean that contact info will ever be good for anything again. Next time it may be a different teacher, or it may be ordered at the District level.....leaving all new contact info. Or, sometimes, one school may have 30 different orders all with different contact info - Maybe by grade level or subject or Both.

Thanks for the advice, but I don't think that's going to work for this.
I need to keep them together by Contract number and that's it.

Thanks though! I'll keep looking...

 

[Scooterbug]

Actually, it would work in this instance. You just have to design your tables correctly.

Ask yourself, "What information can have many different aspects for this order?"

In your example, you state:

There are millions of schools, school districts, and Home school parents all over the world.
Just because say, Jefferson Elementary places an order, that doesn't mean that contact info will ever be good for anything again. Next time it may be a different teacher, or it may be ordered at the District level.....leaving all new contact info. Or, sometimes, one school may have 30 different orders all with different contact info - Maybe by grade level or subject or Both.

This requires several tables:
tblSchool
SchoolID (PK)
Address
Phone
Other info

tblContacts
ContactID (PK)
ContactName
Other information

tblContactSchoolJunction
ContactSchoolJunctionID (PK)
SchoolID
ContactID

Now, by having a separate table for contacts and one for schools, you can use tblContactSchoolJunction to assign a contact to a school. Now, when you go to place the order, you can choose the school, and use that information to bring up a list of contacts associated to that school. You then store the PK of the tblcontactSchoolJunction with the order and you can easily retrieve the school and contact for that order.

As for an order being for a grade or subject, that data should be stored with either the whole order, or with each line item for the order.

Having a normalized database will save you lots of headaches down the road. You are already feeling that effect by storing the same information in several tables.

 

[kvar]

I only have 2 tables, one for the Contracts that get imported in and One that is a list of Employees and ProductID's.
Well, and a TempTable for the import, but there isn't anything in that outside of for a few seconds during import.

Orders come in by Contract Number. My department never goes back to look at them again, therefore no reason to store them past the end of the month that they are filled. This is a department use database, not company wide. We get our orders, we fill them, then we get rid of the records.

The orders come in with the appropriate contact info for that particular order. Nothing more. And since I've been here, I've never seen a duplicate contact person for a school or district. And with that, I could have 35 different phone numbers for one school.
But it doesn't really matter, since all I need to be concerned with is filling the current order.
Think of the Contract number like a PO number. Maybe that would help. There can be many lines on one order. The products and quantities may all be different, but the rest is the same right?
So all I want to do is when I update, I don't know, a comment that says I sent them an email, or I check the box on the form that says the order is complete.......is for the rest of the records in the same table with the same contract number to update to reflect the same comment, or same completion date.

I really don't have any need for more than my current two tables. Before building this we were just completing all the orders straight from the Excel spreadsheet that the reports come to us in. That worked okay, but someone had to sit there and color code each line of the spreadsheet based on which one of us was going to complete that order.....hours a day, literally.
This way, access searches for some key words (among other things) and all we have to do is click our name and we can see all of our work. And, it provides a place to put comments or notes much better than Excel.

I guess it's not that big of a deal to just copy and paste the comment into the other records that apply, just seemed to me like it couldn't be more than a couple of lines of code typed in once, to avoid many hundreds of times copying and pasting.
Maybe not. It was worth a shot though.

Thanks.

 

[Scooterbug]

Well, if you only have two tables, and you use the contact number as the field to identify a record, all you have to do is to use that as the where clause. For example:

UPDATE tblTableName SET FieldName = "New Data" WHERE ContactNumber = "Contact Number"