Copy record(s) from a form into table?

[KevinSlater]

hello, I have a form named: "addnew" (that looks at the query: "query:employees" which in turn looks at the table "employees") the form looks at the fields from the query: firstname, lastname. shiftname, shiftdate, after i have added a new record (or any new records) into the form I would like it to copy all of that records details into a blank table named: "main" which includes all the field names from the other table/query/form. Is this possible?, any help would be appreciated.

 

[ColinEssex]

Why would you want to save the same data twice?

Col

 

[KevinSlater]

Might seem strange but its because one table is more of a history reference wheres the other main table will have those fields from the employees table + others, bit difficult to explain exactly why but think its the only way for what i want to do, would like to know if this is possibe to do or not?.

 

[workmad3]

hmm.. i think i get you... you want an archive of the old data.

What would probably be better, as a db isnt optimised for space, would be to take the records out into a text file(csv or the like) that you can store/compress, whatever.

Only problem is, Im not sure how 2 do that from VBA code

 

[KevinSlater]

yes thats sort of what i mean workmad3, I only want some of the records, such as the new records copied to the other table from the form. I think I
need to insert them one at a time just as they are being inserted in the the
employees table by Useing the forms BeforeInsert event to write a VBA procedure, trouble is I dont know what the vba is

 

[workmad3]

i think what you need is something like this.

Dim SQL as String

SQL = "INSERT INTO <table name>(<fieldnames in order of how you wish to add, comma separated>) VALUES(<values you wish to add, in order of field, comma separated>)
CurrentDb.Execute SQL

to explain the above. If i had a table call foo, with fields foo1 and foo2, along with text boxes txtFoo1 and txtFoo2(foo1 is a text field, foo2 is a number field), i would add the values as follows

SQL = "INSERT INTO foo(foo1, foo2) VALUES('" & txtFoo1 & "', " & txtFoo2 & ")"
CurrentDb.Execute SQL

Hope this helps with setting it up for now. I would look into more efficient archinving methods if i were you though, this will cause you db to become much larger than needed and slow down queries on data that is current.

 

[KevinSlater]

Thanks workmad that helped but I had trouble getting it to work, this is the code i have now (in the before insert event of form) which i believe is close but it displays a runtime error when the user selects a employee number in the form (this is from a look up list) & copies nothing into the table main, any suggestions?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim SQL_Text As String
SQL_Text = "Insert into MAIN (SHIFTNAME,EMPLOYEE_NUMBER) select '" & Me.SHIFTNAME & "','" & Me.EMPLOYEE_NUMBER & "'"
DoCmd.RunSQL (SQL_Text)
End Sub

 

[workmad3]

try this as the sql text

SQL_Text = "Insert into MAIN (SHIFTNAME,EMPLOYEE_NUMBER) VALUES('" & Me.SHIFTNAME & "','" & Me.EMPLOYEE_NUMBER & "')"

 

[ScottGem]

Might seem strange but its because one table is more of a history reference wheres the other main table will have those fields from the employees table + others, bit difficult to explain exactly why but think its the only way for what i want to do, would like to know if this is possibe to do or not?.

What are you trying to reference? Ther are a few ways to do this. You could use an Audit trail. There are a number of examples of such a function (try www.candace-tripp.com for one). An audit trails will record any changes made to all or specified fields. Generally you record the table, the field, the old & new values and who made the change and when.

With an employee database you might also want to maintian a history of certain changes. For example a promotional/salary history. In such a case you would have a tblStatus like so:

StatusID (PK Autonumber)
EmployeeID (FK)
EffectiveDate
CurrentSalary
CurrentPosition

Instead of copying records from one table to another, any changes are entered into the Status table and you you get current info by selecting the record with the most current date.

 

[KevinSlater]

Thanks workmad & ScottGem, that weblink is useful scottgem cheers, giveing me a few ideas, will try a few things

 

[ScottGem]

glad to assist

 

[KevinSlater]

Ok im still trying to get this to work as i would still like the records copied into a new table (main), i have the below code, when i enter information into the form is says: "runtime error 3346, number of query values and destination fields arnt the same" (the query the form looks at looks at 3 tables & the main table is empty with the fields employee_number & shiftname). any ideas what im doing wrong? (ive tried "values" instead of "select" but no luck)


Private Sub Form_BeforeInsert(Cancel As Integer)

Dim SQL_Text As String
SQL_Text = "Insert into main (SURNAME) select '" & Me.SURNAME & "','"
DoCmd.RunSQL (SQL_Text)
End Sub

 

[workmad3]

SQL_Text = "INSERT INTO main (SURNAME) VALUES '" & Me.SURNAME & "')"

 

[KevinSlater]

tried that workmad but it says "runtime error 3134, syntax error in insert into statement, think it needs to be a select statement.

 

[workmad3]

thats my fault for not bracket matching, sorry :$

SQL_Text = "INSERT INTO main (SURNAME) VALUES ('" & Me.SURNAME & "')"

 

[KevinSlater]

No problem, but it still doesnt work , ive tried this to:

SQL_Text = "Insert into main (SHIFTNAME) select '" & Me.SHIFTNAME & "'"

maybe it doesnt like my query

 

[ScottGem]

You need to review the syntax of Insert Into. There are 2 ways to do it. You can either select values from another table or supply a value list. The syntax for first is:

INSERT INTO table (fieldlist)
SELECT fieldlist FROM table;

So your use of select is incorrect and not going to work because you haven't specified a field list or a table.

But that's not what you want to do since you are adding values input on a form, so you want to supply a value list. The syntax for that is:

INSERT INTO table (fieldlist)
VALUES(fieldlist);

So what you need to do is concatenate the values into the field list. The values need to be surrounded by parentheses and you need to end the statement with a ;

This should work:

SQL_Text ="Insert into main (SHIFTNAME) VALUES('" & Me.shiftname & "');"

If that doesn't work, then you need to tell us what did happen (error msg?).

 

[KevinSlater]

That does help scottgem , thanks for your detailed help, i will try to get it to work

 

[KevinSlater]

Ok scottgem tried but still cant get it to work, any other suggestions anyone?, if i do it with a "Value" statement instead of select as shown below:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim SQL_Text As String
SQL_Text = "Insert into main (SHIFTNAME) VALUES '" & Me.SHIFTNAME & "'"
DoCmd.RunSQL (SQL_Text)

End Sub

i get this error message: "runtime error 3134 synax error in insert into statement"

if i change the line as you suggested to: SQL_Text ="Insert into main (SHIFTNAME) VALUES('" & Me.shiftname & "');"

i get the following message: "cant append all recorsds in the append query. 1 record due to validation rule violations, do you wish to run the query anyway yes/no, if i click yes it doesnt do anything (main table is emtty) if i click no it says" runtime error: 2501, the runsql action was cancelled"

 

[workmad3]

cant append all recorsds in the append query. 1 record due to validation rule violations

that means that you are trying to insert data that isnt valid. What is the type of the field you are trying to insert into, and what data are you trying to insert? And do you have any validation rules etc. set up on the field?