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
Solved

Looping using values in tables with VBA

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

792 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