Solved

MS Access - Move record from one table to another on Button Click- VBA,SQL?

Posted on 2009-05-15
13
1,400 Views
Last Modified: 2012-08-13
I have two tables, with the exact same columns (Open Tickets and Closed_Tickets). I want to be able to click a button on the Open Tickets form and move the active record to the Closed_Tickets table and delete the record from the Open Tickets table if the Insert was successful.

I know it would be easier to use a status field, but this is what the customer wants to do.

So far I have a Command button that executes the code in the code box below but I get the following error: "Syntax Error (missing operator) in query '[Open Tickets].TicketNumber=& Me![TicketNumber]'

Please see my code and help me expand it :)  Thanks!
Private Sub Command119_Click()

On Error GoTo Err_Command119_Click
 

DoCmd.RunSQL "Insert Into [Completed_Tickets] Select * from [Open Tickets] where [Open Tickets].[TicketNumber]=& Me![TicketNumber]"
 

Exit_Command119_Click:

    Exit Sub
 

Err_Command119_Click:

    MsgBox Err.Description

    Resume Exit_Command119_Click

    

End Sub

Open in new window

0
Comment
Question by:synapse88
  • 8
  • 3
  • 2
13 Comments
 
LVL 10

Accepted Solution

by:
therealmongoose earned 500 total points
Comment Utility
This should do it...


docmd.setwarnings false

DoCmd.RunSQL "Insert Into [Completed_Tickets] Select * from [Open Tickets] where [Open Tickets].[TicketNumber]= " & Me![TicketNumber] & ";"
 

docmd.runsql "Delete [Open Tickets].* from [Open Tickets] where [Open Tickets].[TicketNumber]= " & Me![TicketNumber] & ";"

docmd.setwarings true

Open in new window

0
 

Author Comment

by:synapse88
Comment Utility
I'll run the code after lunch to check.

How can I ensure the Insert Command was successful?
0
 
LVL 10

Expert Comment

by:therealmongoose
Comment Utility
You can leave the docmd.setwarnings false statement out until you have debugged and are happy that the insert works....
0
 

Author Comment

by:synapse88
Comment Utility
When I run that Insert statement I get" Could Not Find Output table 'Completed_Tickets'" even though that table exists in the same database as the Open Tickets table...I see it listed right alongside Open Tickets. Do I need to qualify it with the database name or something?
0
 

Author Comment

by:synapse88
Comment Utility
Ok scratch that, now it's "Data Type mismatch in criteria expression"
0
 

Author Comment

by:synapse88
Comment Utility
I assume it has something to do with the where statement...but the Ticket Number field in the database is a Text type, and I assume anything in a Textbox is a Text value, so I'm not sure where the error is...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Has this got anything to do with MS SQL Server?
0
 

Author Comment

by:synapse88
Comment Utility
No it's all in Access...but it uses SQL language.
0
 

Author Comment

by:synapse88
Comment Utility
Oh I figured it out, just had to add in some single apostrophes.  Thanks therealmongoose:

Oh and for anyone reading this later I used a transaction to ensure the insert went ok.  Sample code below.



docmd.runsql "Delete [Open Tickets].* from [Open Tickets] where [Open Tickets].[TicketNumber]= '" & Me![TicketNumber] & "';"

docmd.setwarings true
 
 
 
 
 

Sub DoArchive()

On Error GoTo Err_DoArchive

  Dim ws As DAO.Workspace   'Current workspace (for transaction).

  Dim db As DAO.Database    'Inside the transaction.

  Dim bInTrans As Boolean   'Flag that transaction is active.

  Dim strSql As String      'Action query statements.

  Dim strMsg As String      'MsgBox message.
 

  'Step 1: Initialize database object inside a transaction.

  Set ws = DBEngine(0)

  ws.BeginTrans

  bInTrans = True

  Set db = ws(0)
 

  'Step 2: Execute the append.

  strSql = "INSERT INTO MyArchiveTable ( MyField, AnotherField, Field3 ) " & _

    "IN ""C:\My Documents\MyArchive.mdb"" " & _

    "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField = True);"

  db.Execute strSql, dbFailOnError
 

  'Step 3: Execute the delete.

  strSql = "DELETE FROM MyTable WHERE (MyYesNoField = True);"

  db.Execute strSql, dbFailOnError
 

  'Step 4: Get user confirmation to commit the change.

  strMsg = "Archive " & db.RecordsAffected & " record(s)?"

  If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then

    ws.CommitTrans

    bInTrans = False

  End If
 

Exit_DoArchive:

  'Step 5: Clean up

  On Error Resume Next

  Set db = Nothing

  If bInTrans Then   'Rollback if the transaction is active.

    ws.Rollback

  End If

  Set ws = Nothing

Exit Sub
 

Err_DoArchive:

  MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.number

  Resume Exit_DoArchive

End Sub

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>but it uses SQL language.<<
Then perhaps you should know that MS Access uses a diffrent SQL dialect to T-SQL (MS SQL Server) and PL/SQL (Oracle) and ... well you get the idea.

For example, the solution you posted in unusable in MS SQL Server.
0
 

Author Comment

by:synapse88
Comment Utility
"Then perhaps you should know that MS Access uses a diffrent SQL dialect to T-SQL (MS SQL Server) and PL/SQL (Oracle) and ... well you get the idea."

I'm well aware the syntax is different between different versions of SQL language.

"For example, the solution you posted in unusable in MS SQL Server."

Well aware.  I work in SQL server every day and if I needed this functionality I'd just write a stored procedure :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Fair enough.
0
 

Author Comment

by:synapse88
Comment Utility
I just posted it in SQL Server singe you can select up to 3 zones and I figured most anyone who has worked in MS SQL Server has probably worked with Access as well.  Perhaps a VB/VBA section would have been better...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now