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


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)?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this

you also have to change the list box multi select property to simple
ExpExchHelpAnalystAuthor Commented:

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?

Rey Obrero (Capricorn1)Commented:
ok. i just coded the command button to select all the items in the list box.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

ExpExchHelpAnalystAuthor Commented:

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.

Rey Obrero (Capricorn1)Commented:
ExpExchHelpAnalystAuthor Commented:

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?
Rey Obrero (Capricorn1)Commented:
ok... hang on
Rey Obrero (Capricorn1)Commented:
here test this
ExpExchHelpAnalystAuthor Commented:

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?
Rey Obrero (Capricorn1)Commented:
here try this

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpExchHelpAnalystAuthor Commented:

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")
With Me.ListBoxTest
    For j = 0 To .ListCount - 1
        .Selected(j) = True
'    Next
'End With

Open in new window

Rey Obrero (Capricorn1)Commented:
oh sorry, remove line 8
ExpExchHelpAnalystAuthor Commented:
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.

Rey Obrero (Capricorn1)Commented:
check if your Table1 have records
ExpExchHelpAnalystAuthor Commented:
Rey -- thank you... another perfect solution!!   I appreciate your help on getting this VBA modification done.  ;)
ExpExchHelpAnalystAuthor Commented:

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?

Rey Obrero (Capricorn1)Commented:

can you copy the codes and paste here..
ExpExchHelpAnalystAuthor Commented:

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,
Rey Obrero (Capricorn1)Commented:
for the Select ALL codes


'" & Me.ListBoxTest.Value & "'


'" & .itemdata(j) & "'
ExpExchHelpAnalystAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.