Invalid SQL Statement error - Combobox in Query design

[danny2000]

I'm getting the error below when I'm trying to use a Combobox Value in my access query. I've placed the below reference to the combobox in the query criteria in design view.



The idea is that when I change the combobox the event triggers a procedure which calls the query. That query incorporates the value from Combobox50. I've also placed the code below as well. I've attached the file.
This is quiet a mystery.

Not sure if my syntax is wrong. Can anybody help. Thanks

[form]![form1]![combo2] 'have placed this in criteria field


Error below.
'Invalid SQL Statement; Expected Delete, Insert, Procedure, select or Update'

Private Sub Combo2_AfterUpdate()
Dim rst1 As Recordset


Set rst1 = New ADODB.Recordset

rst1.Open "RefLookup", CurrentProject.Connection ' "RefLookup" is the query name.

Form_Form1.Text37.Value = rst1.Fields(1)

End Sub

[RuralGuy]

You will need to go through the FORMS collection:
[FORMS]![form1]![combo2]

[NoellaG]

Hi,


================================
dim rst1 as new ADODB.recordset

rst1.Open "select [FieldName] from RefLookup", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
with rst1

if not(.BOF and .EOF) then

.movefirst

Forms("Form1").Text37 = !FieldName

end if

.close

end with
set rst1 = nothing
===========================================

Also I would advise to give some more meaningfull names then "Text37" and add the error handling

Success
NG

[danny2000]

Thanks for the responses.

RuralGuy, I've tried several variations including 'Forms' with no success. Rightfully this should work and there are plenty of references to this method on the web.

NoellaG, I can see this is a way around my current problem but for the sake of understanding why this won't work I need to know how to make the current method work. I should be able to reference controls in the criteria field of the access query but it's just not letting me do that.

An example of this problem is attached. Thanks again for coming back to me on this, your help is greatly appreciated. Thanks

danny2000

[weekend00]

Syntax for rst.open SOURCE,,,
-------------------------------------
Source Optional. A Variant that evaluates to a valid Command object, an SQL statement, a table name, a stored procedure call, a URL, or the name of a file or Stream object containing a persistently stored Recordset.
-----------------------------

maybe source can't be a query name!

[ssanfu]

I looked at your dB. Here are the problems I found:

1) The criteria in the query was wrong. While it doesn't hurt to have the 'OR' clause in the criteria, you only need one of them. But you do have to spell correctly . You had:

[Form]![FindColour]![combo2] Or [forms]![findcolor]![combo2]

The correct spelling (and syntax) is:

[Forms]![FindColour]![combo2] Or [forms]![findcolour]![combo2]


2) In the combo box, the Column Count property (on the Format tab) should be 2.

3) You don't need to set the combo box DEFAULT property to "" (empty string). The Default property only affects new records. (not really a problem)

4) In the After Update event code, it is far easier (IMO) to use DAO rather ADO. This line "rst1.Open........" appears to only work with action queries. I don't use ADO, so I'm not really sure; that is what the error message said.

Also, you should uncheck "Perform Name Autocorrect" and "Track Autocorrect Info" (Tools/Options/Geeneral Tab). It is a known cause of corruption.


You can replace all of the code you have with one line:

Private Sub Combo2_AfterUpdate()
    Me.Text7 = Me.Combo2.Column(1)
End Sub

[danny2000]

Thanks very much for looking at this. I eventually corrected my spelling though thanks for checking.
I admit that the example I found on the web which using the combobox as a query parameter was using DAO now that I think of it. I think that what you say about action queries and ADO is correct as well, that may be the ultimate answer here. My main problem with this was not knowing why this wouldn't work. I'll eventually find a way to make it work though thanks again for your response. I'll leave the thread unsolved for a while to see if anyone can solve this one and will correct my spelling on the attachment.
Cheers
Danny2000

[NoellaG]

Hi,

weekend was right with his remark

maybe source can't be a query name!

but I don't see the problem if you use "select * from qryMyQuery" instead of just "qryMyQuery". It's just 3 words more...
I find working with ADO makes more sense when you are used to working OO, and is easier then DAO, but that's personal.

One more remark: when you want to work with action queries: use the command object. Example:

Dim cmd As New ADODB.Command
Dim cnn As ADODB.Connection
Dim lngAffected As Long
Dim strMyActionQuery as string
    
strMyActionQuery  = "put here the SQL for the action auery"    
Set cnn = CurrentProject.Connection
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strMyActionQuery
cmd.Execute lngAffected

greetings
NG

[danny2000]

Thanks very much.

I believe this solves it. You can't use a Query name as a source.

I should have read the instructions.

Thanks again.