Solved

Looping using values in tables with VBA

Posted on 2011-09-20
4
163 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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