Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-15
13
Medium Priority
?
1,796 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
13 Comments
 
LVL 10

Accepted Solution

by:
therealmongoose earned 2000 total points
ID: 24396598
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
ID: 24396869
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
ID: 24396992
You can leave the docmd.setwarnings false statement out until you have debugged and are happy that the insert works....
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:synapse88
ID: 24398028
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
ID: 24398069
Ok scratch that, now it's "Data Type mismatch in criteria expression"
0
 

Author Comment

by:synapse88
ID: 24398096
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
 
LVL 75

Expert Comment

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

Author Comment

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

Author Comment

by:synapse88
ID: 24399066
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
ID: 24400541
>>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
ID: 24403311
"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
ID: 24403325
Fair enough.
0
 

Author Comment

by:synapse88
ID: 24403673
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

705 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