Solved: Access Macro

[MMQQ]

Hello,

I think it's a simple answer, but haven't had luck to figure it out. I have a switchboard where button # 1 is to open up a select query and seek user input as to the criteria/filter.

I also have a button #2 that requires the user to press to just to automatically run the rest of the make and append queries.

Really button #1 and #2 should be combined in one button. After the select query is saved and closed, the make and append should follow automatically.

I tried using openquery and then runmacro and noticed that while the select query is open at the design mode, #2 is also run simulataneously.

MM

[OBP]

You should be able to put all of the Open Query statements on one Command Buttton.
Just place them in the order that you want them to run.

[MMQQ]

I tried and it doesn't work. For the same command button I have the select query and the make query. At the same time the select query is open it asked me whether I was to make table. Is there a way for the macro to detect the select query is closed before it proceeds to the make table?Thanks,MM
You should be able to put all of the Open Query statements on one Command Buttton.
Just place them in the order that you want them to run.

[CreganTur]

Is there a way for the macro to detect the select query is closed before it proceeds to the make table?

Without seeing your code, there are too many variables for us to provide a good answer. Can you post the code, and please be sure to wrap it with VBA tags so it is easy to read (click the green VBA button when posting)

[MMQQ]

The green vba button is disabled at work, sorry about that...Here's my switchboard: I want to make "Macros_Create_the_Report" automatically follow the previous one without any clicking.'------------------------------------------------------------' mcr00_Switchboard_Macros_Make_a_SelectionFunction mcr00_Switchboard_Macros_Make_a_Selection() DoCmd.OpenQuery "Selection_1 (User Selection Here)", acViewDesign, acEditEnd Function'------------------------------------------------------------' mcr00_Switchboard_Macros_Create_the_Report''------------------------------------------------------------Function mcr00_Switchboard_Macros_Create_the_Report() DoCmd.RunMacro "mcr03_Identify Mgmt Levels", , ""End Function--------------------------------------------------------Here mcr03 in detail (it's a mixture of select,append, create, update queries) and they rely on "Selection_1 (User Selection Here)":Function mcr03_Identify_Mgmt_Levels() DoCmd.Echo False, "" DoCmd.SetWarnings False DoCmd.OpenQuery "Reporting Relationship_01a (Identify Supervisors)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_01b (add direct reports outside of unit)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02b (Group 2a)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02c (Update Reporting Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_02d (Counter Up)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_03 (Summary of All Supervisors)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_04a (Create Tbl for Current TopDown Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_04b (Create Tbl for Cur Bottom Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_04c (Update Highest Level Mgmt)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05a (Update Mgrs of Next Level)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_05b (Update Current Top Down Lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_06 (All mgr grp 2 min reporting mgmt lvl)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_07 (update mgmt rpt lvl for all staff)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08a (Rpt Lvl 9 for unknown reportings)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08b (9 - rpt supervisor not in unit)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08c (9 - Reporting Supervisor Pos Vacant)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08d (9 - Pos has not reporting relation)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08e (9 - On assignment - no position num)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_08f (9 - No Pos)", acViewNormal, acEdit DoCmd.OpenQuery "Reporting Relationship_09 (Final tbl)", acViewNormal, acEdit Call CloseAllTblAndQry DoCmd.Echo True, "" DoCmd.SetWarnings TrueEnd Function

[MMQQ]

Oops, sorry about the above formating. I did no write any macro for the command buttons. I just used the macro wizard to do so.

In Summary:
Button #1:
DoCmd.OpenQuery "Selection_1 (User Selection Here)", acViewDesign, acEditEnd

Button #2 (through macro 3):
DoCmd.OpenQuery "Reporting Relationship_01a (Identify Supervisors)", acViewNormal, acEdit

DoCmd.OpenQuery "Reporting Relationship_01b (add direct reports outside of unit)", acViewNormal, acEdit

DoCmd.OpenQuery "Reporting Relationship_02a (Is employee also a supervisor)", acViewNormal, acEdit
.....

If I combine button #1 and #2 into in, query 1 opens in design and at the same time trying to run the queries from #2.

[CreganTur]

where button # 1 is to open up a select query and seek user input as to the criteria/filter.

I noticed that you're actually opening the query in design view for the user. If this user is not you, then this is probably not a good idea, since it means that your user could very easily screw up the entire design of that query.

A better option would be to restrict the user's options by either having a combobox, or set of comboboxes that contain static options for the criteria/filter. Or you could use textboxes with a lot of data-validation behind them.

Doing this means that you could combine all of your queries into a single button.


If I combine button #1 and #2 into in, query 1 opens in design and at the same time trying to run the queries from #2.

Using the above would mean that you would have no need to open the query in design view.

Now, you would need to hardcode your SQL statement for the Query in the above #1 into your VBA procedure- the reason for this is so that you can have variables interact with the SQL- and the value of the variables would be the input from the comboboxes or textboxes.

Whenever you put VBA variables into a SQL string you'll need to format them a special way, otherwise they won't work. Here's an example- this first shows a regular SQL string:

SELECT tblName.CustName, tblName.LoanDate FROM tblName
WHERE (((tblName.AcctNmbr)=AcctNmbr ));

Now, let's say that you want to use variables for the fields CustName, LoanDate, and AcctNmbr. The SQL string would look like this in VBA:

SELECT tblName.'" & CustName & "', tblName. #" & LoanDate & "# FROM tblName
WHERE (((tblName.AcctNmbr)='" & AcctNmbr & "'));

As you can see you need to wrap variables- for string data type variables you need to use single quote, then double quote. For Date data types, you ned to use the pound sign.

HTH