Copy a Record from a Button on a Form

M

molsonexpert

I need to create a button on a form so that when clicked, the current record
displayed is copied and appended to another table in the same database. I've
successfully created an append query, but this appends all the records in
the table to the new table; I need just the record currently opened on the
form.

Thanks in advance.

steve.
 
V

Van T. Dinh

You need to use a parametrised Append Query so that the Append Query will
only select the current Record on the Form for appending. I don't know your
Query but you will need a WHERE clause something like:

INSERT ...

SELECT ...

WHERE [RecordID] = Me.txtRecordID

where the value in the Control txtRecordID uniquely identify the source
Record you want to add to the other Table.

You can also use the "append single record" construct using the keyword
"VALUES". Check Access Help on the SQL syntax "INSERT...VALUES...).

Beware that storing the same set of data in 2 different Tables may not be
the correct structure in the end as you don't want to store duplicated data
in your database which can lead to inconsistencies later.
 
M

molsonexpert

Van T. Dinh said:
You need to use a parametrised Append Query so that the Append Query will
only select the current Record on the Form for appending. I don't know your
Query but you will need a WHERE clause something like:

INSERT ...

SELECT ...

WHERE [RecordID] = Me.txtRecordID

where the value in the Control txtRecordID uniquely identify the source
Record you want to add to the other Table.

You can also use the "append single record" construct using the keyword
"VALUES". Check Access Help on the SQL syntax "INSERT...VALUES...).

Beware that storing the same set of data in 2 different Tables may not be
the correct structure in the end as you don't want to store duplicated data
in your database which can lead to inconsistencies later.
Click to expand...
Thanks for the input. Let me summarize where I'm at (and my apologies: I'm
not a programmer by any stretch, and I certainly don't think like one): I
have a small database, and a form on which users will only change two fields
for existing records (they're both date fields). However, I need those dates
retained for future reference/archival history, so once they enter new
dates, the old ones can't be wiped out. The only other option I could think
of was to have a new record created for data entry (saving any old records),
but the new record would have to have all the fields populated from the old
record it's based on.

Once again, thanks for any and all input.

steve.
 
V

Van T. Dinh

I am not sure of your set-up but it sounds like you need to set up a
One-to-Many relationship and have the Child / Many Records storing the
historical dates.

If you are not familiar with Database Design Theory and Normalization
technique, you might find them useful in getting to the correct structure
for your database.
 
Top