Let me ask this generically.  In an Access Project:

1) Can a Row Source of a Combo Box contain a stored procedure (Answer: Yes)

2) Can a Row Source of a Combo Box contain a stored procedure that has an

input parameter (Answer: Apparently No?)



I would love it if someone could tell me that the answer to #2 is Yes, and

tell me how to do it.



Thanks!

TA

































 -----Original Message-----

From:  Peter Kaufman [mailto:kaufman@l...]

Sent: Saturday, July 21, 2001 6:33 AM

To: Access

Subject: [access] RE: Access Project Combo Box



Yeah, I know what you mean. I missed a lot for a while too. But I meant the

cstr VBA function (change to string).



HTH,



Peter



> -----Original Message-----

> From: Tom Abel [mailto:tabel@u...]

> Sent: Friday, July 20, 2001 9:34 PM

> To: Access

> Subject: [access] RE: Access Project Combo Box

>

>

> Do you mean CStr(), the expression from Expression Builder?  I've

> never used

> it and the Help in Access 2000 doesn't give any help!

>

> Boy do I miss the Expression Builder.  I used it like crazy in forms and

> queries.  In Project it is gone (I think it popped up

> somewhere?)?  That was

> useful stuff, and I don't know what Microsoft thinks can replace it.

>

>

>  -----Original Message-----

> From:  Peter Kaufman [mailto:kaufman@l...]

> Sent: Friday, July 20, 2001 8:18 AM

> To: Access

> Subject: [access] RE: Access Project Combo Box

>

> Have you tried cstr?

>

> Peter

>

> > -----Original Message-----

> > From: Tom Abel [mailto:tabel@u...]

> > Sent: Thursday, July 19, 2001 9:13 PM

> > To: Access

> > Subject: [access] Access Project Combo Box

> >

> >

> > Hello List,

> > I am trying to make a combo box display different choices based on the

> > values of another control on the same form.

> > In Access I could do this fairly easily by placing the control

> > name directly

> > into the combo box query.  In and Access Project, it is a

> different story

> > because the front-end does not have access to back-end views.

> > One approach I tried: Use a stored procedure as the source of the

> > combo box.

> > I can place a stored procedure name in the row source in the

> > combo box, and

> > that works fine.  However if I want to pass it a parameter it

> appears that

> > the combo box is now not parsing the string correctly and not

> recognizing

> > that it is a stored procedure.

> > I've tried a number of formats for the row source string, but

> > nothing works:

> > dbo.spr_Division_Dropdown '0100'

> > dbo.spr_Division_Dropdown('0100')

> > dbo.spr_Division_Dropdown "0100"

> > Etc.

> >

> > Any ideas or better approaches to this?  Thanks!

> > TA

>

>

>

The answer, much to your delight, is Yes. The answer as to how depends

on where you are creating the stored procedure. Is it in Access or SQL?

Richard Lobel

Great!, but you've lost me.  Why is that an issue, the procedure is stored

in the SQL database.

Anyway, I believe I created it in SQL Enterprise Manager.

TA



 -----Original Message-----

From: 	Richard Lobel [mailto:richard@a...]

Sent:	Monday, July 23, 2001 2:53 PM

To:	Access

Subject:	[access] RE: Stored Procedures in Combo Boxes



The answer, much to your delight, is Yes. The answer as to how depends

on where you are creating the stored procedure. Is it in Access or SQL?

Richard Lobel

Yes, and yes (sort of).



You could open a recordset on the stored proc, build a value list

(semi-colon delimted string) from the fields, and make that the source of

the combo box. Unfortunately I don't have sample code to do that, but it

should not be hard to find some using a query which you could adapt to a

stored proc. Start with the Access help on value lists - there might be some

there.



HTH,



Peter



-----Original Message-----

From: Tom Abel [mailto:tabel@u...]

Sent: July 23, 2001 9:33 PM

To: Access

Subject: [access] Stored Procedures in Combo Boxes





Let me ask this generically.  In an Access Project:

1) Can a Row Source of a Combo Box contain a stored procedure (Answer: Yes)

2) Can a Row Source of a Combo Box contain a stored procedure that has an

input parameter (Answer: Apparently No?)



I would love it if someone could tell me that the answer to #2 is Yes, and

tell me how to do it.



Thanks!

TA

Ok, but I'm not sure if that would do it.  I think it's the same problem.

For the stored proc to know how to build a list from fields, it would have

to know something about the current record that I am viewing.  That would

mean passing a record key, or in my case, passing the value in another field

on the form.  But the problem I have found with the Row Source property of

the Combo Box is that it will not allow passed parameters.  Yes?, or did I

follow you right?

TA





 -----Original Message-----

From: 	Peter Kaufman [mailto:kaufman@l...]

Sent:	Tuesday, July 24, 2001 7:21 AM

To:	Access

Subject:	[access] RE: Stored Procedures in Combo Boxes



Yes, and yes (sort of).



You could open a recordset on the stored proc, build a value list

(semi-colon delimted string) from the fields, and make that the source of

the combo box. Unfortunately I don't have sample code to do that, but it

should not be hard to find some using a query which you could adapt to a

stored proc. Start with the Access help on value lists - there might be some

there.



HTH,



Peter



-----Original Message-----

From: Tom Abel [mailto:tabel@u...]

Sent: July 23, 2001 9:33 PM

To: Access

Subject: [access] Stored Procedures in Combo Boxes





Let me ask this generically.  In an Access Project:

1) Can a Row Source of a Combo Box contain a stored procedure (Answer: Yes)

2) Can a Row Source of a Combo Box contain a stored procedure that has an

input parameter (Answer: Apparently No?)



I would love it if someone could tell me that the answer to #2 is Yes, and

tell me how to do it.



Thanks!

TA

But the row source would not be a table. It would be a 'value list' which

you would build, then requery the combo box to repopulate it. The parameter

only comes into play to get the recordset to build the value list from.



Personally, I try to stay away from this kind of complexity, but at least

you know you have the option.



HTH,



Peter



-----Original Message-----

From: Tom Abel [mailto:tabel@u...]

Sent: July 24, 2001 7:53 PM

To: Access

Subject: [access] RE: Stored Procedures in Combo Boxes





Ok, but I'm not sure if that would do it.  I think it's the same problem.

For the stored proc to know how to build a list from fields, it would have

to know something about the current record that I am viewing.  That would

mean passing a record key, or in my case, passing the value in another field

on the form.  But the problem I have found with the Row Source property of

the Combo Box is that it will not allow passed parameters.  Yes?, or did I

follow you right?

TA





 -----Original Message-----

From:  Peter Kaufman [mailto:kaufman@l...]

Sent: Tuesday, July 24, 2001 7:21 AM

To: Access

Subject: [access] RE: Stored Procedures in Combo Boxes



Yes, and yes (sort of).



You could open a recordset on the stored proc, build a value list

(semi-colon delimted string) from the fields, and make that the source of

the combo box. Unfortunately I don't have sample code to do that, but it

should not be hard to find some using a query which you could adapt to a

stored proc. Start with the Access help on value lists - there might be some

there.



HTH,



Peter



-----Original Message-----

From: Tom Abel [mailto:tabel@u...]

Sent: July 23, 2001 9:33 PM

To: Access

Subject: [access] Stored Procedures in Combo Boxes





Let me ask this generically.  In an Access Project:

1) Can a Row Source of a Combo Box contain a stored procedure (Answer: Yes)

2) Can a Row Source of a Combo Box contain a stored procedure that has an

input parameter (Answer: Apparently No?)



I would love it if someone could tell me that the answer to #2 is Yes, and

tell me how to do it.



Thanks!

TA