By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

HowTo: for each record in query result do some VBA code ?

P: 12
Another question I've always asked myself:

Is there any possibility to do some sort of "for each record in query result A do some VBA code (e.g. call a VBA procedure)?

Best regards,
D.R.
Feb 21 '10 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Indeed.
  1. Open the QueryDef as a recordset in your code. I typically use the DAO option for this, but there is an ADODB version available too.
  2. Process through the recordset using MoveNext().
  3. Call your code within each iteration of your loop.
  4. Continue While Not .EOF or Until .EOF.
Feb 21 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
Depending on exactly what you require, there is also the option of calling a function (NOT subroutine. It must return a value) as part of the query itself. It would have to be defined as Public in a standard code module, but as well as determining a value for you, you could get it to go off and dance somewhere too.
Feb 21 '10 #3

P: 12
Wow, thanks again for your answer! I googled QueryDef and found enough information.

Question: Are there any (maybe hidden) problems, when using QueryDef with access tables connected to mysql (linked table).

Best regards,
D.R.

PS: There should be some kind of "thank you"-button, so anybody can see that you're giving very good and quick answers. Maybe you should become an Access MVP :)
Feb 22 '10 #4

P: 12
This time I hope I'm not bumping too early.

One question is still open:

Are there any (maybe hidden) problems, when using QueryDef with access tables connected to mysql (linked table)?


Best regards,
D.R.
Feb 23 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
@Poweruser
First let's look at the term QueryDef. Since you need to Google it, it must not be as obvious as I'd expected. QueryDef is simply the correct term for the queries that are saved in an Access database. Every saved query is a QueryDef. A Query is a looser term which covers QueryDefs, but also SQL strings called from within code in Recordsets etc.
@Poweruser
The bump is probably not too early, but as I haven't yet had a chance to look at your previous post, was probably unnecessary. Certainly not a problem. For your peace of mind though, remember that forum based web posts are picked up as and when by various different members. For instance, I may well take a few days off sometimes if I go away from home for any reason. A lack of response for many hours, or even a day or two, doesn't necessarily mean a lack of interest or intention, just that this has to fit in with other things.
@Poweruser
One thing to bear in mind, is that the fact that this is managed by a back-end server means that any permissioning done there will effect such queries. One user may have different rights from another, so it may work correctly for one but not the other. If you're using linked tables though (where the linking is managed outside of the QueryDef object), it should all work pretty smoothly.
@Poweruser
Very kind of you to say so, but from what I see of the Access MVPs, they contribute more than I do at a professional level. This is just a hobby for me. I enjoy it of course, but cannot devote enough time to it even to be considered for that.

On the thanking side, a little note of thanks, as you've done, is always well appreciated :) Aside from that, if you feel an answer (post) particularly hits the spot then you can select it as the best answer. It's always nice to see one of your posts has merited that.
Feb 23 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.