best way to create form with 30 dropdowns

[tagteam]

I have a user that wants a form that queries a table, brings back almost all the data and displays about 150 fields. of those 150 fields he wants to display about 50 of them as dropdown lists. I am wondering what is the best way to create this form so that it opens and operates at a reasonable speed. The database is a split database. The user will be working on the form in an access database on his local computer but accessing the data on the network server.


Thanks

[pbaldy]

Personally I'd probably try it with standard combos getting their selections from linked tables. The performance might be fine. If not, you can try deleting all the row sources (or perhaps just for the less likely to be used combos), then setting the row source for each combo in its got focus event.

[tagteam]

So rather than converting the field box to a combo box and setting the field value to be a query that looks at all the available options in that field you would break each of those fields out into separate tables and then just use the item id in the actual field and use a combo box to display the text description of the item?
That would give you about 50 small tables that you would basically just use as lookup tables.

Not sure if that is what you are saying or not.

Right now, i created the form, converted the field boxes to combo boxes and used the query window to create the dropdown values. that way I dont end up with a bunch of tables but it is very slow to open the form.

[June7]

If you pull values from the records in the data table, there won't be any choices until records are entered. This is something I do but the field is really a free text entry field. The combobox is an aid to the user if they want to use an existing entry (most are phrases, not just single words) but they are free to enter whatever they want. If you need to provide users with a standard set of items to choose from, use a lookup table or a Value List as the RowSource.

[Missinglinq]

What, exactly, is the purpose of this app[/B]? Most experienced developers will tell you that a Table with more than 25-30 Fields is probably a sign that the database is non-normalized. A single Table with 150 Fields is almost assuredly so!

Linq ;0)>

[tagteam]

Yes, I agree. However, the client wants to be able to use whichever of the 30 fields he wants as a search criteria so I dont really know another way to do it. It is a database for railcars and each railcar has about 100 different specs that they keep on file. Then they may want to search by all cars with spec 1 that also have spec 4 and spec5. Or they may want to search by spec7 and spec 10 as long as it is higher than spec3. the combinations are almost endless and i dont really know how to normalize for that.