• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Looping using values in tables with VBA

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
Shanan212
Asked:
Shanan212
  • 3
1 Solution
 
Shanan212Author Commented:
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
 
shaydieCommented:
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
 
Shanan212Author Commented:
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
 
Shanan212Author Commented:
My solution is accepted as correct
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now