Solved

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

Posted on 2014-11-21
20
189 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 119

Expert Comment

by:Rey Obrero
Comment Utility
test this

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

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
ok. i just coded the command button to select all the items in the list box.
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
ok... hang on
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
here test this
AutomationProcess.zip
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
here try this
AutomationProcess.accdb
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
oh sorry, remove line 8
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
check if your Table1 have records
0
 

Author Closing Comment

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

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
EEH,

can you copy the codes and paste here..
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
for the Select ALL codes

change

'" & Me.ListBoxTest.Value & "'

with

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

Author Comment

by:ExpExchHelp
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now