?
Solved

Looping using values in tables with VBA

Posted on 2011-09-20
4
Medium Priority
?
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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