Update query with autonumber field

[Microsoft Access]

I have setup my table so that the invoiceID would autonumber. The issue I am
trying to figure out is what to do when I want to update a row. I figure
there are two options and i was curious what the best would be or if there
is a third I am not thinking of

So the system will initially create an invoice. To update that invoice I
could:
a) do and update query as UPDATE invoice SET Name=..., Cost=...., the
problem with this one is that it is cumbersome. The invoice is created from
a fairly extensive select query and so many select queries would have to be
created for each individual field that needs to be updated since Access
doesn't allow SET (Name, Cost) (SELECT Name1, Cost1...)

b) The option that I can't get to work is to capture the InvoiceID before
deleting it and then recreate the invoice with the select query, this time
inserting the invoiceID that i copied. However it doesn't seem that Access
allows you to insert into an autonumber field.

Any thoughts? Thanks.
Access 2007

 

[KARL DEWEY]

Why not two tables? First with invoice information that never changes in a
one-to-many table for stuff that does change. Use a form/subform and when
you add a record in subform all information is copied to new record so you
can edit as needed and still maintain history.

 

[Microsoft Access]

Thanks for the reply, however I am not sure I understand or I didn't make
myself clear. The invoice table does ahve information that "does not
change", however the situation i am trying to solve is when an invoice is
incorrectly created and needs to be recreated (re-billed). Some examples
are the name of the client changed for some reason and they want me to
reprint the bill with their new name on it or what often occurs for us is
that the market value for the invoice has recently been reconciled and while
most clients are unconcerned about this because the changes are minor, some
prefer that we re-bill them with the reconciled information. These are just
some examples, and as you can tell this is a rare situation, but it does
cause some issues and it would be nice to be able to solve it.

Karl--if I missed your point with the above information please clarify how
your solution helps with this problem

Thanks for all the thoughts.

 

[John W. Vinson]

Thanks for the reply, however I am not sure I understand or I didn't make
myself clear. The invoice table does ahve information that "does not
change", however the situation i am trying to solve is when an invoice is
incorrectly created and needs to be recreated (re-billed). Some examples
are the name of the client changed for some reason and they want me to
reprint the bill with their new name on it or what often occurs for us is
that the market value for the invoice has recently been reconciled and while
most clients are unconcerned about this because the changes are minor, some
prefer that we re-bill them with the reconciled information. These are just
some examples, and as you can tell this is a rare situation, but it does
cause some issues and it would be nice to be able to solve it.

Um?

The customer's name should be stored in the Customers table, not in the
invoice table.

The market value should be stored in a table in a field which can simply be
edited.

Are you perchance creating a table *just for the purpose of printing
invoices*? If so, you're on the wrong track; the printed invoice should be an
Access Report based on a multitable Select query, pulling data from multiple
tables. If you need to change the data in one of those tables, just rerun the
report, and it will pull the current value of the field.

 

[Microsoft Access]

Well i see your point and maybe my schemas need to be revised, but let me
give you my creation thoughts and let me know your thoughts on proper
creation. Each invoice is created for one client. They may have multiple
contacts that receive that invoice, which is in a separate table, but they
all get the invoice. And the market value that the invoice amounts are
billed from is set in stone on a day for that client. So that is why I put
all of that information in one table, viewing the invoice as a set in stone
information store, however maybe I should have made editable data have a
being and end date? Not sure. I do have a table that contains the client
name that can be changed because that is the most recent iteration of that
client name and historical data is not necessary. I am not sure if I have
explained that well, but am open to always improving my database and my
skills.