Pass Combo Value to Query

[artemetis]

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

cboTableSelect gets it's values from:

SELECT MSysObjects.Name 
FROM MSysObjectsWHERE (((MSysObjects.Type=1) AND ((MSysObjects.Name) Like "*tbl*")) OR (((MSysObjects.Type=6) AND ((MSysObjects.Name) Not Like "*~TMPCLP*"))
ORDER BY MSysObjects.Name;

and
cmdQrySurveyInp ut runs the query which contains the following:

 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?

[ChipR]

To run the update, you can just execute an SQL statement. Something like:

 Dim strSQL as string
strSQL = "Update " & cboTableSelect & " SET ..."
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.

[NeoPa]

strSQL = "Update " & cboTableSelect & " SET ..."

For unspecified table names, I'd use :

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.

[ChipR]

Thanks NeoPa, I see what you mean, and I'll be sure to do that in the future.

[artemetis]

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:

Private Sub cmdImportSurvey_Click()
Dim strSQL As String
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.........;"
 
End Sub

[NeoPa]

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 ;) )?

[artemetis]

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 ;) )?

Yeah, this is where I lost myself....I don't know how to execute that with the cmdButton.....i s there a DoCmd?

[Dan2kx]

DoCmd.RunSQL strSQL
'OR JUST
DoCmd.RunSQL "SELECT.........."

[NeoPa]

There is also the "CurrentDB.Exec ute" method of course (as found in ChipR's post #2).
Check out the Help section for each to see what different parameters each presents.

[artemetis]

DoCmd.RunSQL strSQL
'OR JUST
DoCmd.RunSQL "SELECT.........."

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!

[NeoPa]

We're glad to help Art :)

Very pleased to hear how happy you are with everything.