LOOKUP combo box

[vichet]

Hi all;

before, i use only access, not adp.
i use two combo boxes, i.e. cmb1 and cmb2;
Recordsource fo cmb2 is based on the value selected of cmb1
example:

select something from mytable where myfield=cmb1.value

but, after i change it to adp. it does not work.

i try to use storeprocedure , but no result

help me with my example

thank

 

[Bernie Yaeger]

Hi Vichet,

Can we see some code? What language are you programming in?

Bernie Yaeger

 

[Sylvain Lafontaine]

SQL-Server doesn't know anything about your forms or their controls, so you
must set the combobox RecordSource to the proper SQL string by building it
with the concatenation of the proper value; something like:

cmb2.RecordSource = "select something from mytable where myfield=" &
cmb1.value

No need to make a requery, it will be done automatically after setting the
recordsource. If you want to use a parameterized stored procedure; you must
indicate the parameter value in the InputParameters field of the form; for
example:

Me.InputParameters = "@cmb1 int = Forms!MyForme1!cmb1"

Notice that in the later case, we don't have to concatenate the string with
the value; as this will be done automatically by Access. @cmb1 is the name
of the parameter in the stored procedure and doesn't need to be the same as
the control. You will have to call the requery command for the combobox when
necessary.

S. L.

 

[Gerald Aichholzer]

SQL-Server doesn't know anything about your forms or their controls, so you
must set the combobox RecordSource to the proper SQL string by building it
with the concatenation of the proper value; something like:

cmb2.RecordSource = "select something from mytable where myfield=" &
cmb1.value

No need to make a requery, it will be done automatically after setting the
recordsource. If you want to use a parameterized stored procedure; you must
indicate the parameter value in the InputParameters field of the form; for
example:

Me.InputParameters = "@cmb1 int = Forms!MyForme1!cmb1"

Notice that in the later case, we don't have to concatenate the string with
the value; as this will be done automatically by Access. @cmb1 is the name
of the parameter in the stored procedure and doesn't need to be the same as
the control. You will have to call the requery command for the combobox when
necessary.

Hi Sylvain,

you're right, but InputParameters exists for forms only. If the
combobox requires parameters you'll have to assign its recordsource
by code. Either the way you've suggested for cmb2 or if there
exists a SP:

cmb2.RecordSource = "EXEC spGetListCmb2 " & cmb1.value

Gerald

 

[Sylvain Lafontaine]

Hi Gerald,

The InputParameters for the form will works for the controls too but,
like everything else with ADP, you must be in your lucky day.

One easy way to make sure that the parameters will work is to use
exactly the same list of parameters for all stored procedures of a form. I
also take the precaution of sorting them by alphabetical order of type:
"datetime" before "int" and "int" before "smallint". This may look funny
but I have had the impression that A2000 was more stable when I was using
this scheme but I don't know if it's still of a significant value with
A2003.

Working with ADP is like beeing a pitcher for a basketball team: you
must have a lot of funny tics to make things work.

S. L.

 

[Gerald Aichholzer]

The InputParameters for the form will works for the controls too but,
like everything else with ADP, you must be in your lucky day.

Hi Sylvain,

I'm talking about the drop down part of a combobox. I don't see any
InputParameters in design time for this. Also usually you use a dif-
ferent table/view/SP for this than for the form, e.g. the form shows
all active project and the combobox should contain the responsible
employee. In this case the dropdown part of the combobox could show
all applicable (this is the parameter) employees.

One easy way to make sure that the parameters will work is to use
exactly the same list of parameters for all stored procedures of a form. I
also take the precaution of sorting them by alphabetical order of type:
"datetime" before "int" and "int" before "smallint". This may look funny
but I have had the impression that A2000 was more stable when I was using
this scheme but I don't know if it's still of a significant value with
A2003.

Working with ADP is like beeing a pitcher for a basketball team: you
must have a lot of funny tics to make things work.

this is correct, but in German newsgroups everybody recommends to
avoid A2000 and ADP and use A2002/A2003 insteead.

Gerald

 

[Sylvain Lafontaine]

Hi,

I'm talking about the drop down part of the combobox, too. These
controls don't have their own InputParameters but instead use the one from
the form.

For the RecordSource, I didn't say to use the same underlying SP but to
write them so that they have the same list parameters. Of course, you don't
have to use all of these parameters inside a SP as many of them can be left
unused. Don't know if it's still usefull to do this with A2003 but it
wasn't a bad idea with A2000.

S. L.

 

[Gerald Aichholzer]

I'm talking about the drop down part of the combobox, too. These
controls don't have their own InputParameters but instead use the one from
the form.

Hi Sylvain,

ok, I didn't know that it works this way, too.

For the RecordSource, I didn't say to use the same underlying SP but to
write them so that they have the same list parameters. Of course, you don't
have to use all of these parameters inside a SP as many of them can be left
unused. Don't know if it's still usefull to do this with A2003 but it
wasn't a bad idea with A2000.

The problem in my cases is that the form usually has no parameters,
but the comboboxes do or that both have different parameters.

Anyway thanx for your clarification,
Gerald