problem passing Form textbox value to query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

problem passing Form textbox value to query

Author

Topic

RC
Starting Member

8 Posts

Posted - 2005-01-13 : 00:45:08

I just changed my Access 2002 database to a SQL Server ADP project. I
had a form where the user entered a value into a text box and when a
command button on the form was clicked a Report was opened. The reports
record source is a query. The query uses the value from the form text
box to restrict the query.
Table name = EggsTable
one of the columns in the table is named: EggColor
Form name = EggColorForm
Form text box name = ColorTextBox

This sql worked for the query before I converted to SQL:

SELECT EggsTable.EggColor
FROM dbo.EggsTable
WHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));

This no longer works. Can I change the syntax somehow to get this to
work? I tried dropping the brackets around the word "Forms", I tried
dropping all the square brackets, etc., nothing worked.
When I make the change in the design view the editor changes the code to a the letter N in front of the [Forms], like:
WHERE "EggColor = N'[Forms]![EggColorForm]![ColorTextBox]'

I also tried just opening the report with the report's Server Filter
property set to:
EggColor=N'Forms.EggColorForm.ColorTextBox'

I tried using the Report's open event to pass the form value directly
to the report. I tried setting a variable from the text box value on
the form. So far, nothing works. Any ideas?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-13 : 02:59:07

You can build RecordSource for the report on the fly:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT EggColor FROM dbo.EggsTable " & _
"WHERE EggColor='" & Forms!EggColorForm!ColorTextBox & "'"
End Sub

or
you can rewrite the query as a stored procedure and assign it to the report in its Prop Sheet
(along with Input Parameters: @EggColor varchar(10) = Forms![EggColorForm]![ColorTextBox])

create procedure mp_EggColors @EggColor varchar(10)
as
SELECT EggsTable.EggColor
FROM dbo.EggsTable
WHERE (((EggsTable.EggColor)=@EggColor))

RC
Starting Member

8 Posts

Posted - 2005-01-13 : 17:55:32

THANK YOU !!!!
I cannot say how grateful I am for your help. Thank you for sharing your knowledge. I did not even know this Forum existed until I did a Google search for an answer to my question, now I'm a big fan of this site. I spent 14 hours (really) trying every which way to change the syntax around to make this work. I used the Report Open event
and because my report shows Description and EggColor it ended up like this:

Me.RecordSource = "SELECT , Description, EggColor
FROM dbo.InventoryTable
WHERE EggColor='" & Forms!EggColorForm!ColorTextBox & "'"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-14 : 06:29:09

You are welcome, RC!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources