Append Form Data to a Table

[sds568219]

I have a Form which contains Data from Table1. Under each of the data fields textbox is an unbound text box. This is to allow me to identify changes. Example: Field1 = "Steve", txtField1 = "Mike". I want to Append/Insert txtField1 into Table2, which contains the same data fields and structure as Table1. I've tried using an Append Query, and pointing to the Form!frmXXXX.txtField1 but it appends 0 Records. Any suggestions?

 

[AlexCuse]

I think your best bet is to build a SQL String around the text boxes. It would go something like this:

Dim strSQL as String

strSQL = "insert into TABLE2 (MYFIELD) select """ & _
txtField1.Value & & """"

DoCmd.RunSQL strSQL

If you want to do this for more columns it gets trickier of course.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sds568219]

AlexCuse,

Thank you. It does prompt me to append the record, but won't append because of the Null values of the other fields (30). How do you overcome the Null values?

 

[AlexCuse]

I would either append something to them as a placeholder or alter your table design so that your table allows nulls in the other fields.

What is to be done with your new record after it is created?

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sds568219]

AlexCuse,

I had thought about just putting a default value ="NC" for no change that would eliminate the NZ problem.

I want to store the changes so I can produce a report showing the complete record and requested changes, who requested the change, date of request, etc. Then have the requester send the change request via email.

Thanks for your assistance.

Steve

 

[AlexCuse]

This should get you on the right track then. You just need to reference every column that cannot be null and insert something into it. Like this:

Dim strSQL as String

strSQL = "insert into TABLE2 (MYFIELD, otherColumn, anotherColumn) select """ & _
txtField1.Value & & """, ""NC"", ""NC"""

DoCmd.RunSQL strSQL

Notice the order the SQL is constructed, with the record being appended first, and placeholder "NC" inserted into the other columns. Another important thing to note is the use of 2 double quotes ("") inside quotes when building your string. Two of these side by side are needed to represent one double quote in the final string.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom