MS Access front end to SQL Server problem passing Form value to Report

[rickcheney]

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 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.

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?

[Simon Hayes]

<ri********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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 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.

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?

I know nothing about ADP, but since MSSQL is a server, it has no concept of
forms or front end interfaces - you need to get the value in your ADP form,
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 the
stored proc, and present the result set in your report (as far as I can see,
the query you have will return the parameter you pass to it, so it appears
not to do much, but I assume this is just a trivial example). This link has
some examples of calling procs from ASP using ADO, but I guess the concept
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 an
Access or ADP forum on exactly how to pass your value from the front end to
MSSQL.

Simon

[rcmail14872]

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:

<ri********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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 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.

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?

I know nothing about ADP, but since MSSQL is a server, it has no

concept of forms or front end interfaces - you need to get the value in your ADP form, 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 the stored proc, and present the result set in your report (as far as I can see, the query you have will return the parameter you pass to it, so it appears not to do much, but I assume this is just a trivial example). This link has some examples of calling procs from ASP using ADO, but I guess the concept 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 an Access or ADP forum on exactly how to pass your value from the front end to MSSQL.

Simon

[Erland Sommarskog]

(rc*********@yahoo.com) writes:

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!

Beware that

DECLARE @myVariable AS varchar

makes @myVariable a varchar(1)! All strings have a maximum length in
SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andy O'Neill]

<ri********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I just changed my Access 2002 database to a SQL Server ADP project. <<>> 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?

Yes.
Avoid using ADP.
Do you really need to convert your project?

There are some rather odd behaviours in access ADP can be a right pain to
work with.
EG A technique that works fine in one form will not work in another for no
appararent reason.
I think relatively few people are using ADP for serious development.

Fire up google and take a look at posts on the access newsgroup.

--
Regards,
Andy O'Neill

[rickcheney]

ADP as opposed to what? I mean, what GUI front end for the SQL Server
database?

Also, I have SQL Server 2000 evaluation version on DesktopA and the
same SQL Server version running on DesktopB. I would like to use the
SQL Server Enterprise Manager wizard to copy the database from DesktopA
to DesktopB. SQL Server is running on both PCs as a local system
account (I think, how do I check?). When I start the wizard, it says
(local) for the source server and "use windows authentication", the
next step asks for the destination server and lists (local) and
DESKTOPB, when I pick DESKTOPB and either Windows and SQL
authentication I get the error message, "Cannot connect to the server
DESKTOPB. Please check the server name and try again." I am new to this
so any ideas of what I should try would be appreciated. I don't know if
I need to "register" the servers or anything else I might be leaving
out.

[rcmail14872]

Oh, you mean keep the database as an Access MDB. If that's what you
mean, I know what you mean, I like and prefer Access and I think it can
handle quite a lot but in this case I don't have a choice, everything
else is in SQL and it seems to be best to keep all the databases
consistent.

ADP as opposed to what? I mean, what GUI front end for the SQL Server database?

Also, I have SQL Server 2000 evaluation version on DesktopA and the
same SQL Server version running on DesktopB. I would like to use the
SQL Server Enterprise Manager wizard to copy the database from DesktopA to DesktopB. SQL Server is running on both PCs as a local system
account (I think, how do I check?). When I start the wizard, it says
(local) for the source server and "use windows authentication", the
next step asks for the destination server and lists (local) and
DESKTOPB, when I pick DESKTOPB and either Windows and SQL
authentication I get the error message, "Cannot connect to the server
DESKTOPB. Please check the server name and try again." I am new to this so any ideas of what I should try would be appreciated. I don't know if I need to "register" the servers or anything else I might be leaving
out.

[Andy O'Neill]

<rc*********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Oh, you mean keep the database as an Access MDB.
No.
I mean keep the front end as an access mdb and convert the back end to sql
server.

Then attach the tables from the sql server to the access md.
This uses ODBC with a dsn to specify server etc.
Keep the same structure and names and this can mean no (other) changes to
the front end mdb.
If you're thinking front end mdb???
You want to search on google for splitting access databases.
Front end mdb goes on each end user pc and contains all the code bits.
Back end sits on server and contains all the data.

Re-writing as an adp is NOT as simple as just pushing a button.
Can be a tricky bit of work revisiting every screen.
I would question whether that work is really justified.
If that's what you
mean, I know what you mean, I like and prefer Access and I think it can
handle quite a lot but in this case I don't have a choice, everything
else is in SQL and it seems to be best to keep all the databases
consistent.

ADP as opposed to what? I mean, what GUI front end for the SQL

Server

database?

Also, I have SQL Server 2000 evaluation version on DesktopA and the
same SQL Server version running on DesktopB. I would like to use the
SQL Server Enterprise Manager wizard to copy the database from

DesktopA

to DesktopB. SQL Server is running on both PCs as a local system
account (I think, how do I check?). When I start the wizard, it says
(local) for the source server and "use windows authentication", the
next step asks for the destination server and lists (local) and
DESKTOPB, when I pick DESKTOPB and either Windows and SQL
authentication I get the error message, "Cannot connect to the server
DESKTOPB. Please check the server name and try again." I am new to

this

so any ideas of what I should try would be appreciated. I don't know

if

I need to "register" the servers or anything else I might be leaving
out.

Why move the database?
You realise you want to have just one database for an application, rather
than one on each desktop?

Dunno why you can't see the other machine, are you logging onto them using
the same user ?
Will that have authority enough?

Alternatively.
Back up and restore or sp_detach, copy the database files and sp_attach.
--
Regards,
Andy O'Neill