copy data from form to table

[travismp]

tbl_TEMP
frm_TEMP
tbl_FINAL

My user will open the database & use frm_TEMP. They will see a record using the form. The data will be in tbl_TEMP at first. My user will check all data, make any changes, then I want them to click a button on the form that will move the corrected record from tbl_TEMP to tbl_FINAL. It will then refresh the form and bring up the next record.

How do I do this? Thanks.

Travis

 

[Dreamweaver]

tbl_TEMP
frm_TEMP
tbl_FINAL

My user will open the database & use frm_TEMP. They will see a record using the form. The data will be in tbl_TEMP at first. My user will check all data, make any changes, then I want them to click a button on the form that will move the corrected record from tbl_TEMP to tbl_FINAL. It will then refresh the form and bring up the next record.

How do I do this? Thanks.

Travis

I'm just off to work but when I get home if you havn't had an aswer I'll sort out an answer But you'll need to use a recordset or query to move the data from form to table and a query for the Forms Data with an extra field so you can mark the record Done.

Sorry no more time Later.

Mick

 

[travismp]

thank you Mick. I will look forward to help later on then. Thanks.

 

[gemma-the-husky]

is frmtemp bound to tbltemp, or unbound

if you are using tbltemp, after you insert the record into tblfinal, what do you do with the record(s) in tbltemp - do you delete them and start over.

---------------------
hacing said that, the easiest way to load the new record into final table is to use an append query (design in the visual grid) - this way you don't need to use recordsets or much code.

drag tmptable into the design grid, and make the query an append query, appenig to tblfinal .

now you can set each field in tblfinal according to a value in tbltmp.

---------------------------
now in code all you need do is

docmd.openquery "mynewquery", to run the append query, fired by the addbutton click

this will try to add every record in tmptable, into final table - HENCE the intial question about clearing the record

 

[travismp]

tbl_TEMP is pulling the info directltly into the form. I want to hit a button that says "ACCEPT" that will then copy the data from tbl_TEMP to tbl_FINAL. I would like it to delete the record from tbl_TEMP.

 

[gemma-the-husky]

as i say, just have a couple of queries to do what you want

1. an append query
2. a delete query

then in the buttonclick event

docmd.openquery "addqueryname"
docmd.openquery "deletequeryname"

 

[travismp]

How does it know which exact record to append and delete then? tbl_TEMP may have 100 records. The form will show number 32. How will it know I want to copy #32 and delete it? I have a unique number for each test, but how will it know to only copy & delete the current record? Sorry I am slow.

 

[gemma-the-husky]

right, i thought you were using an empty temporary table for input purposes.

if you want to copy and delete only number 32, then in the append and delete queries you need to include the appropriate "recordid" field, and in the criteria row set a reference to the text box on your form.

 

[travismp]

OK I have this so far:

Private Sub cmd_ACCEPT_Click()
On Error GoTo Err_cmd_ACCEPT_Click

DoCmd.OpenQuery "addqueryname"
DoCmd.OpenQuery "deletequeryname"

Exit_cmd_ACCEPT_Click:
Exit Sub

Err_cmd_ACCEPT_Click:
MsgBox Err.Description
Resume Exit_cmd_ACCEPT_Click

End Sub



I have a unique field of "SpecimenID" It will be all numbers in the field almost like a social. Can I just make a change to the VB.

 

[gemma-the-husky]

have an append query that adds the record to the file. change the name in my code from "addqueryname" to this nam

ensure that the specimenid column is a column in the query.

in the criteria but =forms!myupdateformname!specimenid

try opening the query - you should just see one row, where the
recordid matches the specimenid - if it helps use a select query, then change it to an append query.

after you get this sorted, clone the query into a delete query, as the delete query is virtually the same.

 

[Dreamweaver]

Thanks gemma-the-husky That should do the job nicely LOL.

travismp Sorry to be nosy but what is the purpose of this i.e. was then an error that you feel can only corrected by this method and how many records are you looking at doing if it's a data entry error your trying to correct then it may be posable to automate the process sorry don't mean to confuse you there I'm just wondering

Mick

 

[travismp]

Mick thanks for the help. I do not mind you being nosy at all... I am asking for help.


Here is my senerio. I have 2 different Databases. Both Access 2000.

Database_A - has tbl_TEMP with 200 records.
Database_B - has tbl_FINAL

Database_B is the one my users use all of the time.

The tbl_TEMP from Database_A is a linked table into Database_B. I want my reps to open a form to see the records from tbl_TEMP, make changes, then click "ACCEPT" to accept that test into tbl_FINAL.

The info coming from Database_A is not 100% correct ever so I want my guys to be able to review the record, make the changes, then accept it into our system.

Does this help? Thanks.

 

[Dreamweaver]

Mick thanks for the help. I do not mind you being nosy at all... I am asking for help.


Here is my senerio. I have 2 different Databases. Both Access 2000.

Database_A - has tbl_TEMP with 200 records.
Database_B - has tbl_FINAL

Database_B is the one my users use all of the time.

The tbl_TEMP from Database_A is a linked table into Database_B. I want my reps to open a form to see the records from tbl_TEMP, make changes, then click "ACCEPT" to accept that test into tbl_FINAL.

The info coming from Database_A is not 100% correct ever so I want my guys to be able to review the record, make the changes, then accept it into our system.

Does this help? Thanks.

Thanks Just thought it might have been an error that we could have helped speed up correcting LOL