If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append & Make Tables
Hi, Please Help...
I have a form with a comand button on to which I want to be able to click on this and it automatically run append queries and make table queries. There are multiple append queries and multiple make tables. How can I do this?? Thanks, Jez |
#2
|
|||
|
|||
Append & Make Tables
The easiest way would be something like (aircode):
Sub cmdButton_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" DoCmd.SetWarnings True End Sub -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jez" wrote in message ... Hi, Please Help... I have a form with a comand button on to which I want to be able to click on this and it automatically run append queries and make table queries. There are multiple append queries and multiple make tables. How can I do this?? Thanks, Jez |
#3
|
|||
|
|||
Append & Make Tables
To add to Arvin's answer just a little bit, if you are going to use
DoCmd.SetWarnings in code, then I recommend *always* including error handling code so that warnings will be restored in the event that the code terminates prematurely (ie. one of the queries fails, so you never get to the line of code at the end that restores warnings). Something like this: Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" ExitProc: DoCmd.SetWarnings True Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub You can also list the various queries by name in a table, and then use code to run them in the order desired. Here is a post that I made earlier this week that demonstrates this technique: http://www.microsoft.com/office/comm...a5b&sloc=en-us Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Arvin Meyer [MVP]" wrote: The easiest way would be something like (aircode): Sub cmdButton_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" DoCmd.SetWarnings True End Sub -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jez" wrote in message ... Hi, Please Help... I have a form with a comand button on to which I want to be able to click on this and it automatically run append queries and make table queries. There are multiple append queries and multiple make tables. How can I do this?? Thanks, Jez |
#4
|
|||
|
|||
Append & Make Tables
I hate to argue with my peers, but I wouldn't use OpenQuery. In fact, I'm
not sure OpenQuery is appropriate in this case. I'd recommend using the Execute method of the QueryDef object. This not only ensures no pop-up messages will occur, but will generate a trappable error if something goes wrong with the query. Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError Dim dbCurr As DAO.Database Dim qdfCurr As DAO.QueryDef Set dbCurr = CurrentDb() Set qdfCurr = dbCurr.QueryDefs("qryFirstMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qryFirstAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing ExitProc: Set dbCurr = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Tom Wickerath" AOS168b AT comcast DOT net wrote in message ... To add to Arvin's answer just a little bit, if you are going to use DoCmd.SetWarnings in code, then I recommend *always* including error handling code so that warnings will be restored in the event that the code terminates prematurely (ie. one of the queries fails, so you never get to the line of code at the end that restores warnings). Something like this: Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" ExitProc: DoCmd.SetWarnings True Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub You can also list the various queries by name in a table, and then use code to run them in the order desired. Here is a post that I made earlier this week that demonstrates this technique: http://www.microsoft.com/office/comm...a5b&sloc=en-us Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Arvin Meyer [MVP]" wrote: The easiest way would be something like (aircode): Sub cmdButton_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" DoCmd.SetWarnings True End Sub -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jez" wrote in message ... Hi, Please Help... I have a form with a comand button on to which I want to be able to click on this and it automatically run append queries and make table queries. There are multiple append queries and multiple make tables. How can I do this?? Thanks, Jez |
#5
|
|||
|
|||
Append & Make Tables
I agree with Doug's assessment. I stated that it's the easiest way, not the
best. I assessed Jez's experience with code as being a beginner, therefore I wrote code that would work and be easy to understand, as well as easy to implement. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Douglas J. Steele" wrote in message ... I hate to argue with my peers, but I wouldn't use OpenQuery. In fact, I'm not sure OpenQuery is appropriate in this case. I'd recommend using the Execute method of the QueryDef object. This not only ensures no pop-up messages will occur, but will generate a trappable error if something goes wrong with the query. Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError Dim dbCurr As DAO.Database Dim qdfCurr As DAO.QueryDef Set dbCurr = CurrentDb() Set qdfCurr = dbCurr.QueryDefs("qryFirstMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qryFirstAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing ExitProc: Set dbCurr = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Tom Wickerath" AOS168b AT comcast DOT net wrote in message ... To add to Arvin's answer just a little bit, if you are going to use DoCmd.SetWarnings in code, then I recommend *always* including error handling code so that warnings will be restored in the event that the code terminates prematurely (ie. one of the queries fails, so you never get to the line of code at the end that restores warnings). Something like this: Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" ExitProc: DoCmd.SetWarnings True Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub You can also list the various queries by name in a table, and then use code to run them in the order desired. Here is a post that I made earlier this week that demonstrates this technique: http://www.microsoft.com/office/comm...a5b&sloc=en-us Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Arvin Meyer [MVP]" wrote: The easiest way would be something like (aircode): Sub cmdButton_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "qryFirstMakeTable" DoCmd.OpenQuery "qrySecondMakeTable" DoCmd.OpenQuery "qryFirstAppend" DoCmd.OpenQuery "qrySecondAppend" DoCmd.SetWarnings True End Sub -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jez" wrote in message ... Hi, Please Help... I have a form with a comand button on to which I want to be able to click on this and it automatically run append queries and make table queries. There are multiple append queries and multiple make tables. How can I do this?? Thanks, Jez |
#6
|
|||
|
|||
Append & Make Tables
Hi Doug,
I agree with you, so there is no argument here. I was simply showing how to add error handling to the procedure that Arvin offered, in order to guarantee that warnings are always turned back on. I wanted to maintain the procedure that Arvin offered as much as possible, so that the OP could see how to build on the original to include the error handler. If you look at the link I provided for the post that I made last Monday, you will see that I am using .execute method for the action queries (ie. Case 32, 48, 80). Are you sure that you really need the Set qdfCurr statements? Why not just use something like this instead? Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError Dim dbCurr As DAO.Database Set dbCurr = CurrentDb() dbCurr.Execute "qryFirstMakeTable", dbFailOnError dbCurr.Execute "qrySecondMakeTable", dbFailOnError dbCurr.Execute "qryFirstAppend", dbFailOnError dbCurr.Execute "qrySecondAppend", dbFailOnError ExitProc: Set dbCurr = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Douglas J. Steele" wrote: I hate to argue with my peers, but I wouldn't use OpenQuery. In fact, I'm not sure OpenQuery is appropriate in this case. I'd recommend using the Execute method of the QueryDef object. This not only ensures no pop-up messages will occur, but will generate a trappable error if something goes wrong with the query. Option Compare Database Option Explicit Sub cmdButton_Click() On Error GoTo ProcError Dim dbCurr As DAO.Database Dim qdfCurr As DAO.QueryDef Set dbCurr = CurrentDb() Set qdfCurr = dbCurr.QueryDefs("qryFirstMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondMakeTable") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qryFirstAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing Set qdfCurr = dbCurr.QueryDefs("qrySecondAppend") qdfCurr.Execute dbFailOnError Set qdfCurr = Nothing ExitProc: Set dbCurr = Nothing Exit Sub ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdButton_Click..." Resume ExitProc End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#7
|
|||
|
|||
Append & Make Tables
Because I was too lazy to look up whether you could used a named query as
the source for the Execute method of the Database object. Yes, your way would be more efficient. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Tom Wickerath" AOS168b AT comcast DOT net wrote in message ... Are you sure that you really need the Set qdfCurr statements? Why not just use something like this instead? |
Thread Tools | |
Display Modes | |
|
|