Parameter Search Form

[virginia via AccessMonster.com]

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I try
to set-up the combo box to the form, it isn't working correctly. When I open
the form it runs the function with the access default boxes and then it goes
to the form, but the entries you put into the form does not run the function.
If I don't set the on open to run the function, the form opens and still
nothing when entering into the boxes. I can link the function to a report and
it still runs, but runs the default text boxes.

I have searched and read the postings and have been to the Microsoft Site;
tried all the suggestions, but have been unsuccessful. I want the Form to
open and users be able to enter their criteria and run a report. Right now I
have the function working to search the data and it works like a champ, but
can't seem to get the combo box to pull the data from the form.

Thanks

 

[Sylvain Lafontaine]

You don't say how you are calling the function in your report - including
how you are trying to pass the parameters - so I cannot tell you where you
could have made an error. You should provide more details, including the
version of Access you are using.

Also, there have been many posts on this kind of subject in this newsgroup
in the past, so I'm not sure why you didn't find anything appropriate to
your problem here.

 

[virginia via AccessMonster.com]

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I must be
doing something wrong. I have been reading back through the postings and am
not finding anything that I haven't tried.

It must be that I am not setting the input parameter correctly. I printed out
directions from the Office Assistant and it said to do the following:

In ADP set the Input Parameters property in the main report:
Open the report, set the report's InputParameters property to a string that
specifies the parameters that are passed to the stored procedure that the
report is bound to.

The string must be an expression that includes the parameters you specified
in the stored procedure and the reference to the controls on the dialog box:
@Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date],
@Ending_date datetime = [Forms]![Sales Dialog]![Ending Date].

Then you set the OnOpen to the macro that opens the form and the OnClose to
the Macro that closes the Form.

I get an error that says:Bad Query Parameter @Enter_Process_Found (the Form
does open).

Below is the query that I have, if you run the query it runs fine, but not
attaching it to the report:

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE '%' + @Enter_Process_Found + '%') OR
([Part Name] LIKE '%' + @Enter_Part_Name + '%') OR
([Part Number] LIKE '%' + @Enter_Part_Number + '%') OR
([Lot (Serial) Number] LIKE '%' + @Enter_Lot_Number + '%
') OR
([Description of Issue] LIKE '%' + @Enter_Keyword + '%')


The reason for the Like '%' + is because the user may not know the entire
name of the item to search for.

This is what I have in the InputParameter for the Report:

@Enter_Process_Found = [Forms]![MRBSearch]![ProcessFound], @Enter_Part_Name =
[Forms]![MRBSearch]![Part Name], @Enter_Part_Number=[Forms]![MRBSearch]!
[Part Number], @Enter_Lot_Number = [Forms]![MRBSearch]![Lot Number],
@Enter_Keyword=[Forms]![MRBSearch]![Description of Issue]

My form is called MRBSearch and has the following boxes:
ProcessFound
Part Name
Part Number
Lot Number
Description of Issue

In the directions is does not state how the Form is linked to the query, only
how the report is linked to the Form. For MDB the query would be, for example:
Between [Forms]![Sales Dialog]![Beginning Date] and [Forms]![Sales Dialog]!
[Ending Date], which links the query to the Form.

Any help I can get would be greatly appreciated.
Thanks

You don't say how you are calling the function in your report - including
how you are trying to pass the parameters - so I cannot tell you where you
could have made an error. You should provide more details, including the
version of Access you are using.

Also, there have been many posts on this kind of subject in this newsgroup
in the past, so I'm not sure why you didn't find anything appropriate to
your problem here.

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I

[quoted text clipped - 19 lines]

Thanks

 

[Sylvain Lafontaine]

Personally, if I remember correctly, I never been able to use parameters
with parameterised queries myself with Access 2000. I never tried with
A2002 or 2003.

Instead, I use stored procedures (SP) or I build the full query string. For
simple queries like your, creating the SP is pretty straighforward.

For controls, particularly when they are located on a subform, building the
query string is better than using parameters because this will greatly
diminushing the number of uncessary queries that Access is doing for
controls when you move from record to record or when you make changes or
requeries to controls.

Also, on some localised systems, you must the semi-comma ; instead of the
comma to separate your parameters for the InputParameter property.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I must
be
doing something wrong. I have been reading back through the postings and
am
not finding anything that I haven't tried.

It must be that I am not setting the input parameter correctly. I printed
out
directions from the Office Assistant and it said to do the following:

In ADP set the Input Parameters property in the main report:
Open the report, set the report's InputParameters property to a string
that
specifies the parameters that are passed to the stored procedure that the
report is bound to.

The string must be an expression that includes the parameters you
specified
in the stored procedure and the reference to the controls on the dialog
box:
@Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date],
@Ending_date datetime = [Forms]![Sales Dialog]![Ending Date].

Then you set the OnOpen to the macro that opens the form and the OnClose
to
the Macro that closes the Form.

I get an error that says:Bad Query Parameter @Enter_Process_Found (the
Form
does open).

Below is the query that I have, if you run the query it runs fine, but not
attaching it to the report:

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part
Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE '%' + @Enter_Process_Found + '%') OR
([Part Name] LIKE '%' + @Enter_Part_Name + '%') OR
([Part Number] LIKE '%' + @Enter_Part_Number + '%')
OR
([Lot (Serial) Number] LIKE '%' + @Enter_Lot_Number +
'%
') OR
([Description of Issue] LIKE '%' + @Enter_Keyword +
'%')


The reason for the Like '%' + is because the user may not know the entire
name of the item to search for.

This is what I have in the InputParameter for the Report:

@Enter_Process_Found = [Forms]![MRBSearch]![ProcessFound],
@Enter_Part_Name =
[Forms]![MRBSearch]![Part Name], @Enter_Part_Number=[Forms]![MRBSearch]!
[Part Number], @Enter_Lot_Number = [Forms]![MRBSearch]![Lot Number],
@Enter_Keyword=[Forms]![MRBSearch]![Description of Issue]

My form is called MRBSearch and has the following boxes:
ProcessFound
Part Name
Part Number
Lot Number
Description of Issue

In the directions is does not state how the Form is linked to the query,
only
how the report is linked to the Form. For MDB the query would be, for
example:
Between [Forms]![Sales Dialog]![Beginning Date] and [Forms]![Sales
Dialog]!
[Ending Date], which links the query to the Form.

Any help I can get would be greatly appreciated.
Thanks

You don't say how you are calling the function in your report - including
how you are trying to pass the parameters - so I cannot tell you where you
could have made an error. You should provide more details, including the
version of Access you are using.

Also, there have been many posts on this kind of subject in this newsgroup
in the past, so I'm not sure why you didn't find anything appropriate to
your problem here.

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I

[quoted text clipped - 19 lines]

Thanks

 

[Sylvain Lafontaine]

Also, using things like "LIKE %...%" will not be very fast because this will
block SQL-Server to use any index; downgrading to make a full table scan
each time.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Personally, if I remember correctly, I never been able to use parameters
with parameterised queries myself with Access 2000. I never tried with
A2002 or 2003.

Instead, I use stored procedures (SP) or I build the full query string.
For simple queries like your, creating the SP is pretty straighforward.

For controls, particularly when they are located on a subform, building
the query string is better than using parameters because this will greatly
diminushing the number of uncessary queries that Access is doing for
controls when you move from record to record or when you make changes or
requeries to controls.

Also, on some localised systems, you must the semi-comma ; instead of the
comma to separate your parameters for the InputParameter property.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I
must be
doing something wrong. I have been reading back through the postings and
am
not finding anything that I haven't tried.

It must be that I am not setting the input parameter correctly. I printed
out
directions from the Office Assistant and it said to do the following:

In ADP set the Input Parameters property in the main report:
Open the report, set the report's InputParameters property to a string
that
specifies the parameters that are passed to the stored procedure that the
report is bound to.

The string must be an expression that includes the parameters you
specified
in the stored procedure and the reference to the controls on the dialog
box:
@Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date],
@Ending_date datetime = [Forms]![Sales Dialog]![Ending Date].

Then you set the OnOpen to the macro that opens the form and the OnClose
to
the Macro that closes the Form.

I get an error that says:Bad Query Parameter @Enter_Process_Found (the
Form
does open).

Below is the query that I have, if you run the query it runs fine, but
not
attaching it to the report:

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part
Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE '%' + @Enter_Process_Found + '%') OR
([Part Name] LIKE '%' + @Enter_Part_Name + '%') OR
([Part Number] LIKE '%' + @Enter_Part_Number + '%')
OR
([Lot (Serial) Number] LIKE '%' + @Enter_Lot_Number
+ '%
') OR
([Description of Issue] LIKE '%' + @Enter_Keyword +
'%')


The reason for the Like '%' + is because the user may not know the entire
name of the item to search for.

This is what I have in the InputParameter for the Report:

@Enter_Process_Found = [Forms]![MRBSearch]![ProcessFound],
@Enter_Part_Name =
[Forms]![MRBSearch]![Part Name], @Enter_Part_Number=[Forms]![MRBSearch]!
[Part Number], @Enter_Lot_Number = [Forms]![MRBSearch]![Lot Number],
@Enter_Keyword=[Forms]![MRBSearch]![Description of Issue]

My form is called MRBSearch and has the following boxes:
ProcessFound
Part Name
Part Number
Lot Number
Description of Issue

In the directions is does not state how the Form is linked to the query,
only
how the report is linked to the Form. For MDB the query would be, for
example:
Between [Forms]![Sales Dialog]![Beginning Date] and [Forms]![Sales
Dialog]!
[Ending Date], which links the query to the Form.

Any help I can get would be greatly appreciated.
Thanks

You don't say how you are calling the function in your report - including
how you are trying to pass the parameters - so I cannot tell you where
you
could have made an error. You should provide more details, including the
version of Access you are using.

Also, there have been many posts on this kind of subject in this
newsgroup
in the past, so I'm not sure why you didn't find anything appropriate to
your problem here.

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I
[quoted text clipped - 19 lines]

Thanks

 

[virginia via AccessMonster.com]

The reason for the parameter was to give the users the ability to search the
table by the items listed. I know that ctrl F will do the same thing, but was
trying to make it to where they could generate their own reports, so they
wouldn't have to depend on me fully to do this. I would give the ability to
the users to generate stored procedures and querries, but they are a bit
'computer challenged' and this could lead to an unwanted mess on my part.
Currently I am the only one that manipulates the database, there isn't anyone
else here that knows how to. I have several 'pre-set' reports to be printed,
but they don't always contain the information that they want.

The reason for the Like '%%' is because the user may not know the entire name
of the entry, so with this they can put in part of the name and retrieve the
information like that. If I don't use that, then the entry has to be exact to
what was typed in the search.

I tried using a semi-colon in the input parameter and that didn't work. Thank
you for all of your time and help. I don't feel so bad now that I couldn't
get it going, I was going by the directions, but this may not be a correct
route to go. I will keep searching and if you think of anything else to try,
please let me know.

Thanks!

Personally, if I remember correctly, I never been able to use parameters
with parameterised queries myself with Access 2000. I never tried with
A2002 or 2003.

Instead, I use stored procedures (SP) or I build the full query string. For
simple queries like your, creating the SP is pretty straighforward.

For controls, particularly when they are located on a subform, building the
query string is better than using parameters because this will greatly
diminushing the number of uncessary queries that Access is doing for
controls when you move from record to record or when you make changes or
requeries to controls.

Also, on some localised systems, you must the semi-comma ; instead of the
comma to separate your parameters for the InputParameter property.

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I must

[quoted text clipped - 88 lines]

 

[Sylvain Lafontaine]

Do you know how to create stored procedures on SQL-Server?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

The reason for the parameter was to give the users the ability to search
the
table by the items listed. I know that ctrl F will do the same thing, but
was
trying to make it to where they could generate their own reports, so they
wouldn't have to depend on me fully to do this. I would give the ability
to
the users to generate stored procedures and querries, but they are a bit
'computer challenged' and this could lead to an unwanted mess on my part.
Currently I am the only one that manipulates the database, there isn't
anyone
else here that knows how to. I have several 'pre-set' reports to be
printed,
but they don't always contain the information that they want.

The reason for the Like '%%' is because the user may not know the entire
name
of the entry, so with this they can put in part of the name and retrieve
the
information like that. If I don't use that, then the entry has to be exact
to
what was typed in the search.

I tried using a semi-colon in the input parameter and that didn't work.
Thank
you for all of your time and help. I don't feel so bad now that I couldn't
get it going, I was going by the directions, but this may not be a correct
route to go. I will keep searching and if you think of anything else to
try,
please let me know.

Thanks!

Personally, if I remember correctly, I never been able to use parameters
with parameterised queries myself with Access 2000. I never tried with
A2002 or 2003.

Instead, I use stored procedures (SP) or I build the full query string.
For
simple queries like your, creating the SP is pretty straighforward.

For controls, particularly when they are located on a subform, building
the
query string is better than using parameters because this will greatly
diminushing the number of uncessary queries that Access is doing for
controls when you move from record to record or when you make changes or
requeries to controls.

Also, on some localised systems, you must the semi-comma ; instead of the
comma to separate your parameters for the InputParameter property.

I am using Access 2003 and SQL 2000 in a server environment. When I try
to
set the input parameters on the report, it isn't working correctly. I
must

[quoted text clipped - 88 lines]

Thanks

 

[virginia via AccessMonster.com]

Yes, I can create stored procedures. I create these through my Access front-
end. I have several stored procedures that are driving my reports and even
some of the Forms. Some of my querries are in either a view or a function,
because they transported in from Access and were functional, so I left them
be as is. I have also found that some of the things I wanted to do in the
stored procedure, I had to do these in either a view or a function. Is there
something I am missing I should be doing with the sp that I am not doing?

Do you know how to create stored procedures on SQL-Server?

The reason for the parameter was to give the users the ability to search
the

[quoted text clipped - 54 lines]

 

[Sylvain Lafontaine]

Then I don't see why you don't rewrite the parameterised query

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part
Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE '%' + @Enter_Process_Found + '%') OR
([Part Name] LIKE '%' + @Enter_Part_Name + '%') OR
([Part Number] LIKE '%' + @Enter_Part_Number + '%') OR
([Lot (Serial) Number] LIKE '%' + @Enter_Lot_Number +
'%
') OR
([Description of Issue] LIKE '%' + @Enter_Keyword +
'%')


as a parameterised stored procedure instead:

Create Procedure dbo.sp1
(
@Enter_Process_Found varchar (255),
...
)
AS

if (@Enter_Process_Found is null OR @Enter_Process_Found = '')
Set @Enter_Process_Found = '%'
else
Set @Enter_Process_Found = '%' + @Enter_Process_Found + '%'

...

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part
Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE @Enter_Process_Found) OR
([Part Name] LIKE @Enter_Part_Name) OR
([Part Number] LIKE @Enter_Part_Number) OR
([Lot (Serial) Number] LIKE @Enter_Lot_Number) OR
([Description of Issue] LIKE @Enter_Keyword)


and use this SP in remplacement of the parameterised query as the record
source of your form or report.

A second possibility would be to dynamically build the query string and use
this query string as the record source.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Yes, I can create stored procedures. I create these through my Access
front-
end. I have several stored procedures that are driving my reports and even
some of the Forms. Some of my querries are in either a view or a function,
because they transported in from Access and were functional, so I left
them
be as is. I have also found that some of the things I wanted to do in the
stored procedure, I had to do these in either a view or a function. Is
there
something I am missing I should be doing with the sp that I am not doing?

Do you know how to create stored procedures on SQL-Server?

The reason for the parameter was to give the users the ability to search
the

[quoted text clipped - 54 lines]

Thanks

 

[Guest]

Virginia, I also had a similar problem. After a long time grappling with it
I found this article http://support.microsoft.com/kb/235359/EN-US/ in a post
in this group. I think it may help you.

 

[PFC Sadr]

then use fulltext search

The reason for the parameter was to give the users the ability to search the
table by the items listed. I know that ctrl F will do the same thing, but was
trying to make it to where they could generate their own reports, so they
wouldn't have to depend on me fully to do this. I would give the ability to
the users to generate stored procedures and querries, but they are a bit
'computer challenged' and this could lead to an unwanted mess on my part.
Currently I am the only one that manipulates the database, there isn't anyone
else here that knows how to. I have several 'pre-set' reports to be printed,
but they don't always contain the information that they want.

The reason for the Like '%%' is because the user may not know the entire name
of the entry, so with this they can put in part of the name and retrieve the
information like that. If I don't use that, then the entry has to be exact to
what was typed in the search.

I tried using a semi-colon in the input parameter and that didn't work. Thank
you for all of your time and help. I don't feel so bad now that I couldn't
get it going, I was going by the directions, but this may not be a correct
route to go. I will keep searching and if you think of anything else to try,
please let me know.

Thanks!

Personally, if I remember correctly, I never been able to use parameters
with parameterised queries myself with Access 2000. I never tried with
A2002 or 2003.

Instead, I use stored procedures (SP) or I build the full query string. For
simple queries like your, creating the SP is pretty straighforward.

For controls, particularly when they are located on a subform, building the
query string is better than using parameters because this will greatly
diminushing the number of uncessary queries that Access is doing for
controls when you move from record to record or when you make changes or
requeries to controls.

Also, on some localised systems, you must the semi-comma ; instead of the
comma to separate your parameters for the InputParameter property.

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I must

[quoted text clipped - 88 lines]

Thanks

 

[virginia via AccessMonster.com]

Thank you for the information. I have printed this out and will give it a try.
Thanks!

Virginia, I also had a similar problem. After a long time grappling with it
I found this article http://support.microsoft.com/kb/235359/EN-US/ in a post
in this group. I think it may help you.

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I try

[quoted text clipped - 12 lines]

Thanks

 

[msnews.microsoft.com]

hi virginio .... whats is you MSN

I am using Access 2003 and SQL 2000 in a server environment. When I try to
set the input parameters on the report, it isn't working correctly. I must
be
doing something wrong. I have been reading back through the postings and
am
not finding anything that I haven't tried.

It must be that I am not setting the input parameter correctly. I printed
out
directions from the Office Assistant and it said to do the following:

In ADP set the Input Parameters property in the main report:
Open the report, set the report's InputParameters property to a string
that
specifies the parameters that are passed to the stored procedure that the
report is bound to.

The string must be an expression that includes the parameters you
specified
in the stored procedure and the reference to the controls on the dialog
box:
@Beginning_date datetime = [Forms]![Sales Dialog]![Beginning Date],
@Ending_date datetime = [Forms]![Sales Dialog]![Ending Date].

Then you set the OnOpen to the macro that opens the form and the OnClose
to
the Macro that closes the Form.

I get an error that says:Bad Query Parameter @Enter_Process_Found (the
Form
does open).

Below is the query that I have, if you run the query it runs fine, but not
attaching it to the report:

SELECT dbo.[Material Review Board].*, [Part Name] AS Expr1, [Part
Number]
AS Expr2, [Lot (Serial) Number] AS Expr3, [Description of Issue] AS Expr4
FROM dbo.[Material Review Board]
WHERE ([Process Found] LIKE '%' + @Enter_Process_Found + '%') OR
([Part Name] LIKE '%' + @Enter_Part_Name + '%') OR
([Part Number] LIKE '%' + @Enter_Part_Number + '%')
OR
([Lot (Serial) Number] LIKE '%' + @Enter_Lot_Number +
'%
') OR
([Description of Issue] LIKE '%' + @Enter_Keyword +
'%')


The reason for the Like '%' + is because the user may not know the entire
name of the item to search for.

This is what I have in the InputParameter for the Report:

@Enter_Process_Found = [Forms]![MRBSearch]![ProcessFound],
@Enter_Part_Name =
[Forms]![MRBSearch]![Part Name], @Enter_Part_Number=[Forms]![MRBSearch]!
[Part Number], @Enter_Lot_Number = [Forms]![MRBSearch]![Lot Number],
@Enter_Keyword=[Forms]![MRBSearch]![Description of Issue]

My form is called MRBSearch and has the following boxes:
ProcessFound
Part Name
Part Number
Lot Number
Description of Issue

In the directions is does not state how the Form is linked to the query,
only
how the report is linked to the Form. For MDB the query would be, for
example:
Between [Forms]![Sales Dialog]![Beginning Date] and [Forms]![Sales
Dialog]!
[Ending Date], which links the query to the Form.

Any help I can get would be greatly appreciated.
Thanks

You don't say how you are calling the function in your report - including
how you are trying to pass the parameters - so I cannot tell you where you
could have made an error. You should provide more details, including the
version of Access you are using.

Also, there have been many posts on this kind of subject in this newsgroup
in the past, so I'm not sure why you didn't find anything appropriate to
your problem here.

I have a parameter query (function), set-up with an SQL backend and ADP
frontend, that searches a table and the function works fine, but when I

[quoted text clipped - 19 lines]

Thanks