Append Current record on Form using Form Button

  • Thread starter Thread starter KevinC
  • Start date Start date
K

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 ¡V 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
 
Create an Append Query that will insert a record in the history table using
the values of the controls on your form and execute the query in the Click
event of the command button. That is all that is required. It will have no
affect on the current record in the form.
You can either write the SQL statment in VBA or, more simply, create a
stored query that references the controls on the form. In either case, I
always recommend using the Execute statment rather than the RunSQL statment.
It is faster and you don't have to worry about warning messages.

Currentdb.Execute(qappAddToHistory), dbFailOnError

or
strSQL = "INSERT INTO..........."

Currentdb.Execute(strSQL), dbFailOnError
 
Hi Dave,

Can you give a simple example of what it is you mean? I have a vague
idea of what you mean but am not sure of how to set that up.

Regards,

Kevin
 
Sure, here is an example where you create an SQL statment in VBA and execute
it to create the new record:

Dim strSQL As String

strSQL = "INSERT INTO dbo_Employee ( EmployeeID, EmployeeFirstName,
EmployeeLastName, " & _
"EmployeeStartDate, EmployeeEndDate, EmployeeNumber ) " & _
"SELECT " & Me![txtEmployeeID] & " AS Expr1, " & _
Me![txtEmployeeFirstName] & " AS Expr2, " & Me![txtEmployeeLastName]
& _
" AS Expr3, " & Me![txtEmployeeStartDate] & " AS Expr4, " &
Me![txtEmployeeEndDate] & _
" AS Expr5, " & Me![txtEmployeeNumber] & " AS Expr6 FROM
dbo_Employee;"

CurrentDb.Execute (strSQL), dbFailOnError

Note the references to the form controls has to be outside the quotes so the
values of the controls will be in the string.
 
Back
Top