Hi there - I started off with a

[spherey]

Hi there -

I started off with a question in the Forms forum, but after another member's suggestion, I realize that this may be more of a query issue (and if I can do this with a query, great - that would probably be easier).

The original issue is in thread 702-593338, if anyone wants to see it, but my question is basically this:

I have a form which adds data to its own underlying table, and also to another table at the users' discretion, by means of a command button which kicks off an append query. However, the append query cannot handle updating a record which has already been appended to the secondary table.
Can an update query also handle appending new records to the table (that is, can I simply change the existing append query, which is already in place, into an update query instead, and have it 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)?

Any advice you can offer is appreciated.
Thanks,

Spherey

 

[GoDawgs]

You're going to need a query (or SQL in code) for both the update and append portion. You don't necessarily need another button on the form though, the button that you use to run the append query can include the code to run the update query as well. Hope that helps.

Kevin

 

[spherey]

It does help, thanks. Is there anything I need to know before I started coding about making sure that the queries don't conflict with one another? For instance, the reason that the update query won't allow appends is that it's treating them as duplicates, and filtering them out. If a field is updated, what allows the updated record to be recognized as an update instead of a duplicate?

Thanks for your help,

Spherey

 

[GoDawgs]

Whatever it is that is keeping the append query from appending those records is what you need to use in the update query to make sure that you're updating the proper records. Most likely this will be your primary key of that table, but it could also be any field that is set to not allow duplicates ("Allow duplicates?" in the field's properties). I would create a new query and link the two tables by this field(s), then the update will only update where the two are equal. I wouldn't do much coding in this case, just set up another query and then have the button run the update query and then the append query (running the append query first will just add more time because when you run the update query after the append it will also "update" all the appended rows...basically just doing double work). Hope that makes sense.

Kevin

 

[spherey]

Awesome. I really appreciate you taking the time to talk me through this; and I'm relieved to see that this all sounds pretty simple, now!

Much thanks,

Spherey

 

[spherey]

Kevin -

One more question, if you're up for it.

I'm in the process of setting this query up now - I set up a new query with the two tables, linked by the primary key and specifying that the query only includes records which are the same in each table. But what do I put in the "Update To:" portion of the query's fields? If I'm updating Field1 in Table1 to the value of the record which was just updated in the form, I thought it would look something like this:

[Forms]![Form I'm Using to Update]![Field in the Form]

but that doesn't work; what am I missing?

Thanks again,

Spherey

 

[spherey]

Kevin -


Never mind - the above was a typo. Easy fix.

Now the query works, and with the above coding, but there's one hole in it - if the update is an update to the primary field, it doesn't record the update; instead it appends a new record, so there ends up being a record with the old primary key info and a new, appended record with the new primary key info, instead of simply updating the existing record. Is there a fix for this?

Thanks,

Spherey

 

[GoDawgs]

I'm kinda confused with that question...if you have the primary key as what you are determing what record to update, then linking with a "changed" primary key will make the query think that it's a new record since it has nothing to link the two tables on (ie the pk is 150, but the record in the other table is 175-what you want to change it to-then it won't work).

I'm not really sure of your situation, but the quickest solution I came up with would be to have a field called "old pk" or something in the table where you make the first change...this field would be the same as the primary key to begin with, but if you were to change the primary key then that other field would give you something to link to the other table. Make sense? I don't know if that will apply in your situation, but that's the first thought that popped into my head...hope it helps.

Kevin

 

[spherey]

Thanks. That's a good idea.
Admittedly, having the primary key change is not going to be a situation which happens very often. I'm just trying to cover all the bases, since I don't know what these users are going to come up with. But, since this database is still in the process of being set up, and none of the users are using it yet, it might be easiest of all to simply make it a caveat that although the forms can add data to more than one table, and update data that has already been added, that if there's some compelling reason to make a change to the primary key, they're going to have to do it from the main form or table.

Thanks!

Spherey