Append Current Record on a Form using Form Button

[KevinC]

Hi All,

I have two tables: tblLicensedPrem and tblLicensedPremHistory (these tables are identical).

tblLicensedPrem contains records for licensed premises. Over time details of a licensed premises change: e.g. the premises changes its company name, opening hours, manager, telephone number etc

What I would like to do is add a button to a form that when clicked:

- Firstly, appends the CURRENT record, in its current state, into the table tblLicensedPremHistory
- Secondly, allows editing of the current record so details can be updated (although I am not to worried about this step at the moment).

I think I am best off doing this in VBA – however I am new to this and struggling.

As a test (I’m trying to take this on one stage at a time!) I have added a button named cmdArchiveData to my form and as a starter tried to copy only the record with PremID equal to 1, and only the first three fields in tblLicensedPrem. This event is running off of the OnClick Event of the form button. However for some reason this is not working.

Can anyone tell me where I am going wrong?

Regards,

Kevin


-----CODE---

Private Sub cmdArchiveData_Click()
'Run Archive - Append to tblLicensedPremHistory

Dim db As Database
Dim strSQLAp As String

Set db = CurrentDb

strSQLAp = "INSERT INTO tblLicensedPremHistory( Prem_ID, LicNumber, PremName ) "
strSQLAp = strSQLAp & "SELECT tblLicensedPrem.Prem_ID, "
strSQLAp = strSQLAp & "tblLicensedPrem.LicNumber, "
strSQLAp = strSQLAp & "tblLicensedPrem.PremName, "
strSQLAp = strSQLAp & "FROM tblLicensedPrem "
strSQLAp = strSQLAp & "WHERE tblLicensedPrem.Prem_ID = 1;"

db.Execute strSQLAp

End Sub

[JustJim]

Hi All,

I have two tables: tblLicensedPrem and tblLicensedPremHistory (these tables are identical).

tblLicensedPrem contains records for licensed premises. Over time details of a licensed premises change: e.g. the premises changes its company name, opening hours, manager, telephone number etc

What I would like to do is add a button to a form that when clicked:

- Firstly, appends the CURRENT record, in its current state, into the table tblLicensedPremHistory
- Secondly, allows editing of the current record so details can be updated (although I am not to worried about this step at the moment).

I think I am best off doing this in VBA – however I am new to this and struggling.

As a test (I’m trying to take this on one stage at a time!) I have added a button named cmdArchiveData to my form and as a starter tried to copy only the record with PremID equal to 1, and only the first three fields in tblLicensedPrem. This event is running off of the OnClick Event of the form button. However for some reason this is not working.

Can anyone tell me where I am going wrong?

Regards,

Kevin


-----CODE---

Private Sub cmdArchiveData_Click()
'Run Archive - Append to tblLicensedPremHistory

Dim db As Database
Dim strSQLAp As String

Set db = CurrentDb

strSQLAp = "INSERT INTO tblLicensedPremHistory( Prem_ID, LicNumber, PremName ) "
strSQLAp = strSQLAp & "SELECT tblLicensedPrem.Prem_ID, "
strSQLAp = strSQLAp & "tblLicensedPrem.LicNumber, "
strSQLAp = strSQLAp & "tblLicensedPrem.PremName, "
strSQLAp = strSQLAp & "FROM tblLicensedPrem "
strSQLAp = strSQLAp & "WHERE tblLicensedPrem.Prem_ID = 1;"

db.Execute strSQLAp

End Sub

Hi,
Instead of working directly into the history table, try opening a recordset based on that table and appending your record to that recordset. Key words to check in the helpfiles would be recordset, addnewand of course, don't forget to update.

Jim

[KevinC]

Hi Jim,

But wouldn't it be easier to just run one append query? I will actually want to append all fields from the original table into the history table and it seems a single append query should work for this - or am I incorrent?

I am getting the following error at present when I run my code give above:

Run-time error '3134':
Syntax error in INSERT INTO statment.

When I go into debug the arrow points to the line ---> db.Execute strSQLAp

Regards,

Kevin

[JustJim]

Hi Jim,

But wouldn't it be easier to just run one append query? I will actually want to append all fields from the original table into the history table and it seems a single append query should work for this - or am I incorrent?

I am getting the following error at present when I run my code give above:

Run-time error '3134':
Syntax error in INSERT INTO statment.

When I go into debug the arrow points to the line ---> db.Execute strSQLAp

Regards,

Kevin

OK, I thought you wanted to do it one at a time as you moved through the records on your form.

The INSERT INTO clause may have problems with the spaces and brackets in the first line of the SQL block and doesn't need the semi-colon at the end.

Here is a way to do the duplicate checking and insertion in one go using VBA. Of course declarations need to be made, field names changed to suit you and the WHERE clause changed as well.

'   Find unmatched Programme entries in table "tblNewArrivals"/"Tbl_Programmes" and open that data as a recordset
strSQL = "SELECT tblNewArrivals.School, tblNewArrivals.Campus " & _
"FROM tblNewArrivals LEFT JOIN Tbl_Programmes ON tblNewArrivals.School = Tbl_Programmes.School_No " & _
"WHERE (((Tbl_Programmes.School_No) Is Null) AND ((Tbl_Programmes.Campus_No) Is Null));"
Set rsNewProg = dbNAP.OpenRecordset(strSQL)
 
'   also open the real programmes table as a recordset
strSQL = "SELECT * from Tbl_Programmes"
Set rsProg = dbNAP.OpenRecordset(strSQL)
 
'   Add records from new programmes to real programmes table
Do Until rsNewProg.EOF
 
    With rsProg
        .AddNew
            !School_No = rsNewProg!School
            !Campus_No = rsNewProg!Campus
            !NAP_Provider_ID = 9 '  no information in tblNewArrivals for these fields, but they are required in the
            !Outpost_Host_ID = 9 '  table to fulfill Referential Integrity requirements.  ID #9 is a "No Provider" entry
        .Update
    End With
 
    rsNewProg.MoveNext
 
Loop

Enjoy

Jim