Referencing form value from within a query

[ozTinker]

I'm sure this shouldn't be too difficult, but I lack familiarity with the MS object model.

Suppose I have a table "Purchase_Orders" and a form "TEMP" which I am using to look up a customer's orders. The form has a text field named LastName for typing in new orders, but first let's look at the previous orders. Elsewhere on the form is a Office Web Components Spreadsheet version 11.0, which will run a query and show the result list.

The Spreadsheet object connects to the data source, and the test connection checks out good. The query :

SELECT * FROM Purchase_Orders WHERE
(Purchase_Orders.LastName=Forms!TEMP!LastName)

The result returned is:

No value given for one or more required parameters.

This seems to be a matter of syntax and not referencing the form field properly from inside the query. If I instead use

WHERE LastName='Smith'

it works just fine.

Can someone help me out with the proper MS object referencing convention?

[Rabbit]

I'm sure this shouldn't be too difficult, but I lack familiarity with the MS object model.

Suppose I have a table "Purchase_Orders" and a form "TEMP" which I am using to look up a customer's orders. The form has a text field named LastName for typing in new orders, but first let's look at the previous orders. Elsewhere on the form is a Office Web Components Spreadsheet version 11.0, which will run a query and show the result list.

The Spreadsheet object connects to the data source, and the test connection checks out good. The query :

SELECT * FROM Purchase_Orders WHERE
(Purchase_Orders.LastName=Forms!TEMP!LastName)

The result returned is:

No value given for one or more required parameters.

This seems to be a matter of syntax and not referencing the form field properly from inside the query. If I instead use

WHERE LastName='Smith'

it works just fine.

Can someone help me out with the proper MS object referencing convention?

1) Is the form open when you try to run the query?

2) Is the name, not the control source or the name of the field, of the textbox actually LastName? The Name of a control and the Control Source / Field Name are different things.

[puppydogbuddy]

I'm sure this shouldn't be too difficult, but I lack familiarity with the MS object model.

Suppose I have a table "Purchase_Orders" and a form "TEMP" which I am using to look up a customer's orders. The form has a text field named LastName for typing in new orders, but first let's look at the previous orders. Elsewhere on the form is a Office Web Components Spreadsheet version 11.0, which will run a query and show the result list.

The Spreadsheet object connects to the data source, and the test connection checks out good. The query :

SELECT * FROM Purchase_Orders WHERE
(Purchase_Orders.LastName=Forms!TEMP!LastName)

The result returned is:

No value given for one or more required parameters.

This seems to be a matter of syntax and not referencing the form field properly from inside the query. If I instead use

WHERE LastName='Smith'

it works just fine.

Can someone help me out with the proper MS object referencing convention?

LastName is entered as a text string on the form. The syntax that identifes it is as follows:

SELECT * FROM Purchase_Orders WHERE
("Purchase_Orders.LastName= '" & Forms!TEMP!LastName & "'")

[Rabbit]

LastName is entered as a text string on the form. The syntax that identifes it is as follows:

SELECT * FROM Purchase_Orders WHERE
("Purchase_Orders.LastName= '" & Forms!TEMP!LastName & "'")

This is only if he's trying to do it through VBA code. You don't need the quotes if you're doing from the query builder.

[puppydogbuddy]

This is only if he's trying to do it through VBA code. You don't need the quotes if you're doing from the query builder.

Rabbit,
That is correct. I assumed that he was running his Select statement from VBA because he didn't mention anything about the query grid, nor did he refer to the query by name like he would if it were a saved query.

[ozTinker]

Thank you all for your responses. I looked at the areas you suggested and while this surely has me on the right track, my form still needs some work:

- First I verified that the field name was correct; it is "LastName", same as the table column.

- The text field and the spreadsheet object are both part of the same form.

- Then I tried the statement:

WHERE ("My_Table.LastName= '" & Forms!TEMP!LastName & "'")

Unfortunately that didn't work either, same error message given.

I am building this query by right-clicking on the spreadsheet (in Design view of the form), then selecting Microsoft Office Spreadsheet 11.0 Object > Commands and Options

This yields a dialog box with tabs, and under the Data Source tab putting in the connection string and "Command Text or SQL".

Again, if I specify the WHERE clause with "WHERE LastName='Smith'" it will return an appropriate list of results.

Just for spite I tried putting in

MsgBox "Whatever"

just to see what it would do and it throws:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

...Any suggestions what I might try next?

Tim

[puppydogbuddy]

Thank you all for your responses. I looked at the areas you suggested and while this surely has me on the right track, my form still needs some work:

- First I verified that the field name was correct; it is "LastName", same as the table column.

- The text field and the spreadsheet object are both part of the same form.

- Then I tried the statement:

WHERE ("My_Table.LastName= '" & Forms!TEMP!LastName & "'")

Unfortunately that didn't work either, same error message given.

I am building this query by right-clicking on the spreadsheet (in Design view of the form), then selecting Microsoft Office Spreadsheet 11.0 Object > Commands and Options

This yields a dialog box with tabs, and under the Data Source tab putting in the connection string and "Command Text or SQL".

Again, if I specify the WHERE clause with "WHERE LastName='Smith'" it will return an appropriate list of results.

Just for spite I tried putting in

MsgBox "Whatever"

just to see what it would do and it throws:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

...Any suggestions what I might try next?

Tim

Maybe the cryptic message was saying that the return value was a null. Also maybe the Excel object prefers the dot operator over the bang (!) operator. Try these different variations in syntax and see what happens.

WHERE ("My_Table.LastName= Forms!TEMP!LastName”)

WHERE ("My_Table.LastName= '" & nz(Forms!TEMP!LastName & "'"),””)

WHERE ("My_Table.LastName= '" & nz(Me!LastName & "'"),””)

WHERE ("My_Table.LastName= '" & nz(Me.LastName & "'"),””)

[Rabbit]

Try this:
SELECT * FROM Purchase_Orders WHERE
(Purchase_Orders.LastName=Forms!TEMP!LastName)

[ozTinker]

Just to give you some perspective, what I found was that all these ideas were good but none of them worked in the Spreadsheet object, most likely because the ActiveX control does not fully implement the VBA conventions.

If I opened a datasheet and had a form open at the same time, then yes the syntax was correct for reading the form control and modifying the SQL query based on that. Wroked beautifully in fact.

It would seem that the Spreadsheet ActiveX object just has limited functionality.

After many, many hours of trial and error :-) I have changed my approach and now am using a subform that has a table merged to my original form field. This gives me pretty much the functionality I wanted, of being able to see rows existing in the table as I add a new row using the form.

Again, thanks for your efforts.

Tim

[Rabbit]

Just to give you some perspective, what I found was that all these ideas were good but none of them worked in the Spreadsheet object, most likely because the ActiveX control does not fully implement the VBA conventions.

If I opened a datasheet and had a form open at the same time, then yes the syntax was correct for reading the form control and modifying the SQL query based on that. Wroked beautifully in fact.

It would seem that the Spreadsheet ActiveX object just has limited functionality.

After many, many hours of trial and error :-) I have changed my approach and now am using a subform that has a table merged to my original form field. This gives me pretty much the functionality I wanted, of being able to see rows existing in the table as I add a new row using the form.

Again, thanks for your efforts.

Tim

Glad you found your solution.

Am I hearing you correctly though that you didn't have the form loaded before opening the spreadsheet? An unloaded form has no values.

[ozTinker]

Nope, the form was loaded all right :-) The text fields were on the top part of the form, and the spreadsheet object was in the bottom part.

It started working once I deleted the spreadsheet and replaced it with a sub-form containing a table. The table is linked to the form field.

Interestingly, I found if I had done a separate table generated by a query, suddenly the format

WHERE MyTable.LastName = [Forms]![MyForm]![LastName]

Becomes valid, even though it didn't work in the Spreadsheet query.

...Go figure! :-)

[Rabbit]

I think it's most likely a case of scope. The spreadsheet object is in a world itself and doesn't have access to the values of the forms. Althought I suppose you could change the value of the spreadsheet from a form rather than from in the spreadsheet.