refer to combo box from a query - please advise me

[Mitchell_Collen via AccessMonster.com]

Hi. I am new to sql server queries. i don't understand to create a parameter
query with the parameter as a combo box in a sql server linked database. this
database is an adp.

Here is what happens: in design view, i start typeing in Form!FormName!combo0
as a criteria for username. however, the access rewrites it like the criteria
is a string. (or at least that's what i think it is doing)

ALTER FUNCTION Pharmacy.FM_PX_USER_BYNAME
()
RETURNS TABLE
AS
RETURN ( SELECT Station, TxDatetime, Drawer, SubDrawer, Pkt, GenMedName,
BrandMedName, MedID, Amt, [Min], [Max], UserName, UserID, PtID, PaType
FROM dbo.phmPYXHx
WHERE (UserName = N'Forms!PX_UserReports!combo0.Form') )

--notice the N'Forms snippet... this is how access rewrites it after i type
the following any of the syntaxes in the criteria field in design view:

Forms![PX_UserReports]![combo0].Form
[Forms]![PX_UserReports]![combo0]
[Forms]!PX_UserReports!combo0.column(1)
[Forms]!PX_UserReports!combo0

thanks, MC

 

[Sylvain Lafontaine]

The SQL-Server is a different program running from Access and as such, has
no knowledge at all of things like Forms!PX_UserReports!combo0.Form who are
only known to Access (ADP). You must create your functions using the
standard syntax for parameters defined in T-SQL and dynamically build your
the string to be used as the record source for the combo box and you must
rebuild this string each time you want to requery the function using a
different parameter:

ALTER FUNCTION Pharmacy.FM_PX_USER_BYNAME
(@UserName nvarchar (255))
RETURNS TABLE
AS
RETURN ( SELECT Station, TxDatetime, Drawer, SubDrawer, Pkt, GenMedName,
BrandMedName, MedID, Amt, [Min], [Max], UserName, UserID, PtID, PaType
FROM dbo.phmPYXHx
WHERE (UserName = @UserName) )


and in Access:

Combo2.RowSource = "select * from Pharmacy.FM_PX_USER_BYNAME (" &
Forms!PX_UserReports!combo0 & ")"

Finally, using anything else than dbo. for your schemas might cause you a
lot of trouble in ADP.

 

[Mitchell_Collen via AccessMonster.com]

Hi Sylvain thanks for replying.

I don't understand the part where you said the I must build a string to be
used as the record source for the combo box (the reason is because the combo
box is populating already with another query record source)
this is what I will be doing: eventually I will use the user chosen combo
value to drive reports only. are you saying that when i set the parameter in
the function that is will only recognize strings, i understand that part, but
is there another way around this?

also will you clarify how to rebuild that string each time I requery the
function using a different parameter, i don't know exactly how to go about it,
but is there some way to hold the string value from the combo and send it to
the function when the report is ran?

I apologize for not understanding.

-MC

The SQL-Server is a different program running from Access and as such, has
no knowledge at all of things like Forms!PX_UserReports!combo0.Form who are
only known to Access (ADP). You must create your functions using the
standard syntax for parameters defined in T-SQL and dynamically build your
the string to be used as the record source for the combo box and you must
rebuild this string each time you want to requery the function using a
different parameter:

ALTER FUNCTION Pharmacy.FM_PX_USER_BYNAME
(@UserName nvarchar (255))
RETURNS TABLE
AS
RETURN ( SELECT Station, TxDatetime, Drawer, SubDrawer, Pkt, GenMedName,
BrandMedName, MedID, Amt, [Min], [Max], UserName, UserID, PtID, PaType
FROM dbo.phmPYXHx
WHERE (UserName = @UserName) )

and in Access:

Combo2.RowSource = "select * from Pharmacy.FM_PX_USER_BYNAME (" &
Forms!PX_UserReports!combo0 & ")"

Finally, using anything else than dbo. for your schemas might cause you a
lot of trouble in ADP.

Hi. I am new to sql server queries. i don't understand to create a
parameter

[quoted text clipped - 28 lines]

thanks, MC

 

[Sylvain Lafontaine]

My mention about repopulating the combobox is only a general comment about
how to call a function to populate a control; if your control is already
populated then of course, you don't have to repopulate it. Please disregard
this previous comment.

For your report, you can use the InputParameters property to pass values
from controls to a stored procedure (SP) used as the record source but this
will work only with SP and not with functions. You can take a look at the
previous messages in this forum (search for InputParameters on Google) to
get more details on this.

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

Hi Sylvain thanks for replying.

I don't understand the part where you said the I must build a string to be
used as the record source for the combo box (the reason is because the
combo
box is populating already with another query record source)
this is what I will be doing: eventually I will use the user chosen combo
value to drive reports only. are you saying that when i set the parameter
in
the function that is will only recognize strings, i understand that part,
but
is there another way around this?

also will you clarify how to rebuild that string each time I requery the
function using a different parameter, i don't know exactly how to go about
it,
but is there some way to hold the string value from the combo and send it
to
the function when the report is ran?

I apologize for not understanding.

-MC

The SQL-Server is a different program running from Access and as such, has
no knowledge at all of things like Forms!PX_UserReports!combo0.Form who
are
only known to Access (ADP). You must create your functions using the
standard syntax for parameters defined in T-SQL and dynamically build your
the string to be used as the record source for the combo box and you must
rebuild this string each time you want to requery the function using a
different parameter:

ALTER FUNCTION Pharmacy.FM_PX_USER_BYNAME
(@UserName nvarchar (255))
RETURNS TABLE
AS
RETURN ( SELECT Station, TxDatetime, Drawer, SubDrawer, Pkt,
GenMedName,
BrandMedName, MedID, Amt, [Min], [Max], UserName, UserID, PtID, PaType
FROM dbo.phmPYXHx
WHERE (UserName = @UserName) )

and in Access:

Combo2.RowSource = "select * from Pharmacy.FM_PX_USER_BYNAME (" &
Forms!PX_UserReports!combo0 & ")"

Finally, using anything else than dbo. for your schemas might cause you a
lot of trouble in ADP.

Hi. I am new to sql server queries. i don't understand to create a
parameter

[quoted text clipped - 28 lines]

thanks, MC

 

[Mitchell_Collen via AccessMonster.com]

Sylvain
Thanks for your advice. I will google Inputparmeters now.
-MC

My mention about repopulating the combobox is only a general comment about
how to call a function to populate a control; if your control is already
populated then of course, you don't have to repopulate it. Please disregard
this previous comment.

For your report, you can use the InputParameters property to pass values
from controls to a stored procedure (SP) used as the record source but this
will work only with SP and not with functions. You can take a look at the
previous messages in this forum (search for InputParameters on Google) to
get more details on this.

Hi Sylvain thanks for replying.

[quoted text clipped - 52 lines]