A problem with updating from a form

[spherey]

Hello everyone,

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

 

[SMerrill]

Your query is the culprit. You must use two queries; one to UPDATE if the keys are the same, and the other to APPEND if the keys are different. Use Joins or sub-selects to determine if the keys are the same or not.
A sub-select, if you have a composite key, is

SELECT * FROM OneTable WHERE KEY1 IN (
  SELECT KEY1 FROM AnotherTable WHERE OneTable.KEY2 = AnotherTable.KEY2
)

If you have more than two composite keys, just use an AND within the Inner select statement. If you do not have a composite key, then it is:

SELECT * FROM OneTable WHERE KEY1 IN (
  SELECT KEY1 FROM AnotherTable
)

(But you realize that neither your UPDATE query nor your APPEND query are SELECT queries; I am just demonstrating the sub-select concept.)

 

[spherey]

Thanks! Another forum member suggested an update query, too, and at that, I realized that this was more of a query issue than one for the forms forum, and moved it over there...I think I've got a working solution (the new thread is at 701-596632 ) .
I'm not sure about the joins or sub-selects; I don't think that I'll need them, as I have a primary key - because of the primary key, I can simply have the two queries, right? One to update the record if it already exists in the table, and one to append it if it's new. That was the gist of what I was told over there, anyway. I haven't actually implemented the solution yet as I've been wrangling with one involving automatically populating a field with a username.
Thanks for your help!

Spherey

 

[SMerrill]

There are two usernames to wrangle with; The one from MS Access security, and the one from Windows/networking, (and I guess you could manually create your own username, but why?)

See:

http://www.mvps.org/access/api/api0008.htm

http://www.mvps.org/access/api/api0066.htm

 

[spherey]

Yeah, I've been to that page and copied the code into a macro, but thus far, despite some good advice, it isn't doing what I want it to do. That thread is at 181-596648 .

Thanks!

Spherey