Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Looping using values in tables with VBA

Posted on 2011-09-20
4
Medium Priority
?
171 Views
Last Modified: 2012-05-12
Hi there,

I have a function which adds 2 tables

1. Table 1 has say "Cars & prices"
2. Table 2 has "provinces & taxes"

I had to add one of the car with all of the info in table 2

table sorta looked like this

car1        Province1       values
car1      Province2      values
car1      Province3      values

- The above addition had criterias on province table (such that if car is this, then this, etc)

Now I have to add certain cars to all provinces


The table would look like

car1        Province1       values
      Province2      values
      Province3      values
car2        Province1       values
      Province2      values
      Province3      values
car3        Province1       values
      Province2      values
      Province3      values


The function I have takes care of the additions based on criteria (as I said before)

The problem is that I have to loop each car to run through the function such that

1st time loop function(car1 data set)
2nd time loop  function(car2 data set)
3rd time loop  function(car3 data set)

The functions can output via queries and apend to a table

Could you please tell me how to loop using a table of values? I only need to take 1 string from the loop-er table (car name)

Any direction is much appreciated!

The reason I cannot call this function multiple times is that the number of cars can be nearly 3370 and there are nearly 17 province per car.
0
Comment
Question by:Shanan212
  • 3
4 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 0 total points
ID: 36570204
Dim sql as string
Dim data1 as string, data2 as string 
Dim db as database
Dim rst As DAO.Recordset

sql = "Select * from originProvince;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)
Do Until rst.EOF

data1 = rst("citycombo")

func1(data1)

rst.MoveNext
Loop 

Open in new window


Instead of sitting pretty, I researched and changed some coding as above.

I haven't run this on my file yet but wants to know that 'loop' at the end is sufficiant to loop from beginning to end?

If I am doing something with the 'data1', then can I call a function called 'func1(data1)' like I did?

Thanks!
0
 
LVL 7

Expert Comment

by:shaydie
ID: 36570239
Yes, the loop will loop through all records in the recordset. You can call a function like you are, depending on what you are doing with the function it looks like that should work for what you want to do.

What exactly does func1 do?
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36571384
function1 see if the value is a type of string:

Eg:

"Beyond" or "Local"

Then output tables through 4 queries. This would repeat for each string taken from the loop-er table.

The tables would all be apended to a single table.
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36597750
My solution is accepted as correct
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

877 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