Thanks, I tried using a variable but I kept getting the error message,
"You need to declare the variable", I checked and checked and the
variable was declared, BUT, I was just studying SQL syntax and I found
that with SQL you declare a variable with: DECLARE @myVariable as
varchar
what a concept!
I was using the MS Access way to declare a variable which is: Dim as
in
Dim myVariable as string
Also, a couple of other ideas, that I think will work but I haven't
tested yet.
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
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))
Thanks for your help.
Simon Hayes wrote:[color=blue]
> <rickcheney@gmail.com> wrote in message
> news:1105584733.015653.17480@z14g2000cwz.googlegro ups.com...[color=green]
> >I just changed my Access 2002 database to a SQL Server ADP project.[/color][/color]
I[color=blue][color=green]
> > had a form where the user entered a value into a text box and when[/color][/color]
a[color=blue][color=green]
> > command button on the form was clicked a Report was opened. The[/color][/color]
reports[color=blue][color=green]
> > record source is a query. The query uses the value from the form[/color][/color]
text[color=blue][color=green]
> > 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 EggsTable
> > WHERE[/color][/color]
(((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));[color=blue][color=green]
> >
> > This no longer works. Can I change the syntax somehow to get this[/color][/color]
to[color=blue][color=green]
> > work? I tried dropping the brackets around the word "Forms", I[/color][/color]
tried[color=blue][color=green]
> > dropping all the square brackets, etc., nothing worked.
> >
> > I also tried just opening the report with the report's Server[/color][/color]
Filter[color=blue][color=green]
> > property set to:
> > EggColor=N'Forms.EggColorForm.ColorTextBox'
> >
> > I tried using the Report's open event to pass the form value[/color][/color]
directly[color=blue][color=green]
> > to the report. I tried setting a variable from the text box value[/color][/color]
on[color=blue][color=green]
> > the form. So far, nothing works. Any ideas?
> >[/color]
>
> I know nothing about ADP, but since MSSQL is a server, it has no[/color]
concept of[color=blue]
> forms or front end interfaces - you need to get the value in your ADP[/color]
form,[color=blue]
> then pass it to MSSQL, preferably as the parameter to a stored proc:
>
> create proc dbo.GetEggColor
> @color varchar(10)
> as
> set nocount on
> begin
> select EggColor
> from dbo.EggsTable
> where EggColor = @color
> end
>
> You could then use the ADO Connection object or whatever to execute[/color]
the[color=blue]
> stored proc, and present the result set in your report (as far as I[/color]
can see,[color=blue]
> the query you have will return the parameter you pass to it, so it[/color]
appears[color=blue]
> not to do much, but I assume this is just a trivial example). This[/color]
link has[color=blue]
> some examples of calling procs from ASP using ADO, but I guess the[/color]
concept[color=blue]
> is similar from Access:
>
>
http://www.aspfaq.com/show.asp?id=2201
>
> If this isn't helpful, you'll probably get more detailed feedback in[/color]
an[color=blue]
> Access or ADP forum on exactly how to pass your value from the front[/color]
end to[color=blue]
> MSSQL.
>
> Simon[/color]
