Rowsource - SP parameter based on form field value?

[T]

Hello,

Trying to set the property sheet rowsource of a combobox
using a stored procedure that contains parameters. If I
used fixed values for the parameters everything works
fine. Example: exec usp_SomeProc 9,'',12

I'd like to base one of the parameters using the value of
a field from a form without using VBA if possible.

Can some suggest a way to replace the following VBA into
the Rowsource of a property sheet?

me.cboTest.RowSource = "exec usp_SomeProc 9,''," &
Me.cboTest

Thanks for any suggestions!
T

 

[Dølle Døck]

Use usp_SomeProc as RowSource
The sp's parameters must be named as your controls in your form

A

 

[Guest]

Yeah - That's what I thought too. But I couldn't get it
to work using the control name.

Tried: exec usp_SomeProc 9,'',cboCombo

where cboCombo is the name of the control that contains a
numeric value.

I must be overlooking somthing.

-T

 

[Dølle Døck]

What I ment was:

An example
Create Procedure usp_SomeProc (parm1 INT, parm2 VARCHAR(5), cboCombo INT)
BEGIN
END

Then you got to have these controlnames in your form.
parm1 - Textbox with value 9
parm2 - Textbox with empty string
cboCombo

combo.RowSource = usp_SomeProc
Remarks : No parameters

That should do the trick.

Anders

 

[T]

Thanks Anders.

-----Original Message-----
What I ment was:

An example
Create Procedure usp_SomeProc (parm1 INT, parm2 VARCHAR (5), cboCombo INT)
BEGIN
END

Then you got to have these controlnames in your form.
parm1 - Textbox with value 9
parm2 - Textbox with empty string
cboCombo

combo.RowSource = usp_SomeProc
Remarks : No parameters

That should do the trick.

Anders




.

 

[alexcn]

I tried this and thankfully got the combobox to work but (in my
continuous form) it has the same value for the rowsource in every
record. Is there any way to have the value of the combo rowsource by
different for each record based on the value of another combobox in
that record?

Many thanks!

 

[Dølle Døck]

This is a problem with subforms in general.

The source of the combobox changes for all records.
I've solved this by adding a textbox for displaying the value from the
combobox and reduce the size of the combobox. If you do this right the
textbox will appear as a combobox. This way you will not get all the "blank"
fields when changing rowsources on current record.

Anders

 

[alexcn]

Thanks Anders.

Would it make a difference if I went back to the standard Access Combo
paramater lookup using the VBA qualified name of the feeding combobox
instead of a parameterised SP in my ADP? I understand your suggestion,
good call...

Alex

 

[alexcn]

Hi Anders,

I thought your suggestion was brilliant and hence tried but but have
run into similar problems. Having created a text box for each combo,
if I make it unbound and then set the value of this text box in the
OnChange event of the combo, it changes the value on every instance on
the continuous form, if I make it bound to the description column
value of the combo using the ControlSource property, then it inherits
the last value of the respective combo irrespective of where it is
changed in the continuous form.

Sorry to have to ask again?

Alex

 

[alexcn]

Hi Anders,

I thought your suggestion was brilliant and hence tried but but have
run into similar problems. Having created a text box for each combo,
if I make it unbound and then set the value of this text box in the
OnChange event of the combo, it changes the value on every instance on
the continuous form, if I make it bound to the description column
value of the combo using the ControlSource property, then it inherits
the last value of the respective combo irrespective of where it is
changed in the continuous form.

Sorry to have to ask again?

Alex

 

[alexcn]

Hi Anders,

I thought your suggestion was brilliant and hence tried but but have
run into similar problems. Having created a text box for each combo,
if I make it unbound and then set the value of this text box in the
OnChange event of the combo, it changes the value on every instance on
the continuous form, if I make it bound to the description column
value of the combo using the ControlSource property, then it inherits
the last value of the respective combo irrespective of where it is
changed in the continuous form.

Sorry to have to ask again?

Alex

 

[Dølle Døck]

Try adding the refereced tables in the recordsource for your subform and add
the decription columns. Then use these columns as controlsource for the
textboxes.

Anders