VBA For Each Loop Help

[accvbalearner]

Hello All,

I'm hoping someone out there can point me in the right direction. I don't know exactly what question to ask, because I don't know the correct terminology to use......I Think! So I will explain a little bit and may be someone can figure out why I mean!

Ok, I have an Access Form that has User Entered (or select from a Calendar) box, two Comboboxes and two multi-select list boxes and a User Entered box for a number.

What I want to have happen: User opens the Form, enters the date, selects something from the two comboboxes, selects what ever they want in the two multi-select list boxes and enter a number. Then the User clicks a button, and what they entered and selected is passed into a table, with the date entered, the combobox selections, the number entered and the multi-select choices.

For example:
User Enters today 10-Feb-20
Combo1: BR549
Combo2: CompanyA
List1 selects: Bob, Pam, Tina
List2 selects: Miami, Orlando, Atlanta, Jackson
Number Entered: 1

Table Result

ID	Date	Combo1	Combo2	List1	List2	Number
1	10-Feb-20	BR549	CompanyA	Bob	Miami	1
2	10-Feb-20	BR549	CompanyA	Bob	Orlando	1
3	10-Feb-20	BR549	CompanyA	Bob	Atlanta	1
4	10-Feb-20	BR549	CompanyA	Bob	Jackson	1
5	10-Feb-20	BR549	CompanyA	Pam	Miami	1
6	10-Feb-20	BR549	CompanyA	Pam	Orlando	1
7	10-Feb-20	BR549	CompanyA	Pam	Atlanta	1
8	10-Feb-20	BR549	CompanyA	Pam	Jackson	1
9	10-Feb-20	BR549	CompanyA	Tina	Miami	1
10	10-Feb-20	BR549	CompanyA	Tina	Orlando	1
11	10-Feb-20	BR549	CompanyA	Tina	Atlanta	1
12	10-Feb-20	BR549	CompanyA	Tina	Jackson	1

I have a form that already does this with ONE Multi-select Listbox, but not two, and being a Newbie I don't understand how to incorporate a For Next inside of the For Next, or even if I need to. Here is the code I'm using:

Private Sub btn_AddWONbrs_Click()
' This button will add selected Work Order Numbers to a table
' alongwith the selected Job Number and Report Number.

Dim db As Database
Dim rs As Recordset
Dim strsel_JNbr As String
Dim strsel_RNbr As String

Dim var_WONbr As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("RNWONbrs", dbOpenDynaset, dbSeeChanges)

strsel_JNbr = Inclsel_JNbr()
strsel_RNbr = Inclsel_RNbr()

For Each var_WONbr In sel_WONbr.ItemsSelected()
     rs.AddNew
     rs!WONbr = sel_WONbr.ItemData(var_WONbr)
     rs!JobNbr = strsel_JNbr
     rs!RepNbr = strsel_RNbr
     rs.Update
Next

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

I hope what I'm asking is coming through and that it isn't as clear as MUD!

Thanks in advance for any help one of you Wizards out there can provide!

Take Care,
accvbalearner

 

[NauticalGent]

Hello there,

Without knowing anything more, I would suggest you convert your list boxes to combo boxes and simply use a bound form in Data Entry mode.

 

[namliam]

You need to drasticaly rethink your design, this sounds like flight information and a family traveling that route of cities.

Rethink your design, learn about "normalization" here there and everywhere... Comeback with questions about that if you need, but normalizing your data will save your behind in the future.

 

[jdraw]

Agree with namliam -- some business context in plain English would be helpful.

Can you translate this into simple English?

For example:
User Enters today 10-Feb-20
Combo1: BR549
Combo2: CompanyA
List1 selects: Bob, Pam, Tina
List2 selects: Miami, Orlando, Atlanta, Jackson
Number Entered: 1

 

[theDBguy]

Hi. You might also consider looking at using a Cartesian Query. Cheers!

 

[accvbalearner]

Hello All,

Thanks for all of your comments, and I guess I need to clear up the muddy water! I was in a rush at the time, I'll try to be better with the explanation.

This is part of database that is accumulating/tracking progress of a set of tasks over many different projects. At present, I have a form setup where the user can select the job, select the tag, select the type of progress based on the previous two selections, enter the data and enter the percent complete. This works great but is incredibly slow for entry because it can only handle one tag number, one single type of progress, one single progress value, each time the value is entered the form clears, then to enter a second line of progress against the exact same line, the user has to enter everything all over again. It doesn't sound too bad as written above, but when there are 25-30 different projects, each job has anywhere from 500-30,000 tags, the progress available for a tag is variable depending on what type of work it takes to complete it, it basically takes forever to enter on projects progress for a period. As the job increases in Tagnbr quantity I put in additional comboboxes to help the User drill down to make the TagNbr List Shorter, but that just leads to more boxes to select from.

What I am trying to setup is a bulk entry method where similar tags, having similar earned values in the same project can be selected and with the click of a button the progress can be captured and entered for all of them selected at the same time. This will be especially helpful for smaller projects or rapid paced projects.

All of the data is already normalized. I'll try to give an example using the tables involved.
For this situation there are four tables:

1. Table1 - List of JobNbrs
2. Table2 - List of Progress Type Groups
   1. Each Individual Progress Type Group can have anywhere from 1 to as many as 27 different milestone values
3. Table3 - List of TagNbrs to Complete
   1. Contains JobNbr, Progress Type Group, TagNbr
4. Table4 - List of Progress Earned
   1. Contains DateEarned, JobNbr, Progress Type Group, TagNbr, Progress Milestone, Progress Earned

So having listed those tables I'm trying to make the form do this:

1. Box1 - User Enter/Select Date
2. Box2 - Combobox - User Select Job
3. Box3 - Combobox - User Select Progress Type
4. Box4 - Multiselect Listbox - Based on Selection in Boxes 2 & 3, give the list of Progress Milestones to select from.
5. Box5 - Multiselect Listbox - Based on Selection in Boxes 2 & 3, give the list of TagNbrs available for Progress
6. Box6 - User Enter Progress

Ok, the hard part:

1. Box1 - User selects today
2. Box2 - User Selects Job1
3. Box3 - User Selects Progress Type called 'STD'
4. Box4 - User Gets list of Milestones to Receive, Inspect, Measure, Fitup, Install
   1. User selects Receive, Inspect, Measure
5. Box5 - User Gets list of Tags Tag1, Tag2, Tag3, Tag67, TagAB1
   1. User select Tag1, Tag3, Tag67
6. Box6 - User Enters 100%

Now when the button is clicked I want the Progress Table to Receive:

ID	Date	JobNbr	ProgTypeGrp	ProgMStone	TagNbr	EarnValue
1	10-Feb-20	Job1	STD	Recieve	Tag1	100%
2	10-Feb-20	Job1	STD	Inspect	Tag1	100%
3	10-Feb-20	Job1	STD	Measure	Tag1	100%
4	10-Feb-20	Job1	STD	Recieve	Tag1	100%
5	10-Feb-20	Job1	STD	Inspect	Tag1	100%
6	10-Feb-20	Job1	STD	Measure	Tag1	100%
7	10-Feb-20	Job1	STD	Recieve	Tag1	100%
8	10-Feb-20	Job1	STD	Inspect	Tag1	100%
9	10-Feb-20	Job1	STD	Measure	Tag1	100%

 

[accvbalearner]

To theDBguy

Sorry, I think I replied to myself instead of the one of the folks trying to help me!

Hope the info below is helpful.

Take Care,
accvbalearner

Thanks for all of your comments, and I guess I need to clear up the muddy water! I was in a rush at the time, I'll try to be better with the explanation.

This is part of database that is accumulating/tracking progress of a set of tasks over many different projects. At present, I have a form setup where the user can select the job, select the tag, select the type of progress based on the previous two selections, enter the data and enter the percent complete. This works great but is incredibly slow for entry because it can only handle one tag number, one single type of progress, one single progress value, each time the value is entered the form clears, then to enter a second line of progress against the exact same line, the user has to enter everything all over again. It doesn't sound too bad as written above, but when there are 25-30 different projects, each job has anywhere from 500-30,000 tags, the progress available for a tag is variable depending on what type of work it takes to complete it, it basically takes forever to enter on projects progress for a period. As the job increases in Tagnbr quantity I put in additional comboboxes to help the User drill down to make the TagNbr List Shorter, but that just leads to more boxes to select from.

What I am trying to setup is a bulk entry method where similar tags, having similar earned values in the same project can be selected and with the click of a button the progress can be captured and entered for all of them selected at the same time. This will be especially helpful for smaller projects or rapid paced projects.

All of the data is already normalized. I'll try to give an example using the tables involved.
For this situation there are four tables:

1. Table1 - List of JobNbrs
2. Table2 - List of Progress Type Groups
   1. Each Individual Progress Type Group can have anywhere from 1 to as many as 27 different milestone values
3. Table3 - List of TagNbrs to Complete
   1. Contains JobNbr, Progress Type Group, TagNbr
4. Table4 - List of Progress Earned
   1. Contains DateEarned, JobNbr, Progress Type Group, TagNbr, Progress Milestone, Progress Earned

So having listed those tables I'm trying to make the form do this:

1. Box1 - User Enter/Select Date
2. Box2 - Combobox - User Select Job
3. Box3 - Combobox - User Select Progress Type
4. Box4 - Multiselect Listbox - Based on Selection in Boxes 2 & 3, give the list of Progress Milestones to select from.
5. Box5 - Multiselect Listbox - Based on Selection in Boxes 2 & 3, give the list of TagNbrs available for Progress
6. Box6 - User Enter Progress

Ok, the hard part:

1. Box1 - User selects today
2. Box2 - User Selects Job1
3. Box3 - User Selects Progress Type called 'STD'
4. Box4 - User Gets list of Milestones to Receive, Inspect, Measure, Fitup, Install
   1. User selects Receive, Inspect, Measure
5. Box5 - User Gets list of Tags Tag1, Tag2, Tag3, Tag67, TagAB1
   1. User select Tag1, Tag3, Tag67
6. Box6 - User Enters 100%

Now when the button is clicked I want the Progress Table to Receive:

ID	Date	JobNbr	ProgTypeGrp	ProgMStone	TagNbr	EarnValue
1	10-Feb-20	Job1	STD	Recieve	Tag1	100%
2	10-Feb-20	Job1	STD	Inspect	Tag1	100%
3	10-Feb-20	Job1	STD	Measure	Tag1	100%
4	10-Feb-20	Job1	STD	Recieve	Tag1	100%
5	10-Feb-20	Job1	STD	Inspect	Tag1	100%
6	10-Feb-20	Job1	STD	Measure	Tag1	100%
7	10-Feb-20	Job1	STD	Recieve	Tag1	100%
8	10-Feb-20	Job1	STD	Inspect	Tag1	100%
9	10-Feb-20	Job1	STD	Measure	Tag1	100%

 

[Cronk]

You're trying to construct a spreadsheet in Access. In a relational database, you don't repeat, in your case, JobNmbr and JobDate for each Tag value. Tags would be stored in a separate table, viz

tblJobs
JobID
JobDate
JobProgTypeGrp

tblJobTags
JobTagID
JobID
TagNbr
TagValue

The tables would be related on JobID. User would enter data using a form and sub form with the form bound to tblJobs and the sub form bound to tblJobTags, with the link being JobID.

You probably need to do the same with ProgMStone but there's not enough information provided to understand the process you want.

 

[namliam]

You need to drasticaly rethink your design, this sounds like flight information and a family traveling that route of cities.

Rethink your design, learn about "normalization" here there and everywhere... Comeback with questions about that if you need, but normalizing your data will save your behind in the future.

Just going to quote myself for repetition.