Best method, lookups and bounded columns / SQL

[Petros]

I use many tables as Row Source / type for Combo boxes. All these tables consists of an unique identifier (ID) and two additional fields of Text Type.
What is to be considered the best practise, bounding column 1 when inserting a new record (a numeric value) and when displaying / editing include the Row Source table in the query...?
I am trying to figure out what would increase the performance of the database..
Thanks!

 

[gemma-the-husky]

i dont understand the question precisely

the key to efficient combo boxes is not to have too many possibilities. I would try and limit it to as small a figure as possibly.

eg 50 states is possibly reasonable
or in UK 122 post code main areas

much bigger, and the cbobox becomes unwieldy

 

[Petros]

Thank you gemma... I understand

 

[Galaxiom]

What is to be considered the best practise, bounding column 1 when inserting a new record (a numeric value) and when displaying / editing include the Row Source table in the query...?

If I understand the question it is aking whether the ID or the text column from the combo should be stored in the form's recordsource.

Definitely store the ID. Not only is it smaller to store and transmit than the text, it is much faster to query or join on an integer than text.

The control on the form or report handles the conversion to the displayed value.

Sometimes I will include the rowsource in a recordsource query used to display information that is not entered via a form. For example in a subform or subreport that displays a small number of change history records.

In this case there is no input form and the records are written when changes trigger VBA that writes a timestamp and UserID in a related table. It can sometimes be simpler to retrieve the username directly in the recordsource query than use a combo to convert the ID. I doubt there is much difference in performance so it comes down to a personal preference.