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

How do I reference the result of a vba select statement?

P: 6
I know this has to be a ridiculous question for most, but I am just learning ms access and find I need to write vba code for the main part of the application.
I also know that there is probably an easier way to do this but a person learns best from their mistakes. So please humor me.

Question: How do I reference the result of a select statement written in VBA?

Example: TempTable is a single-row table created with a Select Into statement with, say, 3 columns: Col1,Col2,Col3.

Dim strCol2 as String
strCol2 = "Select Col2 From TempTable"
DoCmd.RunSQL "strCol2"

Now, if this is correct, how do I reference the result in VBA. ie:programmatically.

Thank you in advance for your assistance,
JustAPawn
Dec 26 '07 #1
Share this Question
Share on Google+
10 Replies


Dököll
Expert 100+
P: 2,364
I know this has to be a ridiculous question for most, but I am just learning ms access and find I need to write vba code for the main part of the application.
I also know that there is probably an easier way to do this but a person learns best from their mistakes. So please humor me.

Question: How do I reference the result of a select statement written in VBA?

Example: TempTable is a single-row table created with a Select Into statement with, say, 3 columns: Col1,Col2,Col3.

Dim strCol2 as String
strCol2 = "Select Col2 From TempTable"
DoCmd.RunSQL "strCol2"

Now, if this is correct, how do I reference the result in VBA. ie:programmatically.

Thank you in advance for your assistance,
JustAPawn
Oh, I think you're doing fine, justapawn!

I can post the link to an update query for you and you can turn it to what you need it to do, how 'bout that!

http://www.thescripts.com/forum/thread743450.html

Will also send over to Access Forum for added support in VBA...

In a bit!

Dököll
Dec 27 '07 #2

P: 6
Thank you, Dököll, for your reply.. and your encouragement.
I've spent many hours reading through various forums and help sites in the last 3 months since I've started this project. I had never even opened the ms access program or experienced any db programming previously, but I accepted the project from my employer and I can't accept defeat.

I studied the thread you referred me to and, my apologies but I still cannot understand how to reference, or use, the result of the Select Query. ..Update, Select Into, Delete I understand. They change data to what you want. But Select seems to be out there in dark space. If I was referencing a text field or any control on a form I can use [Forms]![TheNameOfTheForm]![TheNameOfTheObject] but in referencing a field/cell in a table separate from a form or other control object - that is where I get confused. You select it with a query but then where does it go?

Dim strSTR as String
strSTR = DoCmd.RunSQL "Select Col2 From TableName"

... doesn't work.

Dim strSTR as String
strSTR = "Select Col2 From TableName"
DoCmd.RunSQL "strSTR"

...runs the Select Query but doesn't tell me the string/data that is in that field/cell. I am just using that result for an "If Then ElseIf" condition but I need to be able to reference it to use it.

If [Forms]! .. works why doesn't strSTR = [Tables]![NameOfTable]![Criteria]

How do I assign the result of the select query to a string?


Sorry If I'm sounding desperate. I think part of my brain snapped when I realized that to really program using ms access I was required to learn three languages at the same time (ms access - visual basic for applications - structured query language..) all sitting on top of the other and each with enough similarities that the syntax and methods get really mixed up.

Again, sincerely, thank you for your reply and any help or direction you give.
JustAPawn
Dec 27 '07 #3

Minion
Expert 100+
P: 108
I've run into similar problems when I first started the massive project my work dropped on me. It's tricky when you're learning and trying to figure out how to do something that should be relatively simple.

If I'm understanding your plight correctly you need to be able to return the string results of a SELECT query programically. Below I have included a piece of sample code using a method that I currently use. There might be a quicker or more professional way to do this, but I haven't learned it yet.

Expand|Select|Wrap|Line Numbers
  1. Dim rst as Recordset
  2. Dim qDef as QueryDef
  3.  
  4. 'Initialize the variables here. 
  5. set qDef = CurrentDb.CreateQueryDef("")
  6. qDef.SQL = "SELECT Col2 FROM tblTemp"
  7. set rst = qDef.OpenRecordset
  8.  
What this piece of code is doing is creating a query on the fly in the current database. It then uses the SELECT string to draw the records. From there a Recordset is created virtually to contain the results.

This is very important because from the recordset a number of things can be accomplished (I recommend looking other places for further information on recordsets as I am just learning them really). Still for your purposes you will want to call upon the various fields of the recordset. Remember that the field numbers are indexed starting at 0. Below is the code that would return the value of a single row table with three columns to three seperate string variables.

Expand|Select|Wrap|Line Numbers
  1. 'Continued from previous block of code
  2. Dim Str1, Str2, Str3 as String
  3.  
  4. Str1 = rst.Fields(0).Value
  5. Str2 = rst.Fields(1).Value
  6. Str3 = rst.Fields(2).Value
  7.  
Grant it you could use a loop statement to assign these as well if you had a varying number of fields. Additionally a loop statement could be used to step through the records if the table / query was to return multiple records.

I hope this is first of all correct (still getting use to writing code outside of editor) and most of all will help you or at least get you moving in the right direction.

As always if I've made a syntax error or there is a better way to do this please correct me.

Hope this helps.

- Minion -
Dec 27 '07 #4

P: 6
- Minion -, thank you very much!!!

You did understand my problem correctly. I kept bypassing the use of recordsets for lack of understanding of their purpose. ..I've still got a lot to learn.

Your explanation along with the code example did the trick.
I'll be able to utilize that in several places.

Again, thank you very much for taking the time to read and reply to my question.
If I could shake your hand, I would.

JustAPawn

(pawns can only move forward..)
Dec 27 '07 #5

Dököll
Expert 100+
P: 2,364
- Minion -, thank you very much!!!

You did understand my problem correctly. I kept bypassing the use of recordsets for lack of understanding of their purpose. ..I've still got a lot to learn.

Your explanation along with the code example did the trick.
I'll be able to utilize that in several places.

Again, thank you very much for taking the time to read and reply to my question.
If I could shake your hand, I would.

JustAPawn

(pawns can only move forward..)
Hey, I'm glad you found an answer. I was confident you'd find sound help here.

Have fun:-)
Dec 28 '07 #6

Minion
Expert 100+
P: 108
- Minion -, thank you very much!!!

You did understand my problem correctly. I kept bypassing the use of recordsets for lack of understanding of their purpose. ..I've still got a lot to learn.

Your explanation along with the code example did the trick.
I'll be able to utilize that in several places.

Again, thank you very much for taking the time to read and reply to my question.
If I could shake your hand, I would.

JustAPawn

(pawns can only move forward..)

Glad to help. I wasn't sure if that code would do the trick or not, but figured it would get you running in the right direction.

Best of luck with the project.

- Minion -
Dec 28 '07 #7

P: 6
I've tried several different forums (won't name them) and ,so far, this is the only one that I have received help from. Thank you.

JustApawn
Dec 28 '07 #8

100+
P: 365
Mind if i join in??

i have am beginning to dabble with recordsets again (i hate them(they never work for me(fine example here)))

i have used the code above

Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2. Dim qDef As QueryDef
  3.  
  4. Set qDef = CurrentDb.CreateQueryDef("")
  5. qDef.SQL = "SELECT tblStaff.StaffID FROM tblStaff WHERE (((tblStaff.Department)=[Forms]![frmHolidayDates]![ListDept]))"
  6. Set rst = qDef.OpenRecordset
and it says too few parameters... strange?
what i want to do is return a list of staffid's that i can use one by one to put in another loop that appends to a table which is used to crosstab info

thanks if ya can help :)
Dec 28 '07 #9

100+
P: 365
ok figured it out, the sql wouldnt let me put in the form control ([Forms]![frmHolidayDates]![listDept]) i had to "dim" it

anyone know why this is i cant undertand it (the form is open before anyone asks)!?
Dec 28 '07 #10

P: 13
ok figured it out, the sql wouldnt let me put in the form control ([Forms]![frmHolidayDates]![listDept]) i had to "dim" it

anyone know why this is i cant undertand it (the form is open before anyone asks)!?
Hello Dan,

I'm not sure if I understand your problem correctly, but I think I know why your SQL statement doesn't work.

You can't just write "SELECT tblStaff.StaffID FROM tblStaff WHERE (((tblStaff.Department)=[Forms]![frmHolidayDates]![ListDept]))"

I don't have MS Access at home, so I can't check it, but I think that the control and form's name can't be inside the string. They have to be outside the quotes ( " " ), because they are variables.

Try experimenting with sth like:

"SELECT tblStaff.StaffID FROM tblStaff WHERE (((tblStaff.Department)=" & [Forms]![frmHolidayDates]![ListDept] & "))"

If your control (ListDept) contains text, then you also have to add single quotes around it (' ').

Good luck!
Dec 29 '07 #11

Post your reply

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