Hi All,
Is it possible to use form criteria in a query / view in an Access
2003 ADP with SQL Server as the backend?
ie something like:
select * from mytable where recid = forms![frm_myform]![recid]
I'm trying to do something like this as the basis for a subform but I
get a syntax error because of the existence of the "!" characters.
Any help appreciated!
Much warmth,
Murray 5 4949
No, but there is a similar trick you can do with ADPs. If you create a stored
procedure to use as the Row Source of a combo or list box, and the stored
procedure takes a parameter with the same name (except for the additional
required @ sign) as a control on the same form, Access will automatically pass
the control's value to the stored procedure's parameter.
The same trick works with subforms, too. If the subform's Record Source is a
stored procedure, and the form has a control with the same name as a paramter
of the stored procedure, it will be automatically passed.
On Wed, 05 May 2004 14:44:15 GMT, M Wells <pl**********@p lanetthoughtful .org>
wrote: Hi All,
Is it possible to use form criteria in a query / view in an Access 2003 ADP with SQL Server as the backend?
ie something like:
select * from mytable where recid = forms![frm_myform]![recid]
I'm trying to do something like this as the basis for a subform but I get a syntax error because of the existence of the "!" characters.
Any help appreciated!
Much warmth,
Murray
You can't use a forms ref directly in the sql..but you can resolve it.
So, you can stuff the sql directly into the forms reocrdsouce.
dim strSql as string
strSql = "select * from mytable where recid = " & forms![frm_myform]![recid]
me.MySubForm.Fo rm.RecordSouce = strSql
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl************* *****@msn.com http://www.attcanada.net/~kallal.msn
Ah!
Thanks Steve, another thing I was wondering about was how to pass
multiple criteria to a stored procedure from a form.
I think what you've written below solves that issue for me.
However, do stored procedures allow criteria to be passed when they
involve subqueries? I've noticed that functions don't seem to allow
you to pass criteria if the function involves a subquery. Seems like
an odd ommision, since just about every other subform I create is
doing something like, show all available records that aren't currently
included in another subform's recordset. With some playing around, I
can usually write the sql so that it doesn't use a subquery, but it's
a lot easier to simply use a 'where column not in (select column from'
statement.
Thanks for this tip!
Much warmth,
Murray
On Wed, 05 May 2004 16:53:37 GMT, Steve Jorgensen
<no****@nospam. nospam> wrote: No, but there is a similar trick you can do with ADPs. If you create a stored procedure to use as the Row Source of a combo or list box, and the stored procedure takes a parameter with the same name (except for the additional required @ sign) as a control on the same form, Access will automatically pass the control's value to the stored procedure's parameter.
The same trick works with subforms, too. If the subform's Record Source is a stored procedure, and the form has a control with the same name as a paramter of the stored procedure, it will be automatically passed.
On Wed, 05 May 2004 14:44:15 GMT, M Wells <pl**********@p lanetthoughtful .org> wrote:
Hi All,
Is it possible to use form criteria in a query / view in an Access 2003 ADP with SQL Server as the backend?
ie something like:
select * from mytable where recid = forms![frm_myform]![recid]
I'm trying to do something like this as the basis for a subform but I get a syntax error because of the existence of the "!" characters.
Any help appreciated!
Much warmth,
Murray
"M Wells" <pl**********@p lanetthoughtful .org> wrote in message
news:b9******** *************** *********@4ax.c om... Ah!
Thanks Steve, another thing I was wondering about was how to pass multiple criteria to a stored procedure from a form.
If the form is bound to the procedure use the forms' InputParameters
property setting:
I think what you've written below solves that issue for me.
However, do stored procedures allow criteria to be passed when they involve subqueries?
AFAIK the structure of the procedure has no influence on whether or not it
will accept parameters.
I've noticed that functions don't seem to allow you to pass criteria if the function involves a subquery.
I've never seen this. Do you have a script to repro this problem?
I don't even know whether you can bind a function to a form - I've heard
that you can but not sure why you would ever want to.
On Wed, 12 May 2004 09:51:07 -0400, "John Winterbottom"
<as******@hotma il.com> wrote: I've noticed that functions don't seem to allow you to pass criteria if the function involves a subquery.
I've never seen this. Do you have a script to repro this problem? I don't even know whether you can bind a function to a form - I've heard that you can but not sure why you would ever want to.
Hi John,
Using a function to return a recordset to a form using form criteria
is described in the following KnowledgeBase article: http://support.microsoft.com/default...b;en-us;278400
Given that this was the recommendation Microsoft made, this is what I
did.
However, attempting to include an @variable in a function with a
subquery returns the error message I described in my original post.
Much warmth,
Murray This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Steve |
last post by:
Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestock!LastXDays. LastXDays on the form is a combobox where the
selections are 30, 60 and 90. The default is set at 30.
Question1: When the form opens, there are no records displayed although there
are many records that fit the criteria of 30. If I put a button on the form to
do a requery and press the button, all the records...
|
by: CSDunn |
last post by:
Hello,
I have a problem with field filtering between an Access 2000 Project form
(the application is called CELDT), and the report that shows the results of
the filter. Both the form and the report are based on the same View
addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides
in a SQL Server 2000 database. There are two different problems I am having
with filtering: The first involves filtering in the form by date...
|
by: dskillingstad |
last post by:
I'd appreciate any help I can get. I'm not sure what I'm doing wrong,
but....
I've searched these groups for some solutions but no luck.
I have an unbound form (frmSearch), with several unbound text boxes on
it and a command button bound to a macro which fires off a parameter
query based on the criteria/string that the user types into the text
boxes on frmSearch. My goal is to create a search form where the user
can search by any of...
|
by: |
last post by:
hi,
i have a form on which a user can choose specific criteria such as dates
etc, in order to filter the report that is called from the form.
i do this by using the Where section of the docmd.openreport as follows
DoCmd.OpenReport stDocName, acPreview, , strWhere
where strWhere is a string dependant on the choices the user makes in the
|
by: keliie |
last post by:
Hello
Just a quick question that I've spent a few hours trying to solve with
no luck (although one would think this should be fairly easy).
I have a form with a subform. The subform is based on a query. The
criteria for the query is based on the user's input in the main form.
One of the user inputs is a field called "Vendor_Name" (in the form of
a combo box). I would like the query to run for all "Vendor_Name" if
the user leaves the...
| |
by: google |
last post by:
I'm developing an application for use within my company in Access 2003.
I'm new to '03, the application I did for my former employer was in
'97. The two applications have similar functionality (we're sales
offices, and I'm doing things such as associate directories, commission
calculations, order tracking, etc.). 2003 seems to have a few extra
features, but I seem to continually run in to oddities that seem like
they SHOULD work, but...
|
by: fstenoughsnoopy |
last post by:
I have a customer order database and I need to pull a customers
information, ie first name, last name, address, city, state, zip,
phone, etc, into the oder table. i don't know how to go about making
this work...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date
The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so...
|
by: hollinshead |
last post by:
Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo boxes. So when the criteria is chosen the user selects a click command button and then the results are displayed on another form. I have successfully created the code for the combo boxes on the form.The list boxes are are the problem which are set to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |