Querying tables from the code

[abhiutd]

Hi,
I have a button click event and i should be able to run a query command on multiple tables using an id( read from the form) and get the returned data and use it for certain purpose. How do i do it from the code.

Thank You.

 

[missinglinq]

I'm not sure, from reading your post, exactly what you're trying to do, but you can't use a Select Query in VBA code to assign a value to a textbox on a form, or anything like that. Usually for this kind of thing you'd use the DLookUp() function. Take a look in Access Help and see if it'll suit your needs.

 

[abhiutd]

I'm not sure, from reading your post, exactly what you're trying to do, but you can't use a Select Query in VBA code to assign a value to a textbox on a form, or anything like that. Usually for this kind of thing you'd use the DLookUp() function. Take a look in Access Help and see if it'll suit your needs.

Hi,
Isn't DLookup() very slow and expensive method. How is Docmd.RunSQL or CurrentDb.OpenRecordset method? Can you please shed some light on that?

Thank You.

 

[namliam]

Yes you should probably use the Openrecordset method...
But the help can explain its usage much better / faster than we can on the forum.

Check out the help, see how far you get and post back here with any problems you run into.

 

[missinglinq]

Isn't DLookup() very slow and expensive method.

You know, you're always seeing someone saying this in forums, usually people with no real knowledge of the facts, but simply parroting what they've seen someone else write. After years of using DLookUp() without any noticeable loss of performance, I finally asked one of the top Access developers in the world about it. He rather sheepishly said that it did in fact make a difference, but only if you were dealing with recordsets that held a couple of million records!

People tend to learn something and commit it to memory and never adjust it to new realities. When I started out twenty years ago, with a PC with 20mb hard drive, writing tight code was all the rage, by necessity. Reducing two lines of code to one saved important space! Today, with hard drive being measured in gigs, I'd rather write 5 lines of code than 1 line, if it makes the intent of the code clearer.

Likewise, CPU speed has increased so dramatically that things we used to worry about don't really matter any more. From time to time you'll see questions, here and elsewhere, about throwing up a form saying "Please wait while records load!" where the form closes after the records have loaded. The complaint is that the form never appears. The truth, however, is that the form has appeared, as programmed, and closed, as programmed, after the records have loaded. This all has simply happened so fast that the human eye couldn't register the appearance of the form!

Sure, I'd open a recordset rather than use 10 DLookUps against a single table. But if I was looking up one field in ten tables? Would I go to the trouble of opening and looping thru 10 recordsets? No, I'd use 10 DLookUps.

 

[abhiutd]

You know, you're always seeing someone saying this in forums, usually people with no real knowledge of the facts, but simply parroting what they've seen someone else write. After years of using DLookUp() without any noticeable loss of performance, I finally asked one of the top Access developers in the world about it. He rather sheepishly said that it did in fact make a difference, but only if you were dealing with recordsets that held a couple of million records!

People tend to learn something and commit it to memory and never adjust it to new realities. When I started out twenty years ago, with a PC with 20mb hard drive, writing tight code was all the rage, by necessity. Reducing two lines of code to one saved important space! Today, with hard drive being measured in gigs, I'd rather write 5 lines of code than 1 line, if it makes the intent of the code clearer.

Likewise, CPU speed has increased so dramatically that things we used to worry about don't really matter any more. From time to time you'll see questions, here and elsewhere, about throwing up a form saying "Please wait while records load!" where the form closes after the records have loaded. The complaint is that the form never appears. The truth, however, is that the form has appeared, as programmed, and closed, as programmed, after the records have loaded. This all has simply happened so fast that the human eye couldn't register the appearance of the form!

Sure, I'd open a recordset rather than use 10 DLookUps against a single table. But if I was looking up one field in ten tables? Would I go to the trouble of opening and looping thru 10 recordsets? No, I'd use 10 DLookUps.

Well it sounds you have extensive experience and i'm a novice ofcourse. But i did use DlookUp function only once for like table having 10 records and i could see the lag (i had 2 inner joins in the query though).
Okay, i am using OpenRecordSet method and in my case it returns few records. Now i should be able to iterate through all the rows. Can you or anyone suggest a way how to go about it?
I tried using a variant to get the results from recordset GetRows() method but i was wondering if i should use an Array instead. I'm confused. Please help!

Thank You again for all the comments and help.

 

[namliam]

Sure, I'd open a recordset rather than use 10 DLookUps against a single table. But if I was looking up one field in ten tables? Would I go to the trouble of opening and looping thru 10 recordsets? No, I'd use 10 DLookUps.

This is EXACTLY the point!! DLookups SUCK HARD for your average user, IF you know EXACTLY what you are doing yes... usefull... Otherwize STAY AWAY from it

People will use 10 DLookups for 10 seperate fields on 1 table, which anyone can confirm is MUCH slower than doing one recordset query.
Even if you need 1 field from table1 and 1 field from table 3 and you need to use Table2 as a junction table... Using DLookup is not the best way to do it...
A recordset would be if not faster, more maintainable and more constant.

 

[namliam]

Can you or anyone suggest a way how to go about it?

That all depends exactly on what you need to do...

Have a rowsource on the form

Need to loop it?
i.e. rst.Movenext will help you in a "Do while not rst.eof ... loop" thing

Need totals?
A group by query will do that for you.

Etc etc.

 

[abhiutd]

That all depends exactly on what you need to do...

Have a rowsource on the form

Need to loop it?
i.e. rst.Movenext will help you in a "Do while not rst.eof ... loop" thing

Need totals?
A group by query will do that for you.

Etc etc.

Hi,
Thanks for your reply but let me try to explain exactly what i'm trying to do. I have a form and a subform in it. The subform shows data in a DataSheet format. There is a button on the main form on whose click event i should be able to iterate through each row in DataSheet and grab a column value, do a SELECT query to get a value and do something.
I think my main problem is to how iterate throght the records of the subform.

Any help would be greatly appreciated.

Thank You.

 

[dkinley]

Butting in for clarification - not hijacking =]

So instead of manually ...

1. Scrolling down the datasheet
2. Selecting (highlighting) the data in a specific column, and
3. Copying it
4. Paste it or load it into a variable

You want to click a button on the main form (that holds the subform) to do this for you?

-dK

 

[abhiutd]

Butting in for clarification - not hijacking =]

So instead of manually ...

1. Scrolling down the datasheet
2. Selecting (highlighting) the data in a specific column, and
3. Copying it
4. Paste it or load it into a variable

You want to click a button on the main form (that holds the subform) to do this for you?

-dK

There is a column field containing an ID. I need to query to get Email address corresponding to the ID in each row in the DataSheet and then send an email. So i guess i would have to iterate, am i missing anything?

Thank You.

 

[namliam]

Something like: (Note this is air code)

Dim rst as dao.recordset
set rst = me.subformname.form.recordsetclone 'Or something like that
rst.movefirst
Do while not rst.eof
    ' "get a value and do something" 
    rst.movenext
loop
rst.close
set rst = nothing

 

[abhiutd]

Something like: (Note this is air code)

Dim rst as dao.recordset
set rst = me.subformname.form.recordsetclone 'Or something like that
rst.movefirst
Do while not rst.eof
    ' "get a value and do something" 
    rst.movenext
loop
rst.close
set rst = nothing

Thanks man. This was a lot of help. I'm pretty new to VB (i've been coding in C#,C++) so please excuse my basic questions. I have a combobox as one of the columns and when i do rst.Fields("Name").Value it gives me the value like 1,2 etc but not the visible text of the combobox. I tried rst.Fields("Name").VisibleValue but says "operation not supported for this object". Any ideas?

Thank You again.

 

[namliam]

you are getting the bound value of the combo box...
To get the displayed value... You cannot... not in the recordset... not THIS recordset.

If you have a 'proper' combobox you have both the visible values and bound values stored in a table that is beeing used for displaying the combobox.
Join this table to your table and your done... Except you will have to make it a real query instead of fetching the recordset of the form... which is slightly slower....