Solved

Need help with automating batch code (selection of listbox values)

Posted on 2014-11-21
20
192 Views
Last Modified: 2014-11-24
Experts:

I need some assistance with creating a procedure (aka batch process).

Please see attached database (zipped).  In contains the following objects:

1. Table1 and Table2
2. Query1 and Query2
3. frmLogin

Process upon opening:
1. Open form "frmLogin"
2. Select any of the four values from the listbox
3. This will open Query1 and display the associated responses

Note: Upon opening frmLogin, all existing records in Table2 will be dropped.  Then, for each of the selected list box values, append-query 'Query2' will add each of the four records to Table2.  So, if "Report01", "Report02", and "Report03" were selected from the listbox, Table2 will show 12 records.  

Here's what I need some help with:
- While this sample database only includes four sample options in the listbox, the actual application has up to 25 reports.  
- Sometimes, I may only want to run, e.g., 3 out of the 25 reports.
- Othertimes, I may need to run all 25 reports.  In the event I need to run all 25 reports, I'd rather click on an option "Run all 25 reports" vs. having to click on each of them individually in order to have the total # of records added to Table2.

My question:
- How can I tweak the VBA that would automate the "manual clicking/selecting" of all -- in this case -- four reports from the listbox (thus, 16 records would be added -- in the background -- to Table2)?

Thanks,
EEH
AutomationProcess.zip
0
Comment
Question by:ExpExchHelp
  • 10
  • 10
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458110
test this

you also have to change the list box multi select property to simple
AutomationProcess.zip
0
 

Author Comment

by:ExpExchHelp
ID: 40458145
Rey:

Thank you for the prompt response.   I appreciate it.

I've looked at the proposed solution... how does it work?  

- Again, upon opening the frmLogin, all records from Table2 are dropped.
- I opened frmLogin and click "Select All"... nothing happened.
- I then checked Table2... no records are listed.

What am I missing?

EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458154
ok. i just coded the command button to select all the items in the list box.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:ExpExchHelp
ID: 40458159
Rey:

Copy -- thanks... how can the VBA execute all of the select reports vs. having to click them individually.

Based on this process, users need to be able to either:
1. Select all... append query will add all records from Table1 to Table2.
2. Select records one by one... only the selected records will be appended to Table2.

Thanks,
EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458193
0
 

Author Comment

by:ExpExchHelp
ID: 40458300
Rey:

I checked out the proposed solution.   Something is still missing.

Prior to this solution, I would do the following:
- Click "Report 01"... opens query1 with "Report 01"... and all records are appended to Table2
- Click "Report 02"... opens query1 with "Report 02"... and all records are appended to Table2
- Click "Report 03"... opens query1 with "Report 03"... and all records are appended to Table2
- Click "Report 04"... opens query1 with "Report 04"... and all records are appended to Table2

Now, I notice the following:
- I click on "Select All"
- Query 1 opens... with apparently all records from "Report4...  based on the "Select All", I wouldn't need the query to open, but it's ok if it does.
- What's not working is that only "Report 04" records have been appended to Table2... instead, I should see all 16 records.

Makes sense?
EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458318
ok... hang on
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458329
here test this
AutomationProcess.zip
0
 

Author Comment

by:ExpExchHelp
ID: 40458448
Rey:

One major step further... thank you!   Quick follow-up though.

While I can successfully execute all four (and eventual up to 25) reports at once, I cannot longer look at them individually.   For example, when clicking on, e.g., "Report 03", I now get the "Enter parameter value" dialog box.

Is there a way that I can do both?    1) Select All and 2) Select individual reports?

Thank you for your continued assistance?
EEH
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40458575
here try this
AutomationProcess.accdb
0
 

Author Comment

by:ExpExchHelp
ID: 40458597
Rey:

This version doesn't execute properly.

When clicking on "Select All", VBA code comes up... it stops at "Stop".   I'm not sure which lines should be commented out.

Private Sub Command31_Click()
        Dim j
       Dim qd1 As DAO.QueryDef, qd2 As DAO.QueryDef, db As DAO.Database, sql_1 As String, sql_2 As String
       Dim strGenericField As String
       Set db = CurrentDb
       Set qd1 = db.QueryDefs("Query1")
       Set qd2 = db.QueryDefs("Query2")
Stop
With Me.ListBoxTest
    For j = 0 To .ListCount - 1
        .Selected(j) = True
'    Next
'End With

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458636
oh sorry, remove line 8
0
 

Author Comment

by:ExpExchHelp
ID: 40458641
I tried that already... it won't result in any VBA errors but now Table2 is not populated any longer.

Does it work for you?   I'm sure you ran these two scenarios before posting the database.

Thanks,
EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40458711
check if your Table1 have records
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40458724
Rey -- thank you... another perfect solution!!   I appreciate your help on getting this VBA modification done.  ;)
0
 

Author Comment

by:ExpExchHelp
ID: 40462017
Rey:

Good morning... I'm currently integrating the test database into the actual solution.

I realized there's one element missing... when clicking on "Select All", the field "Selection" is not populated.   This value is important as it serves as input for a crosstab query.

I'm not really sure the associated selection values can be added given that no individual record is selected.   Right now, it only works when clicking on the reports one by one.

Any thoughts on this?

Thanks,
EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40462037
EEH,

can you copy the codes and paste here..
0
 

Author Comment

by:ExpExchHelp
ID: 40462064
Rey:

Thanks for the follow-up... the code is already included in the test database.

Basically, here's the process (old version... or when selecting a single report).

1. User opens frmLogin
2. User picks "Report 03"
3. Based on that selection value, the SQL code ['" & Me.ListBoxTest.Value & "' as Selection] passes "Report 03" into Table2.

Check out the difference in Table 2 when choosing "Select All" or clicking a single report.

Again, given that Table 2 will be used as an input for a Crosstab query, having the "Selection" value is important to distinguish between the reports.

Makes sense?

Thank you in advance,
EEH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40462094
for the Select ALL codes

change

'" & Me.ListBoxTest.Value & "'

with

'" & .itemdata(j) & "'
0
 

Author Comment

by:ExpExchHelp
ID: 40462109
Wow... amazingly easy!    Thank you for the additional feedback... it works great in the test apps.

Hopefully, the integration to actual db will work in the same way.   ;)

Again, thanks!
EEH
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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