Re: ADP Help

You're right, the the SQL-Server doesn't know anything about «
Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the
client side only, inside the property "Input Parameters" of a form or report
and in association with a SQL-Server parameter for a stored procedure:

Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1, ..... "

And the stored procedure would be something like:

CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime)
AS
Select * from A where A.date1 = @ComboDate1
GO


You cannot use ADP if you don't know T-SQL. Besides SP, you can also use
Views and User Defined Functions (UDF) but you cannot pass them parameters
from ADP; so you must create the whole string as the record source (either
literaly or using the operator &), for example:

Me.RecordSource = "Select * from A where A.Date = '2005/01/01' "

or:
Me.RecordSource = "Select * from MyUDF ('2005/01/01') "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' "

or:
Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' "

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


"Randel Bjorkquist" <rbjorkquist@xxxxxxxxxxxxx> wrote in message
news:en2xRP46FHA.3388@xxxxxxxxxxxxxxxxxxxxxxx
> Hello Sylvain,
>
> Thanks for the information; I'll give it a try. However, I'm still a
> little bit confused here. Just to help clarify something here, I think I
> may need to give you a bit more information. The form I've been talking
> about has no data source assigned to it. The form is only used to create
> a simple employee selection form, selecting a single employee from the
> combo box, "cbo_Employee". I was using the check box "chk_Terminated" and
> combo box "cbo_Shifts" to filter down the number of employee names that
> were displayed/listed.
>
> Back to you suggestion. When I've try referencing a control on a from,
> like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying
> something like the SQL statement can not understand/contain the "!" and
> that I should set the parameter values another way. But my question is
> this, if all the SQL statements are on the SQL Server, how would the SQL
> statement know how to resolve the form referencing? How would it know
> what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make
> sense?
>
> I'm looking forward to your reply,
>
> Randel
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%23T3vcss6FHA.2608@xxxxxxxxxxxxxxxxxxxxxxx
>> If you are using parameterized queries for your reports, add the
>> following to the input parameters property:
>>
>> @chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated,
>> @cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts
>>
>> Replace the comma "," with whatever is good if you have set up the list
>> separator to something else in the regional settings of your computer
>> (this is often the semi-comma).
>>
>> You can also set up a Server Filter but I don't use them personally;
>> however, it might be a good solution in your case.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: http://cerbermail.com/?QugbLEWINF
>>
>>
>> "Randel Bjorkquist" <rbjorkquist@xxxxxxxxxxxxx> wrote in message
>> news:%23VqLzIs6FHA.2176@xxxxxxxxxxxxxxxxxxxxxxx
>>> Hey Joe,
>>>
>>> Thanks for the help. You put me onto the right path. I'm actually
>>> ended up putting the code in the OnEnter event of the Combo Box. I am
>>> now just starting to run into more issues with how my old queries were
>>> build to how they are now on the SQL Server.
>>>
>>> An example of what I mean is this. I have a parameter query that uses a
>>> Start and Stop date from the main form. I use that query as a base
>>> query for almost all of my reports. Access, if the query was written
>>> correct as shown below in the "WHERE" clause, would automatically fill
>>> in the parameters, but now I'll have to figure out a better/different
>>> way of doing this. If you have any ideas, I'll be more then happy to
>>> hear them.
>>>
>>> :
>>> WHERE
>>> [tbl_Employee].[Terminated] =
>>> [Forms]![frm_EmployeeSelector]![chk_Terminated]
>>> AND
>>> [tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts]
>>> :
>>>
>>> I hope you understand and again thank you for your help,
>>>
>>> Randel Bjorkquist
>>>
>>>
>>> "J. M. De Moor" <someone@xxxxxxxxxxxxx> wrote in message
>>> news:uOmv1Nk6FHA.1944@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Randel
>>>>
>>>> Assume the name of your employee pick list form field is cbxEmployees.
>>>> Add
>>>> this code to OnCurrent event handler for the form (the SQL is standard
>>>> MSSQL):
>>>>
>>>> Dim strEmplSource As String
>>>> strEmplSource = "SELECT " & _
>>>> " EmployeeID, " & _
>>>> " LastName + ', ' + FirstName + ' ' + MiddleInit AS
>>>> Employee " & _
>>>> " FROM tbl_Employee " & _
>>>> " WHERE Terminated = " &
>>>> [Forms]![frm_EmployeeSelector]![chk_Terminated] & _
>>>> " AND Shift = " &
>>>> [Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";"
>>>> cbxEmployees.RowSource = strEmplSource
>>>>
>>>> If either of these fields contain non-numeric data, simply add the
>>>> apostrophes to the constant, e.g.
>>>>
>>>> " AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] &
>>>> "';"
>>>>
>>>> HTH,
>>>> Joe De Moor
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server - Filter
    ... Happy to use a stored procedure but prefer to use a View that can be linked ... >> the sql server tables from access you are pretty much defeating the idea ... > many fewer bugs if they'd just used Access queries for the most part. ... > to query data from the back-end. ...
    (comp.databases.ms-access)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: Promt user for criteria ?
    ... >faster that a passthrough query from my front end which is access. ... I have read reports claiming that Access will fetch a complete linked ... (to a SQL Server table or view) ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
Loading