Should I have one separate table for each combobox source?

[hiccup]

I suspect I have made a beginners mistake in my very first Access database, but before I start this again from scratch, I would like to know if it is really a mistake, or if I might easily fix it somehow.

I have a form with several combo boxes.
All the source fields for those comboboxes are in one table. But of course every field has a different number of cells containing content. Now when I open a combobox in a form, the sequence of the cells are a mess, and there are lots of empty lines for many of them. (probably also because I reordered some fields in the source table)
I now have no clue if I can crop the combobox dropdown output somehow so that each combox only shows cells with content. (and preferably in a defined order)

Should I have started with creating one different table for every combobox, and not trying to put all the source fields in one table?

 

[pbaldy]

That's a bit of a debate, but I think most developers use a separate table for each type of data (I do). If you want to keep yours, you should be able to modify your combo row source to exclude empties, like:

SELECT FieldName
FROM TableName
WHERE FieldName Is Not Null

 

[hiccup]

Damn I feel like an idiot. I cannot even find where to set Select..., From..., Where..., that you mention.
I guess I'd better rebuild it with separate tables as you suggested.

Thnx pbaldy!

 

[pbaldy]

In the row source property of the combo. If it's just the table now, you can click on the ellipsis to the right and build a query.

 

[hiccup]

Ah, got it. I was looking for the literal terms you used.
I have now set: Criteria: 'Is Not Null' and it seems to be working fine now.
thnx again

 

[pbaldy]

No problem.

 

[Gasman]

I created a 'lookup' table for one of my databases. In it I had something like

ID
DataType
Data
SortOrder

and perhaps a few extra fields.

Then I would query on the DataType field that might contain data like 'Actions' and sort them according to the sort field. That way I could add more data, and insert that data when being presented by changing the sort order.?

Is that not best practice then.?

 

[pbaldy]

Like I said, it's a bit of a debate. If it works for you, stay with it.