trying to get a function to use data from a form

  • Thread starter Thread starter Russ V
  • Start date Start date
R

Russ V

Hi.

I'm pretty new to ADP but have been working in Access for many years.
I've published a database to an MDF file and can get a funtion to
search using the following code. The domain login is xxx'd.

SELECT [Unit Name], datadata, [Department Name], [Remedy Name],
[SAP Name], [Employee SAP], [SAP Status], [SAP Dept], [SAP Hire], [SAP
Term],
[IsTerm'd?]
FROM [xxx\xxx].[Nextel Subscribers Temp]
WHERE ([Unit Name] LIKE N'%' + @Search1 + N'%')

A pop-up appears when run. Typing in "russ" gives all of the people
that have russ in the name.

Now I want to change the parm @Search1 for the form data. The
following line of code does not work. I want the function to look at
the value in form MenuSearch and get the data in text0

WHERE ([Unit Name] LIKE N'%' + N'[forms]![menusearch]![text0]' +
N'%')

My goal is to build a web page that an individual can put in several
qualifiers and submit parms for lookup. I already have the web site
set up and the views work.

Thanks in advance
 
The (remote) SQL-Server has no notion of any local forms and the local JET
interface will ask for a parameter only if you use things like @Search1 and
not « [forms]![menusearch]![text0] ».

I'm not sure how you are calling your SQL string (locally or inside a stored
procedure) but something like this might be your ticket if you are buillding
your string locally with VBA code:

WHERE ([Unit Name] LIKE N'% " & [forms]![menusearch]![text0] & "%')

or:
WHERE ([Unit Name] LIKE N'%' + N'" & [forms]![menusearch]![text0] & "' +
N'%')

(BTW, none of these will work properly if there is any ' embedded inside the
value of text0. )

The usual way of doing this in ADP is to create a stored procedure (SP) with
parameters in SQL-Server, set the Record Source to the name of this SP, the
Record Source Qualifier to dbo and set the association between each
parameters and local controls in the Input Parameters property:

@Search1 nvarchar (255) = [forms]![menusearch]![text0], ....

For you web page, I don't know.
 
Hi Sylvain

I've tried your steps but I think I missed something.
I started a new form and sp and will over-communicate here
so I can properly explain what I'm doing.

The usual way of doing this in ADP is to create a stored procedure
(SP) with parameters in SQL-Server,
Click to expand...

I'm using desinger to "design new stored procedure" and here is the SQL
code when I do this.
I've removed my domain and userid

ALTER PROCEDURE [domain\userid].spNextelSearch
(@search1 nvarchar(255))
AS SELECT [Unit Name], [SAP Dept], [SAP Status], [Employee SAP]
FROM [domain\userid].[Nextel Subscribers Temp]
WHERE ([Unit Name] LIKE N'%' + @search1 + N'%')

This SP runs fine by itself. When run, a
dialog box pops up "Enter Parameter Value" and I type in rus
it returns 6 records


Focusing on the form...
This form is called NextelSearch. There isn't a subform
The textbox is Text0
The button to launch the sp is butRunNextelSearch
The code associated with that button is:

Private Sub butRunNextelSearch_Click()
Set search1 = [Forms]![NextelSearch]![Text0]
DoCmd.OpenStoredProcedure "spNextelSearch"
End Sub


-- (Open the form and select the form. open the properties window)
-- set the Record Source to the name of this SP
(I selected spNextelSearch)
-- the Record Source Qualifier to dbo
(I did this but when I selected the Record Source earlier, it put
in my domain\userid)
( I got an error when viewing the form so I put back the
domain\userid)
-- and set the association between each parameters and local controls
in the Input Parameters property:
( I put in @Search1 nvarchar (255) =
[forms]![NextelSearch]![text0] )

Now. What happens is that when I change to the form to 'view' mode
some network activity happens. The form is blank and doesn't show the
text box
or button i created.

My aim here was to open the form...put rus in the text box and launch
the sp to see the records.

What am I missing please.

Thanks.
 
you don't need to set the' input parameters' bullshit

if you have a sproc parameter that is looking for a parameter named
@txtPLU then you have a control named txtPLU you bind that to a vba
function and you can do whatever you need to do.

it's much much much better than mdb
 
There is no relation between the call to « DoCmd.OpenStoredProcedure
"spNextelSearch" » and your form. If you want to use a stored procedure for
your form, you must set its Record Source to its name and its Record Source
Qualifier to its owner:

Me.RecordSource = "spNextelSearch"
Me.RecordSourceQualifier = "[domain\userid]"

BTW, using anything but dbo. as the owner (for example [domain\userid]) will
give you a lot of trouble in ADP.

If you have parameters, the InputParamets must also be set and you want to
have an updatable form, then setting the UniqueTable and ResyncCommand is
not a bad idea either.

However, for parameters, instead of using the InputParameters property, you
can set the RecordSource to a string command:

Me.RecordSource = "exec spNextelSearch '" & Replace ([text0], "'", "''") &
"'"

Notice that you don't have to use the Replace function if you are using the
InputParameters property but that you have to use its fully qualified name
« [Forms]![NextelSearch]![Text0] ».

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Russ V said:
Hi Sylvain

I've tried your steps but I think I missed something.
I started a new form and sp and will over-communicate here
so I can properly explain what I'm doing.

The usual way of doing this in ADP is to create a stored procedure
(SP) with parameters in SQL-Server,
Click to expand...

I'm using desinger to "design new stored procedure" and here is the SQL
code when I do this.
I've removed my domain and userid

ALTER PROCEDURE [domain\userid].spNextelSearch
(@search1 nvarchar(255))
AS SELECT [Unit Name], [SAP Dept], [SAP Status], [Employee SAP]
FROM [domain\userid].[Nextel Subscribers Temp]
WHERE ([Unit Name] LIKE N'%' + @search1 + N'%')

This SP runs fine by itself. When run, a
dialog box pops up "Enter Parameter Value" and I type in rus
it returns 6 records


Focusing on the form...
This form is called NextelSearch. There isn't a subform
The textbox is Text0
The button to launch the sp is butRunNextelSearch
The code associated with that button is:

Private Sub butRunNextelSearch_Click()
Set search1 = [Forms]![NextelSearch]![Text0]
DoCmd.OpenStoredProcedure "spNextelSearch"
End Sub


-- (Open the form and select the form. open the properties window)
-- set the Record Source to the name of this SP
(I selected spNextelSearch)
-- the Record Source Qualifier to dbo
(I did this but when I selected the Record Source earlier, it put
in my domain\userid)
( I got an error when viewing the form so I put back the
domain\userid)
-- and set the association between each parameters and local controls
in the Input Parameters property:
( I put in @Search1 nvarchar (255) =
[forms]![NextelSearch]![text0] )

Now. What happens is that when I change to the form to 'view' mode
some network activity happens. The form is blank and doesn't show the
text box
or button i created.

My aim here was to open the form...put rus in the text box and launch
the sp to see the records.

What am I missing please.

Thanks.
Click to expand...
 
For the use of dbo, you must also create your stored procedure with dbo as
the owner:

ALTER PROCEDURE [dbo].spNextelSearch

instead of:

ALTER PROCEDURE [domain\userid].spNextelSearch


--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Russ V said:
Hi Sylvain

I've tried your steps but I think I missed something.
I started a new form and sp and will over-communicate here
so I can properly explain what I'm doing.

The usual way of doing this in ADP is to create a stored procedure
(SP) with parameters in SQL-Server,
Click to expand...

I'm using desinger to "design new stored procedure" and here is the SQL
code when I do this.
I've removed my domain and userid

ALTER PROCEDURE [domain\userid].spNextelSearch
(@search1 nvarchar(255))
AS SELECT [Unit Name], [SAP Dept], [SAP Status], [Employee SAP]
FROM [domain\userid].[Nextel Subscribers Temp]
WHERE ([Unit Name] LIKE N'%' + @search1 + N'%')

This SP runs fine by itself. When run, a
dialog box pops up "Enter Parameter Value" and I type in rus
it returns 6 records


Focusing on the form...
This form is called NextelSearch. There isn't a subform
The textbox is Text0
The button to launch the sp is butRunNextelSearch
The code associated with that button is:

Private Sub butRunNextelSearch_Click()
Set search1 = [Forms]![NextelSearch]![Text0]
DoCmd.OpenStoredProcedure "spNextelSearch"
End Sub


-- (Open the form and select the form. open the properties window)
-- set the Record Source to the name of this SP
(I selected spNextelSearch)
-- the Record Source Qualifier to dbo
(I did this but when I selected the Record Source earlier, it put
in my domain\userid)
( I got an error when viewing the form so I put back the
domain\userid)
-- and set the association between each parameters and local controls
in the Input Parameters property:
( I put in @Search1 nvarchar (255) =
[forms]![NextelSearch]![text0] )

Now. What happens is that when I change to the form to 'view' mode
some network activity happens. The form is blank and doesn't show the
text box
or button i created.

My aim here was to open the form...put rus in the text box and launch
the sp to see the records.

What am I missing please.

Thanks.
Click to expand...
 
yeah i agree only use dbo.

if you're a member of the db_owner role then objects you create will
default to that owner (dbo)
right??

i mean

i use sql authentication more than others; and i dont think that i've
ever been on a db server where im not sa

so i haven't had a problem with it
 
Back
Top