How do I force a form to save a record as new instead of updating

[Guest]

The issue I’m having is that I’d like to keep a history of the data fields in
one of my tables.

I have two tables set up as tblinfo and tblhistory. The primary key in
tblinfo is a facility ID which is not duplicated. I have a dual primary key
in the second table with facility ID and date (both individually will be
duplicated but not together in the same record) (I also tried auto number and
a few other combinations to get this to work also). I have a form set up to
query both tables (linked by facility ID) to bring up past information for
editing. What I need is that if the field - “date†(found in tblhistory)
changes I want to be able to force the second table (tblhistory) to save the
information as a new record instead of overwriting what is already there.

The data contained in the first table (tblinfo) will change on occasion but
will not need to be tracked. The data contained in the second table
(tblhistory) I do need to track. Both tables contain a large number of
fields that is why it is imperative that when I open the records the most
recent information form the past opens (instead of having to input all the
fields each time).

Is there a way with afterupdate – for the date field to force a new record
only in tblhistory if the date changes?
Do I need to link the tables in the query different?
Do the primary key(s) in tblhistory need to be different?
Do I need to go about this a whole new way?


I’ve been trying to figure this out for awhile any help would be
appreciated. Thanks.

 

[margaret bartley]

The issue I'm having is that I'd like to keep a history of the data fields in
one of my tables.

I have two tables set up as tblinfo and tblhistory. The primary key in
tblinfo is a facility ID which is not duplicated. I have a dual primary key
in the second table with facility ID and date (both individually will be
duplicated but not together in the same record) (I also tried auto number and
a few other combinations to get this to work also). I have a form set up to
query both tables (linked by facility ID) to bring up past information for
editing. What I need is that if the field - "date" (found in tblhistory)
changes I want to be able to force the second table (tblhistory) to save the
information as a new record instead of overwriting what is already there.

The data contained in the first table (tblinfo) will change on occasion but
will not need to be tracked. The data contained in the second table
(tblhistory) I do need to track. Both tables contain a large number of
fields that is why it is imperative that when I open the records the most
recent information form the past opens (instead of having to input all the
fields each time).

Is there a way with afterupdate - for the date field to force a new record
only in tblhistory if the date changes?
Do I need to link the tables in the query different?
Do the primary key(s) in tblhistory need to be different?
Do I need to go about this a whole new way?

Use an unbound form, load the data from your tblHistory record, and then,
on the Save button, create a new record and append it to tblHistory.

BTW, I've always found it better to use Autonumber to generate a unique
table Key, and then indices on both the FacilityID and the Date fields, as
separate entities. It makes everything much much simpler.

 

[Guest]

I routinely update a date field in my database using a form for data entry
and have wanted to have a way to automatically save the prior information
(prior date) but have never found a way to do this..your concept seems the
same...but your answer was pretty far above my head. Could you explain
further?

 

[margaret bartley]

I routinely update a date field in my database using a form for data entry
and have wanted to have a way to automatically save the prior information
(prior date) but have never found a way to do this..your concept seems the
same...but your answer was pretty far above my head. Could you explain
further?

You need to do this with VBA. If you are not experienced with VBA, you
need to learn it.

If you are familiar with VBA, then please be more specific about where your
confusion lies.

 

[Guest]

Thank you Margaret..I guess I need to learn VBA...I will battle it...any
other info let me know....challenge is always good..appreciate your time.