-----Original Message-----
Quoting: « In the query I had language in the criteria column
"[Forms]![frmAcctSelectCard]![txtAcct1]" under AcctNbr and
"[Forms]![frmAcctSelectCard]![txtAmt1]" under Amt. »
From this statement, I'm not sure if you are really speaking about an
Access' ADP project or about a MDB file with linked tables. If you are
using a MDB file for accessing your SQL-Server, you should ask in a more
appropriate newsgroup. If you are using an ADP file, then you are at the
right place but you must know that you must learn how to program T-SQL on
the SQL-Server before using it. SQL-Server doesn't work the same as Access.
If by "query" in the above statement, you are speaking of a Stored Procedure
in design mode, then the above statement is partly wrong: the SP is running
on the SQL-Server and has no knowledge of things running on the Access ADP
form, including things like Forms!frmAcctSelectCard! txtAcct1. Probably the
Designer is interpreting these as string constants; like a name or anything
else. In a SP, parameters are designated with the symbol @ as a prefix; as
in the following exemple:
CREATE Procedure dbo.qsr_Calendrier
(
@ComboIdOrganisme int,
@ComboIdDiscipline int,
@ComboIdLigue int,
@ComboIdSection int,
@ComboIdEquipe int,
@ComboIdInstitution int,
@ComboDate1 datetime,
@ComboDate2 datetime,
@ComboNoMatch1 varchar (10),
@ComboNoMatch2 varchar (10),
@CheckRegulier smallint,
@CheckHorsLigue smallint,
@CheckEliminatoire smallint,
@ComboLimite smallint = 0,
@ModeAff_Eq_Inst int = 1, -- Choix de l'affichage du nom de l'équipe ou de
l'institution ou des deux.
@FrameAffichageDate int = 1
)
AS
SET ROWCOUNT @ComboLimite
SELECT
M.*,
L.Code as CodeLigue, L.Nom as NomLigue,
Case When E.Nom is Null Then M.NomHote_HL Else dbo.Nom_EquipeOuInstitution2
(E.IdEquipe, E.IdInstitution, @ModeAff_Eq_Inst) End as NomEquipe,
Case When E1.Nom is Null Then M.NomVisiteur_HL Else
dbo.Nom_EquipeOuInstitution2 (E1.IdEquipe, E1.IdInstitution,
@ModeAff_Eq_Inst) End as NomEquipe_1,
isNull (S.Nom, M.NomSite_HL) as NomSite,
dbo.FmtDateCompleteRapport (M.Jour, @FrameAffichageDate) AS DateComplete,
-- Les valeurs suivantes pour au tri pour les rapports Access.
-- Les matchs sans date viennent en dernier.
Case When M.Jour is Null then 1 Else 0 End as JourNumerique,
-- Les matchs sans heure viennent en dernier.
Case When M.Heure is Null then 1 Else 0 End as HeureNumerique,
-- NoMatchNumerique est inutile maintenant car NoMatch2 en tient compte; à
vérifier dans les rapports ASP.
Case When isNumeric (M.NoMatch) = 1 then 1 Else 0 End as NoMatchNumerique,
dbo.OrdreNoMatch (M.NoMatch) as NoMatch2
From dbo.Ligues L inner join
dbo.Matchs M on M.IdLigue = L.idLigue
left outer join dbo.Equipes E on E.IdEquipe = M.IdEqHote
left outer join dbo.Equipes E1 on M.IdEqVisiteur = E1.IdEquipe
left outer join dbo.Sites S on M.IdSite = S.IdSite
WHERE (@ComboIdOrganisme > 0 and L.IdOrganisme = @ComboIdOrganisme)
AND (@ComboIdDiscipline = 0 Or (@ComboIdDiscipline > 0 and
@ComboIdDiscipline = L.IdDiscipline))
AND (@ComboIdLigue = 0 Or @ComboIdLigue = L.IdLigue)
AND (@ComboIdSection = 0 Or @ComboIdSection = E.IdSection Or
@ComboIdSection = E1.IdSection)
AND (@ComboIdInstitution = 0 Or @ComboIdInstitution = E.IdInstitution Or
@ComboIdInstitution = E1.IdInstitution)
AND (@ComboIdEquipe = 0 Or @ComboIdEquipe = E.IdEquipe Or @ComboIdEquipe =
E1.IdEquipe)
AND (@ComboDate1 = dbo.CDate(0) Or @ComboDate1 <= M.Jour)
AND (@ComboDate2 = dbo.CDate(0) Or @ComboDate2 >= M.Jour)
AND (@ComboNoMatch1 = '' or @ComboNoMatch1 is Null or @ComboNoMatch1 = '0'
Or dbo.OrdreNoMatch (@ComboNoMatch1) <= dbo.OrdreNoMatch (M.NoMatch))
AND (@ComboNoMatch2 = '' or @ComboNoMatch2 is Null or @ComboNoMatch2 = '0'
Or dbo.OrdreNoMatch (@ComboNoMatch2) >= dbo.OrdreNoMatch (M.NoMatch))
AND ( (@CheckRegulier <> 0 and M.HorsLigue = 0 and M.Eliminatoire = 0)
OR (@CheckHorsLigue <> 0 and M.HorsLigue = 1)
OR (@CheckEliminatoire <> 0 and M.Eliminatoire = 1) )
ORDER BY
-- Les matchs sans date viennent en dernier.
Case When M.jour is Null Then 1 Else 0 End,
M.Jour,
-- Les matchs sans heure viennent en dernier.
Case When M.Heure is Null Then 1 Else 0 End,
M.Heure,
NoMatch2
GO
And the InputParameters string used in the report is:
@ComboIdOrganisme int = Forms!f_Rp!ComboIdOrganisme,
@ComboIdDiscipline int = Forms!f_Rp!ComboIdDiscipline,
@ComboIdLigue int = Forms!f_Rp!ComboIdLigue,
@ComboIdSection int = Forms!f_Rp!ComboIdSection,
@ComboIdEquipe int = Forms!f_Rp!ComboIdEquipe,
@ComboIdInstitution int = Forms!f_Rp!ComboIdInstitution,
@ComboDate1 datetime = Forms!f_Rp!ComboDate1,
@ComboDate2 datetime = Forms!f_Rp!ComboDate2,
@ComboNoMatch1 varchar(10) = Forms!f_Rp!ComboNoMatch1,
@ComboNoMatch2 varchar(10) = Forms!f_Rp!ComboNoMatch2,
@CheckRegulier smallint = Forms!f_Rp!CheckRegulier,
@CheckHorsLigue smallint = Forms!f_Rp!CheckHorsLigue,
@CheckEliminatoire smallint = Forms!f_Rp! CheckEliminatoire,
@ComboLimite smallint = Forms!f_Rp!ComboLimite,
@ModeAff_Eq_Inst int = Forms!f_Rp!ModeAff_Eq_Inst,
@FrameAffichageDate int = Forms!f_Rp!FrameAffichageDate
f_Rp is the form used to make the various selections and remains open while
the report is generated/displayed. This is a real exemple of a query used
as the source of a report in an ADP Project. Of course, you can put a lot
of things inside a SP; including adding values to a temporary table but in
your case, there is probably no need to create an append query.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
Sylvain,
Thanks for your reply.
I don't know how much detail to go into but I really need
some help. The more you know the better chances are I can
get an answer.
In Access the user opened a form where he could select
certain accounts to review. The form allowed the user to
select up to 15 accounts and threshold amounts to view in
a report at one time. The form has 2 combo boxes, one for
the account number and one for the amount to use as a
threshold for that account. The selections have to be
made by account and then amount. As each combo box is
updated, that value is passed to a text box. That is,
when an account is selected then txtAcct1 would populate.
Then he would need to select a threshold amount. That
would then populate txtAmt1. Successive selections would
go to their corresponding text boxes, txtAcct2, txtAmt2,
etc. When the user was done making his selections he
would click the Okay button. This would run an append
query and then open a report showing his selections. In
the query I had language in the criteria column "[Forms]!
[frmAcctSelectCard]![txtAcct1]" under AcctNbr and "[Forms]!
[frmAcctSelectCard]![txtAmt1]" under Amt. These go
through txtAcct15 and txtAmt15. So these values were
passed through to the query from the frmAcctSelect. This
is what I can no longer do and cannot figure out how to
get it to work.
I hope I have given enough information to get a mental
picture of what I did and what I'm now trying to do. If
you can help it would be wonderful.
-----Original Message-----
Try with the name of the SP as the Record Source; « dbo » as the Record
Source Qualifier (if dbo is the owner of this SP, of course) and set the
Input Parameters to the list of parameters that you want to pass:
@ComboDate datetime = Forms!f_StHb!ComboDate, @ComboIdOrganisme int =
Forms!f_StHb!ComboIdOrganisme, ...
Use the comma as the separator list (excerpt if you have set the Windows'
separator list on your system to something else) and use Shift-F2 for an
easier editing and make liberal use of the Refresh (F5) function of the View
menu for the Queries Window.
(Also, in the past, with the first versions of ADP, I was under the
impression that it was much better to write the parameters list by
alphabetical order of type (for example, datetime values before integer
values and integer values before smallint values); both for the SP and the
InputParameters. I don't know if it's really usefull to do this.)
Functions doesn't return a recordset, so you can't use them directly.
Usually, I set the Record Source to something like « Select * from Fct (v1,
v2, ...) » when I want to use a UDF. An automatic requery is done by Access
when you change the record source.
You can also use a view and set the Server Filter but that's another story.
I don't know (or understand) why you are moving your combobox values to a
text box.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
I have Access 2003. I recently installed SQL Server PE, i
think it's version 8. I have created a database in the
Server and have opened a project and all of my tbles are
available.
For over two weeks I have be trying to get things to work
similar to what my access db did. I've tried function
queries, storedprocedure queries, and views and I cannot
figure out how to pass a value from a form to the query.
Of course the old way was [Forms]![FormName]! [FormField].
What I do is, on a form, allow the user to make up to 15
account selections with a dollar threshold. Each account
and dollar amount are selected from their respective combo
box and that combo box value is then moved to a text box.
A query is then run that uses these values, through the
criteria field, to pull the data from an appropriate data
table. Then a report opens showing the user the results
of their search.
After all of this my question is how do I pass these
values to a query using a Project? I cannot seem to get
any of the queries to accept the values from the form.
Any help will be appreciated. I have read help topics
until my eyes have dried up.
Thanks
Ken
.
.
