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