Append Current Record on a Form using Form Button (1 Viewer)

[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

 

[ecawilkinson]

Kevin,

when you say it is not working, are you getting an error message, or does it appear that nothing is happening? Also, is Prem_iD in tblLicensedPremHistory an autonumber datatype?

Chris

 

[KevinC]

Error Message

Hi Chris,

The Prem_iD field in tblLicensedPremHistory is NOT an autonumber datatype.

The error message I am getting is as follows:



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

 

[namliam]

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;"

Comma to many !