Combo box control source in data sheet

[Gijs van Swaaij]

Hey,

In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25 groups.
The datasheet already displays a combobox in which the user can select
a group. Now I would like to have a second combo box display only those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet view.
It's not really an option to let the user select from 500 question in
the combobox, and it isn't really an option to have the form display in
regular view either.

Thanks,
Gijs van Swaaij

 

[Allen Browne]

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd field.
Consequently, when you restrict the combo's RowSource, it shows a complete
blank on many other rows of the datasheet, because those text values are not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes forward only
on the selected row, so the other rows still appear correct, even though the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains both
tables as the source for the form, so you can use a text field instead of 2
combos.)

 

[Gijs van Swaaij]

Thanks a lot for the quick response! Only one more quick question: how
do I get two columns to overlap properly? I've tried setting
columnwidth to its minimum value of 8, but that gives ugly thick bars
to the left and right. Setting ColumnHidden to true also doesn't seem
to be a good solution, as it seems to produce all sorts of bugs (such
as no longer being able to SetFocus() to a column, even after you've
set its ColumnHidden property to true). Any ideas?

Thanks again,
Gijs van Swaaij


Allen Browne schreef:

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd field.
Consequently, when you restrict the combo's RowSource, it shows a complete
blank on many other rows of the datasheet, because those text values are not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes forward only
on the selected row, so the other rows still appear correct, even though the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains both
tables as the source for the form, so you can use a text field instead of 2
combos.)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25 groups.
The datasheet already displays a combobox in which the user can select
a group. Now I would like to have a second combo box display only those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet view.
It's not really an option to let the user select from 500 question in
the combobox, and it isn't really an option to have the form display in
regular view either.

Thanks,
Gijs van Swaaij

 

[Allen Browne]

I'm not sure I understand the question.

Your RowSource statement indicates that the combo has 2 columns, so I expect
you would set these properties for both your combos:
Bound Column 1
Column Count 2
Column Widths 0";2"
List Width 2.2"
Top 0
Left (exactly the same for both combos.)

Since they have the same Left, Top, Width, and Height, the user can see only
one combo at a time. Since only one can be dropped down at a time, I don't
see any problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot for the quick response! Only one more quick question: how
do I get two columns to overlap properly? I've tried setting
columnwidth to its minimum value of 8, but that gives ugly thick bars
to the left and right. Setting ColumnHidden to true also doesn't seem
to be a good solution, as it seems to produce all sorts of bugs (such
as no longer being able to SetFocus() to a column, even after you've
set its ColumnHidden property to true). Any ideas?

Thanks again,
Gijs van Swaaij


Allen Browne schreef:

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd field.
Consequently, when you restrict the combo's RowSource, it shows a
complete
blank on many other rows of the datasheet, because those text values are
not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes forward
only
on the selected row, so the other rows still appear correct, even though
the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains both
tables as the source for the form, so you can use a text field instead of
2
combos.)

In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25 groups.
The datasheet already displays a combobox in which the user can select
a group. Now I would like to have a second combo box display only those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet view.
It's not really an option to let the user select from 500 question in
the combobox, and it isn't really an option to have the form display in
regular view either.

Thanks,
Gijs van Swaaij

 

[Gijs van Swaaij]

Thanks for the reply. The comboboxes do indeed have 2 columns, with one
hidden column. However, this form is displayed in Data Sheet view,
which is oblivious of the Left, Top, Width, and Height properties,
other than regular view (it's the view that's similar to the way tables
are displayed by default). I just tried your advice anyway, but
unfortunately it didn't work.

Gijs van Swaaij


Allen Browne schreef:

I'm not sure I understand the question.

Your RowSource statement indicates that the combo has 2 columns, so I expect
you would set these properties for both your combos:
Bound Column 1
Column Count 2
Column Widths 0";2"
List Width 2.2"
Top 0
Left (exactly the same for both combos.)

Since they have the same Left, Top, Width, and Height, the user can see only
one combo at a time. Since only one can be dropped down at a time, I don't
see any problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a lot for the quick response! Only one more quick question: how
do I get two columns to overlap properly? I've tried setting
columnwidth to its minimum value of 8, but that gives ugly thick bars
to the left and right. Setting ColumnHidden to true also doesn't seem
to be a good solution, as it seems to produce all sorts of bugs (such
as no longer being able to SetFocus() to a column, even after you've
set its ColumnHidden property to true). Any ideas?

Thanks again,
Gijs van Swaaij


Allen Browne schreef:

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd field.
Consequently, when you restrict the combo's RowSource, it shows a
complete
blank on many other rows of the datasheet, because those text values are
not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes forward
only
on the selected row, so the other rows still appear correct, even though
the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains both
tables as the source for the form, so you can use a text field instead of
2
combos.)


In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25 groups.
The datasheet already displays a combobox in which the user can select
a group. Now I would like to have a second combo box display only those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet view.
It's not really an option to let the user select from 500 question in
the combobox, and it isn't really an option to have the form display in
regular view either.

Thanks,
Gijs van Swaaij

 

[Allen Browne]

Yes, I see what you are saying.

This technique of the 2 combos on top of each other works in Continuous form
view, not Datasheet view.

Could you use Continuous view?

If not, you will have to use 1 combo, restrict its RowSource in its Enter
event, and then restore the unrestricted RowSource in its Exit event. The
other rows of the datasheet will go blank while the combo has focus only.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply. The comboboxes do indeed have 2 columns, with one
hidden column. However, this form is displayed in Data Sheet view,
which is oblivious of the Left, Top, Width, and Height properties,
other than regular view (it's the view that's similar to the way tables
are displayed by default). I just tried your advice anyway, but
unfortunately it didn't work.

Gijs van Swaaij


Allen Browne schreef:

I'm not sure I understand the question.

Your RowSource statement indicates that the combo has 2 columns, so I
expect
you would set these properties for both your combos:
Bound Column 1
Column Count 2
Column Widths 0";2"
List Width 2.2"
Top 0
Left (exactly the same for both combos.)

Since they have the same Left, Top, Width, and Height, the user can see
only
one combo at a time. Since only one can be dropped down at a time, I
don't
see any problem.

Thanks a lot for the quick response! Only one more quick question: how
do I get two columns to overlap properly? I've tried setting
columnwidth to its minimum value of 8, but that gives ugly thick bars
to the left and right. Setting ColumnHidden to true also doesn't seem
to be a good solution, as it seems to produce all sorts of bugs (such
as no longer being able to SetFocus() to a column, even after you've
set its ColumnHidden property to true). Any ideas?

Thanks again,
Gijs van Swaaij


Allen Browne schreef:

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd
field.
Consequently, when you restrict the combo's RowSource, it shows a
complete
blank on many other rows of the datasheet, because those text values
are
not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when
tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its
RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes
forward
only
on the selected row, so the other rows still appear correct, even
though
the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains
both
tables as the source for the form, so you can use a text field instead
of
2
combos.)


In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25
groups.
The datasheet already displays a combobox in which the user can
select
a group. Now I would like to have a second combo box display only
those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind
of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet
view.
It's not really an option to let the user select from 500 question
in
the combobox, and it isn't really an option to have the form display
in
regular view either.

Thanks,
Gijs van Swaaij

 

[Gijs van Swaaij]

Hmm, I'll have to think about that. Anyway, thanks a lot for your
incredibly fast and useful responses!


Allen Browne schreef:

Yes, I see what you are saying.

This technique of the 2 combos on top of each other works in Continuous form
view, not Datasheet view.

Could you use Continuous view?

If not, you will have to use 1 combo, restrict its RowSource in its Enter
event, and then restore the unrestricted RowSource in its Exit event. The
other rows of the datasheet will go blank while the combo has focus only.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply. The comboboxes do indeed have 2 columns, with one
hidden column. However, this form is displayed in Data Sheet view,
which is oblivious of the Left, Top, Width, and Height properties,
other than regular view (it's the view that's similar to the way tables
are displayed by default). I just tried your advice anyway, but
unfortunately it didn't work.

Gijs van Swaaij


Allen Browne schreef:

I'm not sure I understand the question.

Your RowSource statement indicates that the combo has 2 columns, so I
expect
you would set these properties for both your combos:
Bound Column 1
Column Count 2
Column Widths 0";2"
List Width 2.2"
Top 0
Left (exactly the same for both combos.)

Since they have the same Left, Top, Width, and Height, the user can see
only
one combo at a time. Since only one can be dropped down at a time, I
don't
see any problem.

Thanks a lot for the quick response! Only one more quick question: how
do I get two columns to overlap properly? I've tried setting
columnwidth to its minimum value of 8, but that gives ugly thick bars
to the left and right. Setting ColumnHidden to true also doesn't seem
to be a good solution, as it seems to produce all sorts of bugs (such
as no longer being able to SetFocus() to a column, even after you've
set its ColumnHidden property to true). Any ideas?

Thanks again,
Gijs van Swaaij


Allen Browne schreef:

Use 2 combos - one on top of the other.

I presume the combo is bound to the ID field, but displays the 2nd
field.
Consequently, when you restrict the combo's RowSource, it shows a
complete
blank on many other rows of the datasheet, because those text values
are
not
in the combo.

To solve this, use 2 combos:
a) One that does not have its RowSource restricted.
This one is on top, and shows the values on all rows of the datasheet.
In its Enter event, you SetFocus to the other one.
The 2nd one then jumps forward, and offers only the restricted list.
Its Tab Stop of #1 is set to No, so the user ends up in #2 when
tabbing
through the form.

b) Use the Enter event procedure of the 2nd combo to set its
RowSource.
Now, whenever the user enters the combo, only the restricted list is
available.
This is a quirk of datasheets--that the combo from behind comes
forward
only
on the selected row, so the other rows still appear correct, even
though
the
active combo has a restricted list.

And the user doesn't know there are actually 2 combos.

(There are other possibilities, such as using a query that contains
both
tables as the source for the form, so you can use a text field instead
of
2
combos.)


In my database, I have a data sheet in which the user gets to enter
certain numbers relating to questions. There are about 500 questions
from which they can choose, but they are divided into about 25
groups.
The datasheet already displays a combobox in which the user can
select
a group. Now I would like to have a second combo box display only
those
questions that are in the selected group. I have tried to set the
record source of that second combobox to the following, and it kind
of
works in normal view (although it doesn't update it, but that's not
relevant). However, it doesn't work in Datasheet view.

SELECT [Vraag].[ID], [Vraag].[omschrijving] FROM Vraag WHERE
((([Vraag].[groepID])=[Forms].[kengetallen].[Vraaggroep])) ORDER BY
[Vraag].[volgorde];

(vraag=question and vraaggroep = question group, it's Dutch).
Does anyone have ideas on how I can make this work in datasheet
view.
It's not really an option to let the user select from 500 question
in
the combobox, and it isn't really an option to have the form display
in
regular view either.

Thanks,
Gijs van Swaaij