Drop down limitations 1

[FoxGolfer]

I have been asked to create a simple order app that will draw customer names, products, etc. from other tables. My question: Is there a "practical" limitation for the number of records for a list box/combo box? The one table I have seen has approximately 40,000 records.
If there is a practical limitation, what's the alternative?
Thanks,

Tom

 

[RoyVidar]

Erm - I think you're somewhat above, the limitation, I mean - think about a user frenetically use an Access dropdown with 40 000 records ...

Joke aside, I've not used anything "that big", but check out this from Allen Brownee Combos with Tens of Thousands of Records

Roy-Vidar

 

[FoxGolfer]

Thanks, I'll check it out.

Tom

 

[FoxGolfer]

Roy-Vidar,
Your suggestion and the link to Allen Browne worked perfectly, with one exception. After I enter the first 3 characters, I am required to click on the down arrow of the combo box to view the selection. Is there a way to enter the first 3 characters and then hit the Enter key to view the choices?

Thanks,
Tom

 

[RoyVidar]

Perhaps something along the lines of my last suggestions here thread702-1148671 together with the approach you're using (i e on the count of three or more characters, use a .DropDown on it). I'm not sure, hovewer, how it works if the dropdown is open while you change rowsource, though. I think you'll need to test that (at least expect some flickering).

Roy-Vidar

 

[FoxGolfer]

Thanks.
I've added text AND a tool-tip to enter three characters and Alt DownArrow (didn't know about that). We'll see if they like that. Seems simple to use. If not, I can always use the other option.

Tom

 

[FoxGolfer]

Here's hoping you may have an answer for this one too.
It's similar but different. (??)
I have a combo box to identify different competitors. After selecting the competitor, the selected result creates the RecordSource for another combo box to identify the PartNo. I have this working to this point.

After the PartNo is selected, I need to find our PartNo in a CrossRef table which contains the competitor PartNo and our corresponding PartNo. With our PartNo, I can feed 5 text boxes with data from another table.

Any ideas? Too complicated?
Tom

 

[PHV]

Why not incorporate the CrossRef table in the RowSource of the PartNo's combo, populating hidden (0 width) columns you may retrieve with the Column property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Hi FoxGolfer ,
I use a combox with around 90.000 records (all the ports where you can load a container around the world in code used by Evergreen - a big container company). To my surprise, no problem at all - works fast and smooth! Off course scrolling through is no fun. But the user types in the first few letters of the harbour and the whole name or code of the harbour is put in the control.


Pampers -

you're neven too young to learn

 

[FoxGolfer]

PHV,
Ok, I incorporated the CrossRef and obtained our PartNo. That required a simple change to the CompPN RowSource. The PartNo gets filled in but I need to do more.
When I select the PartNo, I was hoping that the SQL could obtain fields from a different table.

Existing:
Private Sub cboBrand_AfterUpdate()
cboPartNo.RowSource = "SELECT CompPN, OurPN FROM CrossRef WHERE Brand = '" & Me!cboBrand.Value & "'"
End Sub

AND
Private Sub cboPartNo_Change()
Me.txtOurPN.Value = Me.cboPartNo.Column(1)
End Sub

I created a test query using QBE and it worked fine. My question is where do I put the SQL code? Since I'm filling text boxes with the column(x) values, there is no RecordSource to assign the SQL statement, like ...RecordSource = "Select .....

Thanks,
Tom