Limiting combo box rowsource and displaying them in continuous form

[B]

I know there are several ways to speed up combo boxes and form
loading. Most of the solutions leave rowsource of the combo box blank
and set the rowsource to a saved query or an SQL with a where clause
after users typed in one or several letters/digits.

My problem is as follows

Most of the time I need to display form in continuous format, that
means the combo box will appear in each record.
For example I have a form to let users view and input component mix of
a product.
The main form let users browse through existing product list and add
new product.
The subform (continuous form) then allows them to edit (and view) the
component structure of the currently selected product and to "build"
new products' structure by selecting the component (componentID is a
combobox)

The component list is now several thousands and growing. If I apply
the popular solutions to limit the rowsource of the combo, the form
will open up without info about component structure (because the combo
rowsources are blank). Also, after user start to type & select in a
record combo box , combo boxes in other records will become blank.

So the question is: Is there any way to speed up combo box, yet all
combo boxes in a continuous form still display their value?

Thanks in advance for any opinion
B

[Allen Browne]

Not really.

The combo only becomes blank on your continuous form if the bound column is
zero-width.

You should be able to load several thousand records into a combo.

If you are moving beyond that, consider changing the interface to a text
box, and check you got a match before accepting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"B" <bi**@myrealbox .com> wrote in message
news:4c******** *************** ***@posting.goo gle.com...

I know there are several ways to speed up combo boxes and form
loading. Most of the solutions leave rowsource of the combo box blank
and set the rowsource to a saved query or an SQL with a where clause
after users typed in one or several letters/digits.

My problem is as follows

Most of the time I need to display form in continuous format, that
means the combo box will appear in each record.
For example I have a form to let users view and input component mix of
a product.
The main form let users browse through existing product list and add
new product.
The subform (continuous form) then allows them to edit (and view) the
component structure of the currently selected product and to "build"
new products' structure by selecting the component (componentID is a
combobox)

The component list is now several thousands and growing. If I apply
the popular solutions to limit the rowsource of the combo, the form
will open up without info about component structure (because the combo
rowsources are blank). Also, after user start to type & select in a
record combo box , combo boxes in other records will become blank.

So the question is: Is there any way to speed up combo box, yet all
combo boxes in a continuous form still display their value?

Thanks in advance for any opinion
B

[B L]

Hi Allen

G'day

I thought we almost always need to set the bound column width = 0,
otherwise what users see in the form are only meaningless ID numbers??
(They want to see things like description of the component, name,
company, ...)

Can you elaborate more on using a textbox?

I can figure out like this:
- Put componentID in the subform as a textbox
- Make a command button to pop up a continuous form with all the
components
- When user find the wanted component and doubleclick on the record:
close that popup form (or hide only - to improve performamnce the next
time user use the form) and assign that componentID to the componentID
textbox

- But what about displaying meaningful text like component description?
How should I accomplish that without being awkward?

Thanks
B

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

Yes, it is common to have a zero-width bound column.
Because of the limitations in Access, I often don't though.
I use a meaningful text-based primary key and display it, specificially get
around this problem.

To display the description as well, create a query that contains both the
subform's table and the lookup table. (Use an outer join if the lookup field
can be Null). Add the Description from the lookup table to the query grid.
You can now display the description in a read-only text box beside the combo
where the user selects their item.

Remember that the blank combo problem only occurs when you have both a
limited RowSource and a hidden bound column. Avoiding either one solves the
problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"B L" <bi**@myrealbox .com> wrote in message
news:3f******** *************@n ews.frii.net...

I thought we almost always need to set the bound column width = 0,
otherwise what users see in the form are only meaningless ID numbers??
(They want to see things like description of the component, name,
company, ...)

Can you elaborate more on using a textbox?

I can figure out like this:
- Put componentID in the subform as a textbox
- Make a command button to pop up a continuous form with all the
components
- When user find the wanted component and doubleclick on the record:
close that popup form (or hide only - to improve performamnce the next
time user use the form) and assign that componentID to the componentID
textbox

- But what about displaying meaningful text like component description?
How should I accomplish that without being awkward?