General discussion

Locked

Macros in MS Access

By sjbroyles ·
I have created a macro which executes several
queries each appends records to a table. However if one query results in no records it halts the macro. How can I get the macro to move on even if this occurs?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Macros in MS Access

by Shanghai Sam In reply to Macros in MS Access

One way to solve your problem is convert
macro into VB code, and in this code define
workspace object and querydef objects,
initialise querydefs (CreateQueryDef method) and assign it parameters. Later you can set error handler (on error goto Label - see Access help), start transaction, execute queries, commit transaction (or rollback it on errors). See sample below

Dim Workspace0 As Workspace
Dim Database0 As Database
Dim QueryDef1 As QueryDef
Dim QueryDef2 As QueryDef

Dim Flag As Integer

Set Workspace0 = DBEngine.Workspaces(0)
Set Database0 = Workspace0.Databases(0)

Set QueryDef1 = Database0.CreateQueryDef("", "insert ...")
Set QueryDef2 = Database0.CreateQueryDef("", "update ...")

QueryDef1.Parameters(0) = Forms![fOrders]![Field35]
QueryDef1.Parameters(1) = Forms![fOrders]![fInvoices].Form![Field19]
QueryDef2.Parameters(0) = Forms![fOrders]![Field35]
QueryDef2.Parameters(1) = Forms![fOrders]![fInvoices].Form![Field19]

Flag = True

On Error GoTo E

Workspace0.BeginTrans

If Flag Then
QueryDef1.Execute (DB_FAILONERROR)
End If

If Flag Then
QueryDef2.Execute (DB_FAILONERROR)
End If

If Flag Then
Workspace0.CommitTrans
Exit Sub End If

Workspace0.Rollback

Exit Sub

E:
Flag = False
Resume Next

Collapse -

Macros in MS Access

by sjbroyles In reply to Macros in MS Access

Poster rated this answer

Collapse -

Macros in MS Access

by avhohlov In reply to Macros in MS Access

*** This is repeating of first answer - only for test ***
One way to solve your problem is convert
macro into VB code, and in this code define
workspace object and querydef objects,
initialise querydefs (CreateQueryDef method) and assign it parameters. Later you can set error handler (on error goto Label - see Access help), start transaction, execute queries, commit transaction (or rollback it on errors). See sample below

Dim Workspace0 As Workspace
Dim Database0 As Database
Dim QueryDef1 As QueryDef
Dim QueryDef2 As QueryDef

Dim Flag As Integer

Set Workspace0 = DBEngine.Workspaces(0)
Set Database0 = Workspace0.Databases(0)

Set QueryDef1 = Database0.CreateQueryDef("", "insert ...")
Set QueryDef2 = Database0.CreateQueryDef("", "update ...")

QueryDef1.Parameters(0) = Forms![fOrders]![Field35]
QueryDef1.Parameters(1) = Forms![fOrders]![fInvoices].Form![Field19]
QueryDef2.Parameters(0) = Forms![fOrders]![Field35]
QueryDef2.Parameters(1) = Forms![fOrders]![fInvoices].Form![Field19]

Flag = True

On Error GoTo E

Workspace0.BeginTrans

If Flag Then
QueryDef1.Execute (DB_FAILONERROR)
End If

If Flag Then
QueryDef2.Execute (DB_FAILONERROR)
End If

If Flag Then
Workspace0.CommitTrans
Exit Sub
End If

Workspace0.Rollback

Exit Sub

E:
Flag = False
Resume Next

Collapse -

Macros in MS Access

by sjbroyles In reply to Macros in MS Access

Poster rated this answer

Collapse -

Macros in MS Access

by sjbroyles In reply to Macros in MS Access

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums