cbo value

[terryvanduzee]

Hello

Can anyone tell me what is wrong with my SQL statement Im trying to use on my form?
Error:
A RunSql Action requires an arguement consisting of a SQL Statement.

strSQL = "SELECT * " & _
"FROM [tbl_CurrentInv] " & _
"WHERE [tbl_CurrentInv].[Itemname]= " & _
"'" & [Forms]![frm_Test]![Combo0].[Text] & "';"

Any ideas?



Thank you
Terry

[RuralGuy]

From VBA Help:

Note To set or return a control's Text property, the control must have the focus, or an error occurs. To move the focus to a control, you can use the SetFocus method or GoToControl action.

[dcrake]

The RunSQL command will only work on action queries such as append, updates, deletes it is not designed for select queries.

David

[CGM3]

Instead of the Text property, try using Value:

strSQL = "SELECT * " & "FROM [tbl_CurrentInv] " & "WHERE [tbl_CurrentInv].[Itemname]= " & "'" &
 [Forms]![frm_Test]![Combo0].[Value] & "';"

And you can run a normal query in VBA, so create a query using that logic (and, yes, it can reference the combo control), put the name of the query in a string (here, cQueryName), and use this VBA command:

DoCmd.OpenQuery cQueryName, acViewNormal, acReadOnly

The tricky part is getting people to close the query result that is displayed; I have this instruction in a label next to the list box I use to display available queries:

To close the query, click on the LOWER x (e.g. Close Window) control at the upper right corner of the screen. Clicking on the upper x control will close the Access application.

[terryvanduzee]

Thank you

I assigned the strSql to the recordsource. Works great

Thank you
Terry

[RuralGuy]

Great! Are you ready to use the Solved thread tool on this thread yet?

[terryvanduzee]

Thank you
Yes I am ready to use the solved thread, but where do I find it?

Thank you
Terry

[Matrix]

Thread Tools -> Mark this thread as solved.

Detailed instruction

[RuralGuy]

I have a link in my signature.