Form Code HELP ??

N

NEWER USER

I am have been trying to eliminate having to delete and then redefine my
query in the code below. Can anyone help or suggest how I would modify?

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate
Dim stDocName1 As String
Dim stDocName2 As String
Dim MyQueryDef As QueryDef
Dim db As DAO.Database
Set db = CurrentDb()

If ObjectExists("Queries", "qryUpdateBRSales") = True Then
db.QueryDefs.Delete "qryUpdateBRSales"
End If

If ObjectExists("Queries", "qryUpdateStatus") = True Then
db.QueryDefs.Delete "qryUpdateStatus"
End If

Set MyQueryDef = db.CreateQueryDef("qryUpdateBRSales", "UPDATE
[tblSales] INNER JOIN tblProduct ON [tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" +
Forms!frmSelectColumn!Text0 + "]")
Set MyQueryDef = db.CreateQueryDef("qryUpdateStatus", "UPDATE
[tblStatus] INNER JOIN tblProduct ON [tblStatus].ProductID =
tblProduct.ProductID SET tblProduct.Status = [" + Forms!frmSelectColumn!Text0
+ "]")

stDocName1 = "qryUpdateBRSales"
stDocName2 = "qryUpdateStatus"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close

Exit_Text0_AfterUpdate:
Exit Sub

Err_Text0_AfterUpdate:
MsgBox Err.Description
Resume Exit_Text0_AfterUpdate

End Sub
 
M

Marshall Barton

NEWER said:
I am have been trying to eliminate having to delete and then redefine my
query in the code below. Can anyone help or suggest how I would modify?

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate
Dim stDocName1 As String
Dim stDocName2 As String
Dim MyQueryDef As QueryDef
Dim db As DAO.Database
Set db = CurrentDb()

If ObjectExists("Queries", "qryUpdateBRSales") = True Then
db.QueryDefs.Delete "qryUpdateBRSales"
End If

If ObjectExists("Queries", "qryUpdateStatus") = True Then
db.QueryDefs.Delete "qryUpdateStatus"
End If

Set MyQueryDef = db.CreateQueryDef("qryUpdateBRSales", "UPDATE
[tblSales] INNER JOIN tblProduct ON [tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" +
Forms!frmSelectColumn!Text0 + "]")
Set MyQueryDef = db.CreateQueryDef("qryUpdateStatus", "UPDATE
[tblStatus] INNER JOIN tblProduct ON [tblStatus].ProductID =
tblProduct.ProductID SET tblProduct.Status = [" + Forms!frmSelectColumn!Text0
+ "]")

stDocName1 = "qryUpdateBRSales"
stDocName2 = "qryUpdateStatus"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close

Exit_Text0_AfterUpdate:
Exit Sub

Err_Text0_AfterUpdate:
MsgBox Err.Description
Resume Exit_Text0_AfterUpdate

End Sub
Click to expand...


If the query exists, just set the query's SQL property to
the SQL statement.

OTOH, why bother using a saved QueryDef? instead, just use
the Execute method to run the query:

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate

Set db = CurrentDb()

db.Execute "UPDATE [tblSales] INNER JOIN tblProduct ON
[tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" &
Forms!frmSelectColumn!Text0 & "]"), dbFailOnError
db.Execute "UPDATE [tblStatus] INNER JOIN tblProduct ON
[tblStatus].ProductID = tblProduct.ProductID SET
tblProduct.Status = [" & Forms!frmSelectColumn!Text0
& "]"), dbFailOnError

Set db = Nothing

Exit_Text0_AfterUpdate:
. . .
 
D

Daryl S

Newer User -

Set these queries up correctly in design mode (see below). You don't need
to rebuild them each time if you put the form references right into the
queries in query deign mode. Then you can remove (or comment out) the code
for checking to see if the queries exist, deleting them, and rebuilding them.
You only need to run them.

To fix the queries, open them in design mode, and change just the update
fields for the couple items you want pulled from the form. Put in the
'Update to' row Forms!frmSelectColumn!Text0 where it now contains the
literal value that was on the form the last time it was run. Same for the
other query. You can even use Expression Builder if you want to navigate to
the right form control to prevent typos.
 
N

NEWER USER

Worked Perfect. Thanks again. Why do you Set db = Nothing?

Marshall Barton said:
NEWER said:
I am have been trying to eliminate having to delete and then redefine my
query in the code below. Can anyone help or suggest how I would modify?

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate
Dim stDocName1 As String
Dim stDocName2 As String
Dim MyQueryDef As QueryDef
Dim db As DAO.Database
Set db = CurrentDb()

If ObjectExists("Queries", "qryUpdateBRSales") = True Then
db.QueryDefs.Delete "qryUpdateBRSales"
End If

If ObjectExists("Queries", "qryUpdateStatus") = True Then
db.QueryDefs.Delete "qryUpdateStatus"
End If

Set MyQueryDef = db.CreateQueryDef("qryUpdateBRSales", "UPDATE
[tblSales] INNER JOIN tblProduct ON [tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" +
Forms!frmSelectColumn!Text0 + "]")
Set MyQueryDef = db.CreateQueryDef("qryUpdateStatus", "UPDATE
[tblStatus] INNER JOIN tblProduct ON [tblStatus].ProductID =
tblProduct.ProductID SET tblProduct.Status = [" + Forms!frmSelectColumn!Text0
+ "]")

stDocName1 = "qryUpdateBRSales"
stDocName2 = "qryUpdateStatus"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close

Exit_Text0_AfterUpdate:
Exit Sub

Err_Text0_AfterUpdate:
MsgBox Err.Description
Resume Exit_Text0_AfterUpdate

End Sub
Click to expand...


If the query exists, just set the query's SQL property to
the SQL statement.

OTOH, why bother using a saved QueryDef? instead, just use
the Execute method to run the query:

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate

Set db = CurrentDb()

db.Execute "UPDATE [tblSales] INNER JOIN tblProduct ON
[tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" &
Forms!frmSelectColumn!Text0 & "]"), dbFailOnError
db.Execute "UPDATE [tblStatus] INNER JOIN tblProduct ON
[tblStatus].ProductID = tblProduct.ProductID SET
tblProduct.Status = [" & Forms!frmSelectColumn!Text0
& "]"), dbFailOnError

Set db = Nothing

Exit_Text0_AfterUpdate:
. . .
Click to expand...
 
M

Marshall Barton

Just good housekeeping so there are no stray objects laying
around. Access is suposed to do that automatically when the
code procedure exits, but, at least in the past, there are
cases where that did not happen. Then strange things like
not being able to close Access would seemingly come from
nowhere.
--
Marsh
MVP [MS Access]


NEWER said:
Worked Perfect. Thanks again. Why do you Set db = Nothing?

Marshall Barton said:
NEWER said:
I am have been trying to eliminate having to delete and then redefine my
query in the code below. Can anyone help or suggest how I would modify?

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate
Dim stDocName1 As String
Dim stDocName2 As String
Dim MyQueryDef As QueryDef
Dim db As DAO.Database
Set db = CurrentDb()

If ObjectExists("Queries", "qryUpdateBRSales") = True Then
db.QueryDefs.Delete "qryUpdateBRSales"
End If

If ObjectExists("Queries", "qryUpdateStatus") = True Then
db.QueryDefs.Delete "qryUpdateStatus"
End If

Set MyQueryDef = db.CreateQueryDef("qryUpdateBRSales", "UPDATE
[tblSales] INNER JOIN tblProduct ON [tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" +
Forms!frmSelectColumn!Text0 + "]")
Set MyQueryDef = db.CreateQueryDef("qryUpdateStatus", "UPDATE
[tblStatus] INNER JOIN tblProduct ON [tblStatus].ProductID =
tblProduct.ProductID SET tblProduct.Status = [" + Forms!frmSelectColumn!Text0
+ "]")

stDocName1 = "qryUpdateBRSales"
stDocName2 = "qryUpdateStatus"

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close

Exit_Text0_AfterUpdate:
Exit Sub

Err_Text0_AfterUpdate:
MsgBox Err.Description
Resume Exit_Text0_AfterUpdate

End Sub
Click to expand...


If the query exists, just set the query's SQL property to
the SQL statement.

OTOH, why bother using a saved QueryDef? instead, just use
the Execute method to run the query:

Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate

Set db = CurrentDb()

db.Execute "UPDATE [tblSales] INNER JOIN tblProduct ON
[tblSales].ProductID =
tblProduct.ProductID SET tblProduct.BRSales = [" &
Forms!frmSelectColumn!Text0 & "]"), dbFailOnError
db.Execute "UPDATE [tblStatus] INNER JOIN tblProduct ON
[tblStatus].ProductID = tblProduct.ProductID SET
tblProduct.Status = [" & Forms!frmSelectColumn!Text0
& "]"), dbFailOnError

Set db = Nothing

Exit_Text0_AfterUpdate:
. . .
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cleanup Code on Form ?? 4
run-time error 2467 2
CODE HELP! 2
Help with sending two parameters to Stored Procedure. 4
Simplify Code-Delete Query 3
Email Coding that I have no Idea what its doing... 0
Text field vs. Number field 7
HOW TO Simplify following code 4

Top