SQL Select Satements (Access 2003)

**mitchbvi** · 2003-12-20 18:41

Can anyone tell me what is wrong with this Function.

Function FindOpWells()
Dim SQL As String
SQL = "SELECT * FROM tblOperations"
DoCmd.RunSQL SQL
End Function

When I try and run it I am told "An Sql Statements requires an arguement consisting of an SQL statement. My original Query was more complicated and I shortened it to try and see what I was doing incorrectly.

thanks

Peter

**HansV** · 2003-12-20 19:17

Your SQL string is for a select query that returns records. RunSQL is meant to execute SQL strings that represent action queries, i.e. make table, append, update or delete queries. Such queries begin with SELECT INTO, INSERT INTO, UPDATE or DELETE, not with just SELECT.

What do you want to accomplish with this function?

**mitchbvi** · 2003-12-21 01:14

The object was to show which wells were already assigned to an operator. The operator is selected from a combo box and a click on a button is tsupposed to run the query. On thinking about it I suppose I want to make a table based on the records is this correct?

Thanks for your help.

Peter

**HansV** · 2003-12-21 01:17

Why don't you open a form based on tblOperations?

**mitchbvi** · 2003-12-21 04:53

I was trying to reduce the number of forms I have and thought a datasheet would do the job. I assume from what you have told me there is not a way to do this. Anyway once agian my thanks for all your help at least I know what I cannot do.

regards

Peter

**HansV** · 2003-12-21 12:13

To display a datasheet (which I don't recommend), you would have to create and save a query, then use DoCmd.OpenQuery. There is no way you can show the records returned by a SELECT SQL string directly, without using a stored query or form.

**mitchbvi** · 2003-12-21 16:19

Thank you again. If I use a form I think I will have to base it on a query as I want to pass a parmeter so I only get the records relative to the operator that has been selected. Therefore I assume I cannot use the DoCmd.Openquery as I cannot pass a paremeter to it, am I correct?

Regards

Peter

**HansV** · 2003-12-21 19:20

Well, you could create a query with criteria of the form [Forms]![frmOperators]![txtOperator], and open this. But I never let end users view a table or query directly; they can only view and edit data in a form. Since DoCmd.OpenForm has a WhereCondition argument, it is easy to filter a form.

**mitchbvi** · 2003-12-21 23:26

Thanks Hans I will try that, had not thought about what havoc users could create, its early days for me.

regards

Peter

Thread: SQL Select Satements (Access 2003)

Thread Tools