loop through a table (vba)

[lala]

how do i loop through a table and get the data from each row so that i can use it for something else?

there's 2 columns from each row that i need and it's less than 100 rows

thank you very much!!

 

[boblarson]

That all depends on what you need to do with it. Can you give a more thorough and actual explanation?

 

[dcb]

Dim sql as string
Dim data1, data2 as string ''Undefined
Dim db as database
Dim rst as Recordset

sql = "Select * from undefinedTable;"

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

data1 = rst("NAMEDCOLUMN")
data2 = rst("NamedColumn")

''DO STUFF WITH THE DATA

rst.MoveNext
Loop

 

[boblarson]

Dim sql as string
Dim data1, data2 as string ''Undefined
Dim db as database
Dim rst as Recordset

sql = "Select * from undefinedTable;"

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

data1 = rst("NAMEDCOLUMN")
data2 = rst("NamedColumn")

''DO STUFF WITH THE DATA

rst.MoveNext
Loop

dcb -

Make sure you are explicit with your variables -

Dim rst As Recordset

should be

Dim rst As DAO.Recordset

because if you don't, many times you will get an error because there are a lot of databases out there which have DAO and ADO selected in the references and Access likes to know which one you want (event though it should be able to figure it out from the syntax you are using in the instantiation).

 

[lala]

Sure, thank you
Every week I need to create spreadheets with data for different people

I automated it and do it in access then export to excel
There's 60 people, but the people change sometimes, so its not a set list

So the table is a list of the people for the week and I need to be able to go to the table, read the first record, plug it in as criteria for the query, append the data to my table, then go to the second record, plug it in and so on
And I need to do that for all the records in that table

Is this better or am I still not explaining it good?

 

[dcb]

And AGAIN I learn
I think I should send you money for the personal training today
Will PM you

 

[lala]

Dcb thank you very much!!!!!
I'm also self taught and I always wish there was a person at my job who I can ask the million questions I have
But noone knows access here

One thing, if you have the time to explain
If not I totally understand

When you declare the recordset and the database and stuff, what is that for
What does it do?
In some pieces of code I see people do it and in others I don't

Again thank you so much for the code

 

[boblarson]

I would suggest you go through these VBA tutorials.

 

[lala]

thank you
this is made for little kids, i should be able to understand

i tried reading up on it, i don't just run here right away without trying to solve my problem myself for a few hours or days
but i didn't get it, English is not my first language and that was probably part of it

but on this site they seem to explain it pretty good

 

[boblarson]

...but on this site they seem to explain it pretty good

I've found it to be one of the best sites for tutorials.

By the way, what is your native language?

 

[dcb]

Well the reason I have learnt to use them is I have a freind who codes C# - It is a very structured language and I have picked up (what I believe) are a few good tricks from it...

The link Bob has given is great by the looks of it!

 

[lala]

it works!!!!!!!!!!!!!!!!


thank you, thank you, thank you)))))))))))))))))))))))))))))

 

[lala]

I've found it to be one of the best sites for tutorials.

By the way, what is your native language?

Russian
i came here when i was 15, i'm 35 now, so my english is good, but only when it comes to talking and stuff
reading specialized literature is a bit harder


but thank god this forum exists, you guys are amazing!!!
and you Bob, ive seen you here for years, and read a lot of your posts
is there anything you don't know about access?)))))))))))))

 

[lala]

Well the reason I have learnt to use them is I have a freind who codes C# - It is a very structured language and I have picked up (what I believe) are a few good tricks from it...

The link Bob has given is great by the looks of it!

i tried learning it once - no way))))))))))
but maybe it was just the way i did it, reading the book on C# on the way home from work

access is another story for me, i started designing databases 10 years ago, and i'm in love with it
i stay up nights trying to do things and i enjoy it so much
my job is like a hobby for me

i don't know what it is about access that i love so much
but i tried learning other languages and they didn't go


but if i try again, what would you say i should try to learn?
what's being used these days?
another thing with Access is that not too many companies use it

 

[boblarson]

Russian

Ah, well I definitely can't help in THAT realm

i came here when i was 15, i'm 35 now, so my english is good, but only when it comes to talking and stuff
reading specialized literature is a bit harder

Well, I think you're doing great for having to deal with such complex tasks in another language. I don't know that I would do as well having to do that the other way around.

...is there anything you don't know about access?)))))))))))))

LOTS I don't know. I've been working with it for almost 13 years now and I still find I have things to learn and get corrected and confused by other, more technical, users. So, I guess it is all relative.

 

[lala]

oops, one problem
how do i make it stop?
it keeps looping))))))))))))))

 

[lala]

LOTS I don't know. I've been working with it for almost 13 years now and I still find I have things to learn and get corrected and confused by other, more technical, users. So, I guess it is all relative.

maybe it is relative, but then it's a little scary
if you feel like there's LOTS you don't know, then how much more is there to know?
to me you're the guru in access


i do understand your feeling in a way, though, at my job i'm considered the access master, but i myself realize that i'm far from it


but what exactly do you think you don't know yet?
can you give me an example of something you cant do?

 

[dcb]

oops, one problem
how do i make it stop?
it keeps looping))))))))))))))

Umm
The rst.MoveNext should have got it to the EOF?
Press break to stop it - and then debug

 

[lala]

... I still find I have things to learn and get corrected and confused by other, more technical, users. So, I guess it is all relative.

you're lucky you even know someone who's good in Access, and you're double lucky that you consider them being better than you at it. think how much you can learn from them!!!!

i wish i had someone here who knew more than me in Access, i'd be so happy
i love having smart people around, so much I can learn

and in general i like watching people who are good at something (doesn't matter what) do their job

 

[lala]

Umm
The rst.MoveNext should have got it to the EOF?
Press break to stop it - and then debug

i got out of it already, and i also thought that EOF would make it stop, even though i didn't understand exactly how

i don't know why i posted this problem about looping nonstop, this is the kind of thing id usually research and fix myself


thank you so much for the code, it works perfect, just what i needed