Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: docmd.runsql insert into Problem

    Good Evening

    I could do with some help with an issue I am having, on a form I have a button "Delete Record" which actually move a record to another table first then deletes it.

    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO [DeletedWarningNotices] ([ID],[Issue Date], [VRN],[Vehicle Make], [Location], [Officer], [Description],[Violation], [Infringement Number], [Paid], [Canceled], [Appealed], [Amount Paid], [Date Appealed], [Date Canceled], [Date VQ5 Requested], [Date of Invoice Sent], [Staff or Student Name], [Invoice Sent to]) " & _
    "SELECT [WarningNotices].[ID],[WarningNotices].[Issue Date], [WarningNotices].[VRN], [WarningNotices].[Vehicle Make], [WarningNotices].[Location],[WarningNotices].[Officer],[WarningNotices].[Description],[WarningNotices].[Violation],[WarningNotices].[Infringement Number],[WarningNotices].[Paid],[WarningNotices].[Canceled], [WarningNotices].[Appealed],[WarningNotices].[Amount Paid],[WarningNotices].[Date Appealed],[WarningNotices].[Date Canceled],[WarningNotices].[Date VQ5 Requested], [WarningNotices].[Date of Invoice Sent],[WarningNotices].[Staff or student name],[WarningNotices].[Invoice sent to] " & _
    "FROM [WarningNotices] " & _
    "WHERE ([WarningNotices].[ID])= " & Me.ID & "; ")
    DoCmd.RunSQL "DELETE * From " & "WarningNotices" & " Where [ID] =" & Me.ID
    DoCmd.SetWarnings True
    Me.Form.Requery

    The ID is set as an auto number

    The user can also chose a button to restore the record if deleted accidentally

    The ID in this table is set to Text as if you set it to auto number when you restore the record it over writes a record which is the corrosponding record. But if you try to add the record with the ID number which was removed it will not allow you

    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO [WarningNotices] ([Issue Date],[VRN],[Vehicle Make], [Location], [Officer], [Description],[Violation], [Infringement Number], [Paid], [Canceled], [Appealed], [Amount Paid], [Date Appealed], [Date Canceled], [Date VQ5 Requested], [Date of Invoice Sent], [Staff or Student Name], [Invoice Sent to]) " & _
    "SELECT [DeletedWarningNotices].[Issue Date], [DeletedWarningNotices].[VRN], [DeletedWarningNotices].[Vehicle Make], [DeletedWarningNotices].[Location],[DeletedWarningNotices].[Officer],[DeletedWarningNotices].[Description],[DeletedWarningNotices].[Violation],[DeletedWarningNotices].[Infringement Number],[DeletedWarningNotices].[Paid],[DeletedWarningNotices].[Canceled], [DeletedWarningNotices].[Appealed],[DeletedWarningNotices].[Amount Paid],[DeletedWarningNotices].[Date Appealed],[DeletedWarningNotices].[Date Canceled],[DeletedWarningNotices].[Date VQ5 Requested], [DeletedWarningNotices].[Date of Invoice Sent],[DeletedWarningNotices].[Staff or student name],[DeletedWarningNotices].[Invoice sent to] " & _
    "FROM [DeletedWarningNotices] " & _
    "WHERE ([DeletedWarningNotices].[ID])= " & Me.ID & "; ")
    DoCmd.RunSQL "DELETE * From " & "DeletedWarningNotices" & " Where [ID] =" & Me.ID
    DoCmd.SetWarnings True
    Me.Form.Requery

    I tried removing the ID when restoring the record hoping it would go to a new record in the Warningsnotices Table but it come up with a miss match error, as there is no ID field in the deletedwarningnotices table.

    How can I restore the record with it going to a new entry and not overwriting any of the previous entrys

    Thank for your time

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you cannot reinstate an autonumber column. you cannot supply a value for an autonumber column. an autonumber column will allocate a new number when you attemtp to insert a row
    just a though why do you have a separate table for deleted warning notices, especailly if there is a risk that you may want to delete /reinstate rows. If it were me I'd use one of two options. leave the code as is, so users will qucikly learn to be careful
    OR
    add a column which identifies that row has been deleted BUT dont actually delete the row

    of the two I guess the latter is the more sensible option. if for no other reason you dont' need to add new forms / reports
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by pjawynn View Post
    The ID in this table is set to Text as if you set it to auto number when you restore the record it over writes a record which is the corrosponding record. But if you try to add the record with the ID number which was removed it will not allow you
    This makes little sense. If you want to keep the ID in the [DeletedWarningNotices] table, the column should be defined as numeric (Long) there. You're right in saying that you won't be able to restore the ID in the original table, though, as it is defined as an Autonumber there and you cannot write (insert) in an Autonumber column. Carefully compare the data definition of all columns in both tables, that's probaly the source of the error.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •