473,574 Members | 3,151 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.EggCo lor
FROM EggsTable
WHERE (((EggsTable.Eg gColor)=[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'Form s.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?

Jul 23 '05 #1
7 6403

<ri********@gma il.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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.EggCo lor
FROM EggsTable
WHERE (((EggsTable.Eg gColor)=[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'Form s.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
Jul 23 '05 #2
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(Can cel As Integer)
Me.RecordSource = "SELECT EggColor FROM dbo.EggsTable " & _
"WHERE EggColor='" & Forms!EggColorF orm!ColorTextBo x & "'"
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.EggCo lor
FROM dbo.EggsTable
WHERE (((EggsTable.Eg gColor)=@EggCol or))

Thanks for your help.

Simon Hayes wrote:
<ri********@gma il.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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.EggCo lor
FROM EggsTable
WHERE (((EggsTable.Eg gColor)=[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'Form s.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


Jul 23 '05 #3
(rc*********@ya hoo.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
<ri********@gma il.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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
Jul 23 '05 #5
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.

Jul 23 '05 #6
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.


Jul 23 '05 #7
<rc*********@ya hoo.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2519
by: Raghuraman | last post by:
Hai I have a designed Crystal report .rpt file which has 4 access tables .. After designing the report i put a parameter field which filteres the desired records quite naturally. Every thing works fine in the crystal report design time as well as in the preview asking parameters .. But if i call it thru my VB form it works nice after...
49
14308
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. ...
15
7229
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? Thank you.
92
7596
by: Jeffrey P via AccessMonster.com | last post by:
Our IT guys are on a vendetta against MS Access (and Lotus Notes but they've won that fight). What I can't understand is, what's the problem? Why does IT hate MS Access so much. I have tried to find out who it is that actually wants to get rid of it, but I can't find anyone who will admit to trying to get rid of it. Nevertheless, I'm...
13
4437
by: Greg Strong | last post by:
Hello All, Hello All, What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to an Access Project (i.e. ADP) file for the front end using Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) for the back end? Now for the background. I have a prototype MDB file that was built in Access 2K2, and compiled in Access 2K to...
11
2406
by: Steve Jorgensen | last post by:
I'm working on an application in Access 2002, and every time I'm done making changes in the VBA project, the database is noticeably larger after compacting, even when code changes are small or even if they involve removing more code than I add. In the past, I've usually been able to recover space in these situations by decompiling the...
22
6250
by: Jordan S. | last post by:
SQL Server will be used as the back-end database to a non trivial client application. In question is the choice of client application: I need to be able to speak intelligently about when one client (MS Access vs ..NET Windows Forms) would be preferred over the other. While I have some good arguments on both sides, I would appreciate your...
16
6503
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report, that I've also created within MS Access. I've attempted using the Access.Application instance, but I get errors when it tries to load the ...
10
3343
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Not sure if I quite follow that. 1. Data encrypted by AES key 2. AES key encrypted with Asymmetric public key (?)
0
7764
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8110
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8273
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7862
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8138
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6514
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5336
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3775
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2277
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.