Append a table to another table

[Shap]

Hello,
I am still at the beginning of my learning process but already have some problems. I used Access long time ago, and some things changes, while I forgot a lot. So, here is my problem:
I have a table (Table1), which is supposed to be appended to a Table2. Table2 had 2 additional fields, in addition to the fields in Table1. Values for those two fields are in 2 text boxes on the form (txtStardDate and txtEndDate)
I know I have to write VBA where I will open database, and two recordsets, edit them, and make a look, but whatever I tried, did not work. Anybody had a code that will do something like this?
Many thanks.

[Scott Price]

Why not look into an append query? Create a regular select query in design view, then right-click change to Append, and set the two extra values by referencing the text boxes: Forms![FormName]!txtStartDate Etc...

Regards,
Scott

p.s. you can also run an append query from vb code by using the DoCmd.RunSQL [ValidSQLStatement] route.

[Shap]

Thanks for the reply. That was my first idea, but thisngs are bit more complicated here. Simply append is not possible, since tex boxes are Text fileds, and fields in Table2 are Date type. So, I have to perform a CDate( ) in the meantime, and can not just refer to the form.
Still, I believe I have to go through the code, but have problems with ADO (how to open recordset, how to move through it, how to edit and save). I hope somebody will help me here.
Regards.

[MMcCarthy]

Thanks for the reply. That was my first idea, but thisngs are bit more complicated here. Simply append is not possible, since tex boxes are Text fileds, and fields in Table2 are Date type. So, I have to perform a CDate( ) in the meantime, and can not just refer to the form.
Still, I believe I have to go through the code, but have problems with ADO (how to open recordset, how to move through it, how to edit and save). I hope somebody will help me here.
Regards.

You don't need to use ADO. You can run an INSERT (append) statement from code.

Dim strSQL As String
 
    strSQL = "INSERT INTO TableName (Field1, Field2, Field3, Field4) " & _
        "VALUES (" & Me!IDField & ", '" & Me!SomeTextField & "', " & Me!SomeNumField & ", #" & Me!SomeDateField & "#)"
 
    DoCmd.RunSQL strSQL
 

[Scott Price]

Thanks, Mary... This seems to have slipped through the cracks in my subscribed threads box :-(

As for using the CDate() function to change from text to date, this is easily done also by storing first into a variable, then calling the variable in the SQL statement.

I.e:

Dim MyDate As Date/Time
 
MyDate = CDate(Me!TextBoxWithDate)

Regards,
Scott