Solved

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

Posted on 2014-11-21
20
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

717 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