Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to run multiple update queries from one button?

Posted on 2012-08-16
8
Medium Priority
?
481 Views
Last Modified: 2012-08-16
How can I run multiple update queries from the same button. The below works, but I cannot run more than one query.

Dim stDocName As String

    stDocName = "query1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Thank you very much!
0
Comment
Question by:kat50
  • 4
  • 4
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 38301638
With CurrentDB
    .Execute "Query1", dbFailOnerror
    .Execute "Query2", dbFailOnerror
    .Execute "Query3", dbFailOnerror
 ' and so on
End With

mx
0
 

Author Comment

by:kat50
ID: 38301678
I am sorry, I am a beginner and I will need a little more help with this.

What preceeds the .Execute commands?   How do I define the execution?  I get a compile error  Invalid or unqualified reference.

Thank you very much.
0
 
LVL 75
ID: 38301727
Well, nothing but the DOT proceeds the Execute command.
You don't need to define anything (execution).
This is the same thing:

CurrentDB.Execute "Query1", dbFailOnError

What version of Access ?

Do you have a Reference set to DAO (see image)

1
The code I posted is all you need ... there is no other code.  Put this in a button Click event.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kat50
ID: 38301764
I need to embed these queries after the following.  I have the reference you have noted.

Option Compare Database

Private Sub AFES___New_CEP_12496686_1_BeforeUpdate(Cancel As Integer)
Me.AFES___New_CEP_12496686_1.Enabled
End Sub



Private Sub Command4_Click()

Dim strInputFileName As String
Dim strExt As String
Dim strFileTemp As String

strInputFileName = ahtCommonFileOpenSave(OpenFile:=True, _
                DialogTitle:="Please select an payment input file...", _
                Flags:=ahtOFN_HIDEREADONLY)

' Read the file extenstion
strExt = Mid(strInputFileName, InStr(1, strInputFileName, "."))
Me.Text2 = strInputFileName

' Replace the extension with _Temp.txt for a new file name
strFileTemp = Replace(strInputFileName, strExt, "_Temp.txt")

' Copy the selected file to a temp .txt file
FileCopy strInputFileName, strFileTemp

' Import the temp file
DoCmd.TransferText acImportFixed, "Import", "tblImport", strFileTemp, False

' Delete the temp file
Kill strFileTemp



End Sub
0
 
LVL 75
ID: 38301824
Private Sub Command4_Click()

Dim strInputFileName As String
Dim strExt As String
Dim strFileTemp As String

strInputFileName = ahtCommonFileOpenSave(OpenFile:=True, _
                DialogTitle:="Please select an payment input file...", _
                Flags:=ahtOFN_HIDEREADONLY)

' Read the file extenstion
strExt = Mid(strInputFileName, InStr(1, strInputFileName, "."))
Me.Text2 = strInputFileName

' Replace the extension with _Temp.txt for a new file name
strFileTemp = Replace(strInputFileName, strExt, "_Temp.txt")

' Copy the selected file to a temp .txt file
FileCopy strInputFileName, strFileTemp

' Import the temp file
DoCmd.TransferText acImportFixed, "Import", "tblImport", strFileTemp, False

' Delete the temp file
Kill strFileTemp

With CurrentDB
    .Execute "Query1", dbFailOnerror
    .Execute "Query2", dbFailOnerror
    .Execute "Query3", dbFailOnerror
 ' and so on
End With


End Sub
0
 

Author Comment

by:kat50
ID: 38301965
I receive it cannot find "query22"  It is named exactly as you have it here.  Could this be because it is an update query?  I receive the same error if I use the button wizard and add additional queries.

Thank you very much for being patient with me.
0
 
LVL 75
ID: 38301988
Well ... you need to substitute the exact name of your queries.

Also, Execute only works for Action queries, so there is no problem there.

That error message is pretty clear ... "Can't find query" ...

I'll be back in an hour or so ... fyi.

mx
0
 

Author Closing Comment

by:kat50
ID: 38302105
Thank you very much!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

569 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