Problem with using a form to update a table

[spherey]

Hello everyone,

Here's a repost from yesterday; I hadn't heard from anyone, and didn't want the post to get lost. If anyone has any insights, please let me know.

I have a database with three forms which correspond to three tables. However, two of these forms need to add data to another table, as well as their 'main' table, when a user chooses to do so. I've accomplished this by following the suggestions of a few different forum members - it works by using a command button to kick off an append query which adds all records which meet a given criteria - a "yes" value in a check box in a particular field.
But I've run into two problems. The first I was able to correct after another idea from the forum. (The query tried to add ALL checked-off records, even ones which it had already added. It would return an error message, saying, essentially, "only new records have been added, not the duplicates." I used

DoCmd.SetWarnings False

to disable the error message before running the query, then set it to

True

again afterwards.)
So - here's the new problem: sometimes, the users are going to want to use the form to update a given record which already exists in the table. But at this point, if they change a record, the changes are only recorded in the form's main table; they aren't recorded in the other table because the query overlooks them. If the primary field is unchanged, the query apparently treats them as duplicates, and if the primary field is updated, the secondary table only shows updates in that field, but none of the others.
I hoped to get around this simply by enforcing referential integrity and cascading updates - but that won't work, as apparently that's only possible in fields with a unique index, and most of the fields I'm working with won't have one.
Does anyone have an idea of how I can work this out to be able to use the form to update a record that's already been added, and to have the changes show up in both tables instead of only the form's 'main' table?
Thanks for any help anyone can offer.

- Spherey

 

[cghoga]

Try running an update query. It's much like an append query except you specify what record(s) to update, and what you want the field values set to.

In order for it to work you will need to base the query off your unique key field in Table 2. In other words, a field on the form that can uniquely identify the record in Table 2.
In query design mode, within the criteria section of your key field, you will need to specify
Forms!NameofYourForm!Fieldname(note: as it is named on the form).value
In the other fields you have selected for your query, you will need to include in the Update to: section a reference to its corresponding field on the form.

For example, if the field in the table is named ProductName, and the name of the field on the form is prodname, then within the Update to: section of the Product Name field, you would enter
Forms!NameofYourForm!prodname.value so that for that record, Table 2 updates to the value represented on the form.

I hope this helps.

 

[spherey]

It definitely does help - thank you!

Can an update query also handle appending new records to the table (that is, can I simply change the existing append queries to update queries instead and have them thus accomplish both appends and updates), or am I going to have to create a distinct update query to handle updates (along with a distinct command button to kick it off)?

 

[spherey]

Any thoughts re. the last question?

Can an update query also handle appending new records to the table (that is, can I simply change the existing append queries to update queries instead and have them thus accomplish both appends and updates), or am I going to have to create a distinct update query to handle updates (along with a distinct command button to kick it off)?

Thanks for your help,

Spherey