Append Only New Records To A Table

[maviko]

I want to append records that I have created in Table1, via a form (Form1), to Table2, using Form1's OnClose event. (Table2 will be amended later, but I need to preserve Table1). Is there a way to append only the records from Table1 that haven't been previously appended to Table2? Also, can I turn off the warning messages in an append query? TIA

 

[sportsguy]

there is nearly always a way. . .

first the Table1 must have a way of identifying the appended versus non appended records, so a simple way is to have a table1 field called appended and the field type is yes/no, default is no.

Form Onclose

With DoCmd
      .SetWarnings False
      .RunSQL "INSERT INTO TABLE2 (field1, field2 ) SELECT field 1, field2 . . . . . FROM TABLE1 WHERE TABLE1.APPENDED = No"   
      .RunSQL "UPDATE Table1 SET TABLE1.APPENDED = Yes"
      .SetWarnings True
End With

sportsguy

 

[maviko]

Shorter Way?

Thanks. I see what you mean with the "Appended" field. My table has several dozen fields which need to be appended. Is their a shorter way to append using this method, without having to type in all the fields? Can I set the warnings to false, initiate the append query, then set the Append field to Yes? I am unsure of how to run a query without a macro or the Run (!) button.

 

[ScottGem]

Why are you doing this? its not good design to have duplicate tables. if you need to preserve a history of changes there are a few Audit trail examples you can use.

 

[maviko]

No duplicate tables

They are not duplicate tables. The first is an initial inventory. The second one will build off the first, change info from the first, and add info to other fields. I must preserve the first so that I can print it at a later date for auditing, if need be, and, so that others can see what the initial inventory was. The tables have separate names. I guess basically, if I turn off the warnings, and then initiate the append query, that would be fine, since my table will not accept duplicates for the primary key. If I can just do that, everything will be OK. The main reason I want the warnings off is that others may become confused by the messages.

 

[neileg]

*Bad Design Alert*

You sound as if you are going to be holding balances in table 2. Not the right way at all. You need to hold a series of transactions, additions and withdrawals to inventory and sum these to get your current balance. The initial inventory would be the first of these transactions, and you don't need to preserve these in any special way, because you won't be ammending the records, just adding new ones.

Do a search in here for inventory and stock. There's lots of advice. Inventory management is non-trivial and you might wish you had never started!

 

[ScottGem]

They are not duplicate tables. The first is an initial inventory. The second one will build off the first, change info from the first, and add info to other fields. I must preserve the first so that I can print it at a later date for auditing,

As Neil says, you may be approaching inventory incorrectly. In a properly constructed Inventory application stock counts are CALCULATED not stored. Such an app centers around a transaction table that records ALL movement of stock. So your initial counts would be entered into this table and identified as the initial count. Then you add transactions that record incoming and outgoing stock. Stock on Hand is then calculated by adding all the incoming amounts and subtracting the outgoing.

This is why I asked why you were doing it. Whenever I see someone moving and especially copying records from one table to another it raises a flag. While there are situations where that might be a valid technique, they are the exception. So any situation that involves such movement of records should be scrutinized.

 

[maviko]

My Dilemma

The main reason I am approaching it this way, is I am printing the totals onto a pre-printed form (which I wish I could change, but which would require much bureaucratic effort!) I'm basically forced to use this accounting method, as the form is an official record. I must print an inventory for each individual upon the initial inventory of items at our site. From then on, I may have to add an amount to items currently on the inventory, or add an amount to other items on the form that the individual previously didn't have. When a person's term is completed, he/she is then given a departure inventory. Therefore, I need to preserve the initial inventory, as there may be grievances by individuals concerning when they acquired these items.
The entire project is very workable, and nearly complete. In fact, I have no trouble with the append query. I just felt that turning the warnings off would require less input from other users, and reduce errors if the wrong "Yes" or "No" was selected. I checked the Access "help" and could not find a way to turn the warnings off, then run the append, using the OnClose method. I certainly have a lot to learn about Access, especially when SQL is involved!

 

[ScottGem]

Nothing you said materially changes what I said. You still should have a transaction record that records initial inventory, then changes over time. That satisfies your requirements.

 

[neileg]

Clearly Scott and I are on the same wavelength (hardly surprising since we are both advocating the 'right way').

You seem to think that you have to base your report on a table, so the data has to exist in the table in the way you want to print. That's not the case, you should be using a query.

If you are wedded to your design, you can handle the warnings in VBA code using SetWarnings.