none
Insert a subform record into a new table using VBA RRS feed

  • Question

  • Hello,

    I have a form with a subform called Funds_subform. I have created a button that allows a user to delete a selected record in the subform. I want to keep track of any records that are deleted, so I'm trying to use a SQL INSERT statement to insert the record into a table called DeletedRecords when the record is deleted. I'm having trouble referencing the record in the subform in my INSERT statement. I have very limited Access knowledge. Below is the code I'm using that is not working:

            

    Dim strSQL As String
    strSQL = "INSERT INTO DeletedRecords([FiscalYear]) VALUES (Me.Funds_subform.Form.[Year])"

        If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then

            CurrentDb.Execute strSQL

            Me.Funds_subform.Form.Recordset.Delete

        End If

    I am trying to place the [Year] field in the FiscalYear column of the DeletedRecords table (just as a test - if I get it to work I will update the INSERT to insert all fields into the new table). Me.Funds_subform.Form.[Year] does not seem to be pulling the selected record's Year. When I click the button, the error I receive is:

    Run-time error '3061': Too few parameters. Expected 1.

    Please advise the correct way to reference the selected field in the subform or if there is a better way to do this.

    Thanks

    Friday, September 28, 2018 7:42 PM

Answers

  • The best way to do it is as Imb-hb suggested: use a field in the current table to mark the record as deleted, rather than actually deleting the record.

    However, the reason your current code is getting error 3061 is that the Execute method of the database object (returned by CurrentDb) doesn't know anything about Access objects such as forms and controls, nor VB objects references such as "Me".  To get around this, you could build the actual value into the SQL as a literal, rather than a reference to an object:

        strSQL = "INSERT INTO DeletedRecords([FiscalYear]) VALUES (" & Me.Funds_subform.Form.[Year] & ")"

    However, as I said, it's better to leave the record in place, and just mark it as "deleted".


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by DF12345 Tuesday, October 2, 2018 3:44 PM
    Sunday, September 30, 2018 4:57 PM

All replies

  • or if there is a better way to do this.

    Hi DF12345,

    Instead of transfer the record to an (identical) other table, you could also add a new field (Delete_date) to the table of type DateTime.

    When a record will be "deleted" the Delete_date is filled with Date. Your subform with the undeleted records will have the additional selection: Delete_date IS NULL.

    Resetting the Delete_date to Null will undeleted the record, if necessary.

    Imb.

    Friday, September 28, 2018 8:22 PM
  • The best way to do it is as Imb-hb suggested: use a field in the current table to mark the record as deleted, rather than actually deleting the record.

    However, the reason your current code is getting error 3061 is that the Execute method of the database object (returned by CurrentDb) doesn't know anything about Access objects such as forms and controls, nor VB objects references such as "Me".  To get around this, you could build the actual value into the SQL as a literal, rather than a reference to an object:

        strSQL = "INSERT INTO DeletedRecords([FiscalYear]) VALUES (" & Me.Funds_subform.Form.[Year] & ")"

    However, as I said, it's better to leave the record in place, and just mark it as "deleted".


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by DF12345 Tuesday, October 2, 2018 3:44 PM
    Sunday, September 30, 2018 4:57 PM
  • You might like to take a look at DeleteDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes examples of how to mark records as deleted, and how to delete records with backup, the latter being in essence what you are attempting.  In both cases 'deleted' records can be restored on demand.

    Ken Sheridan, Stafford, England

    Monday, October 1, 2018 10:11 PM