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

Pass Combo Value to Query

P: 76
I have a form named: frmSurveyInput
On the form there are two controls.
1. cboTableSelect - select input table
2. cmdQrySurveyInput - run qrySurveyInput

cboTableSelect gets it's values from:

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name 
  2. FROM MSysObjectsWHERE (((MSysObjects.Type=1) AND ((MSysObjects.Name) Like "*tbl*")) OR (((MSysObjects.Type=6) AND ((MSysObjects.Name) Not Like "*~TMPCLP*"))
  3. ORDER BY MSysObjects.Name; 
  4.  
and
cmdQrySurveyInput runs the query which contains the following:

Expand|Select|Wrap|Line Numbers
  1.  UPDATE     [your access table]       AS A 
I would like the [table name] in the query, to be passed to (received from the cboTableSelect) the sql from the cboTableSelect value.

Am I looking in the right direction?
Jan 14 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
To run the update, you can just execute an SQL statement. Something like:
Expand|Select|Wrap|Line Numbers
  1.  Dim strSQL as string
  2. strSQL = "Update " & cboTableSelect & " SET ..."
  3. Currentdb.Execute strSQL, dbfailonerror
On the other hand, if you have a parameter query that you want to show on click, then you would set the parameter in the query to Forms![frmSurveyInput]!cboTableSelect. Then you just have to make sure that form is open to run the query. Details in Everything About Using Parameters from Code.
Jan 14 '09 #2

NeoPa
Expert Mod 15k+
P: 31,494
@ChipR
For unspecified table names, I'd use :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Update [" & cboTableSelect & "] SET ..."
It's ok to leave the brackets ([]) out when you already know the name has only valid characters. Otherwise, it's safer to use them.
Jan 15 '09 #3

Expert 100+
P: 1,287
Thanks NeoPa, I see what you mean, and I'll be sure to do that in the future.
Jan 15 '09 #4

P: 76
I must be missing something? No worky

I'm assuming (I know...I know) that this was to be the event for the button?
Here's the code from the Form:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImportSurvey_Click()
  2. Dim strSQL As String
  3. strSQL = "UPDATE tblMain AS A INNER JOIN [" & cboTableSelect & "] AS E ON A.empId=E.[Employee Id] SET A.empReview= E.[Employee Review], so on and so on.........;"
  4.  
  5. End Sub
  6.  
Jan 16 '09 #5

NeoPa
Expert Mod 15k+
P: 31,494
The SQL seems ok from what you've shown. There appears to be no code executing it in your version however. Is it as simple as that? Or have you just left that out as irrelevant (Not a good idea ;) )?
Jan 18 '09 #6

P: 76
@NeoPa
Yeah, this is where I lost myself....I don't know how to execute that with the cmdButton.....is there a DoCmd?
Jan 18 '09 #7

100+
P: 365
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL strSQL
  2. 'OR JUST
  3. DoCmd.RunSQL "SELECT.........."
Jan 19 '09 #8

NeoPa
Expert Mod 15k+
P: 31,494
There is also the "CurrentDB.Execute" method of course (as found in ChipR's post #2).
Check out the Help section for each to see what different parameters each presents.
Jan 19 '09 #9

P: 76
@Dan2kx
Thanks to all for the assistance!!!
This did it.......

BTW - Did I say how much I LOVE this site???

Such nice people.....you all are the best!
Jan 23 '09 #10

NeoPa
Expert Mod 15k+
P: 31,494
We're glad to help Art :)

Very pleased to hear how happy you are with everything.
Jan 23 '09 #11

Post your reply

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