Insert All

[proballin]

What would be the best way to do the following scenario:

I have a form where users can select continents. Another combo box is autofilled to show the respective countries. In that same combo box there is also an "All" option. So for North America the combo box would be filled with U.S., Mexico, Canada, and All. There are many other text/combo boxes that the user fills in and then finally a table is update with the record.

I want to be able to insert into a table identical records for U.S., Mexico and Canada if the user selects "All" in the countries combo box.

 

[RuralGuy]

How do you create or insert records currently? What code are you using?

 

[proballin]

I insert records using SQL in both VB and the Access queries. An example of a VB insert is:

CurrentDb.Execute "INSERT INTO Market_Table (Region, SubRegion, Segment, Sector, Product, Market_Size, Percentage, Volume, Ingredient)VALUES ('" & Me.Region_Combo.Column(1) & "', '" & Me.SubRegion_Combo & "', '" & Me.Segment_Combo.Column(1) & "', '" & Me.Sector_Combo.Column(1) & "', '" & Me.Product_Combo & "','" & Me.MkSz & "','" & Me.Percentage_Text1 & "', '" & Share & "','" & Me.Ingredient_Combo1 & "')"

 

[proballin]

Also I get "(All)" to appear in my combo box for the choices by doing:

SELECT SubRegion_Table.SubRegion FROM SubRegion_Table WHERE (((SubRegion_Table.Region_ID)=Forms!Formulation_Combo!Region_Combo3)) UNION SELECT "(All)" FROM SubRegion_Table WHERE((SubRegion_Table.Region_ID)=Forms!Formulation_Combo!Region_Combo3);

However when I select "All" it just puts the word "All" in the table for whatever selections I made as opposed to putting all the resepective subregions in the table with the same selections.

 

[RuralGuy]

Understood. You will need to test for the (All) in the ComboBox and change the INSERT query accordingly.

 

[proballin]

Lol...that is where the problem comes in Rural...I am not sure of the best way to change the query. Do you have any suggestions?

 

[RuralGuy]

Sorry I have not responded sooner but the reply emails from this site fail to get to me often. How many elements of the INSERT query in post #3 would need to be changed?

 

[proballin]

Yeah the site is messing up a bit.

All of the elements would remain constant in the insert query from post #3 except the SubRegion. So we would apply all of those criteria for each SubRegion.

Example:

If they selected:
Region:North America
SubRegion:All
Segment:Food
Sector:Candy
Product:M&Ms
and so on... the result in the table should be:

NA/U.S/Food/Candy/M&Ms
NA/Canada/Food/Candy/M&Ms
NA/Mexico/Food/Candy/M&Ms

Is that clear?

 

[RuralGuy]

If I understand correctly then we should walk through the RowSource of the SubRegion_Combo creating a new record for each record in the combo, right? It is not just North America, right?

 

[proballin]

No it is just North America...we should walk through and create a new record for each SubRegion in North America only. There are SubRegions for Africa, Asia and so on...therefore we only do "All" for the selected Region. In the example its North America. When we choose North America in the combo box, the SubRegion Combo is loaded ONLY with "All", "US", "Canada", and "Mexico". We want a new record for US, Canada, and Mexico when "All" is the selection. I hope I am being a bit clearer

 

[RuralGuy]

So that we can be generic and allow for the future, what is the RowSourceType of the SubRegion_Combo ComboBox and what is the RowSource?

 

[RuralGuy]

Right now I am not getting *any* emails from this forum so I have to keep checking all of my subscribed threads. What a pain!

 

[proballin]

Yeah I dont know what happened to them sending emails...I just sit and click refresh lol.

The RowSource is posted in the post #4 and its a Table/Query.

 

[RuralGuy]

This is <<< AIR CODE >>> but should convey the idea:

   Dim i As Integer
   Dim MySQL As String

   For i = 1 To Me.SubRegion_Combo.ListCount - 1
      MySQL = "INSERT INTO Market_Table " & _
              "(Region, SubRegion, Segment, Sector, Product, " & _
              "Market_Size, Percentage, Volume, Ingredient)VALUES " & _
              "(  '" & Me.Region_Combo.Column(1) & _
              "', '" & [COLOR="Red"]Me.SubRegion_Combo.Column(1, i)[/COLOR] & _
              "', '" & Me.Segment_Combo.Column(1) & _
              "', '" & Me.Sector_Combo.Column(1) & _
              "', '" & Me.Product_Combo & _
              "', '" & Me.MkSz & _
              "', '" & Me.Percentage_Text1 & _
              "', '" & Share & _
              "', '" & Me.Ingredient_Combo1 & "')"
      CurrentDb.Execute MySQL, dbFailOnError
   Next i

This code assumes that the "(All)" is the first item in the ComboBox and skips it.

 

[proballin]

Alright RG...how much is your consultant fee? lol...

IT WORKED THANKS!!!!!!

 

[RuralGuy]

My usual fee! Glad it worked as you wanted.