Copying a record from one table to another using a command button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a database in which I'm trying to allow a person with master access
to go in and approve or reject commitment requests entered by users of a
department.

I have a button on the form that I want the coordinator to click which will
add the current record on the form in the current table named temp to the
table named permanent. I've tried using recordsets however when the button
is clicked it adds the first record in the temp set records to the file
instead of the current record.

Help on this would be appreciated.
 
Use an append query instead of VBA, and put a reference to the current record
in its criteria so that the Where clause of the SQL has something like this
in it:

WHERE CustomerID = [Forms]![Customers]![CustomerID]

This will append a record for only the current customer on the form. You
could also just use the filter above in the criteria portion of your current
code to identify the correct record to append.
 
I have tried using an apped query however it appends all of the records in
the set the new table. How do I append just the current t record?

Carter
 
The key is the criteria portion of the query. Make sure that the primary key
of the record in question is bound to a control on the form from which you
call the query; then, you can refer to this control to filter the query to
the current record.

Let's use an example, with table names Temp & Perm, where the primary key of
the records in both tables is Field1, and the form showing the record from
Temp is called frmTemp.

I am assuming that the record is already saved in the Temp table. I am also
assuming that the RecordSource of frmTemp is something like this:

SELECT Temp.* from Temp

Make sure that frmTemp has a text box called Field1 bound to Field1 (again,
the primary key) from the Temp table. If it does not currently display on the
form (perhaps because it is just a key and the user does not need to see it),
add it as a text box (again, called Field1), set its Control Source as
Field1, and make it invisible.

Then, in your query, make sure Field1 shows up (even if you will not append
this field), and in its criteria, enter this:

[Forms]![frmTemp]![Field1]

This will filter the append query so that it append only that one record
whose Field1 is currently in the Field1 text box on frmTemp.
 
Back
Top