Limit to list error

[stepone]

Hi folks,

I have searched for this issue, but cannot see it anywhere.

I am trying to set up combo boxes for a field in my main table. The Lookup values for the field are as follows ;

Display control : combo box
Row source type : Table/Query
Row source : tblDose
Bound column : 2
Column count : 2
Column widths : 0cm;3cm
Limit to list : No

The table design seems to save okay, but when I try to open the table I get an error message "Cant set the LimitToList property to No right now. The first visible column, which is determined by the Columnwidths property, isn't equal to the bound column. Adjust the columnwidths property first and then set the LimitToList property."

I have seen this error before and have never managed to figure out why I get it - to me all the settings look okay, and if I try to change anything I end up with the wrong column being bound.

Can anyone help ?

Cheers,
Peter.

 

[o1110010]

Display control : combo box
Row source type : Table/Query
Row source : tblDose
Bound column : 2
Column count : 2
Column widths : 0cm;3cm
Limit to list : No

Something you should try might be either (1) change the bound column to 1 and see what it's actually binding with or (2) change the column widths so they both are visible.

If you are only referencing one field, why even bother with making the first "invisible"? You can use a SELECT statement for the Row Source.

(Example) SELECT tblDose.fieldYouWant FROM tblDose

Write back with an update please. Good luck.

 

[Pat Hartman]

If this is a "lookup" table. Column1 should be the autonumber ID which is the primary key and Column2 should be the descriptive text. Column1 should be the bound column but its width should be 0 so that it is hidden. That way the combo shows the text field but stores the hidden key field.

 

[stepone]

If this is a "lookup" table. Column1 should be the autonumber ID which is the primary key and Column2 should be the descriptive text. Column1 should be the bound column but its width should be 0 so that it is hidden. That way the combo shows the text field but stores the hidden key field.

Thanks for all the replies. Column 1 is the Autonumber field, but its the value from column two that I want to store. I suppose my reason for this is that if the user is writing their own reports, queries etc, it will make more sense to them if they are looking for an actual dose e.g. "1 tablet" rather than a meaningless autonumber, e.g. "2".

Is this bad design for my database ? Should I be storing the ID number rather than the actual value ? I know I am wasting some storage space, but the database is not a large one.

By the way, I got round this by creating a query which pulled one column from the tblDose table and using that for my lookup.

Thanks again,
Peter.

 

[stepone]

If you are only referencing one field, why even bother with making the first "invisible"? You can use a SELECT statement for the Row Source.

(Example) SELECT tblDose.fieldYouWant FROM tblDose

Thanks for that - very useful - I did not know it could be done. The reason I am using an invisible first column is that the book I am learning from does it that way

Thanks,
Peter.

 

[Mile-O]

Column 1 is the Autonumber field, but its the value from column two that I want to store. I suppose my reason for this is that if the user is writing their own reports, queries etc, it will make more sense to them if they are looking for an actual dose e.g. "1 tablet" rather than a meaningless autonumber, e.g. "2".

You are going to let other users design reports? Why not design the reports yourself and they only have to open them and print them off?

Is this bad design for my database ? Should I be storing the ID number rather than the actual value ?

Yes. Yes. If the user selects from a form what they want, you can set a reference to the combobox within the query and open the report.

 

[Pat Hartman]

Is this bad design for my database ? Should I be storing the ID number rather than the actual value ? I know I am wasting some storage space, but the database is not a large one.

1. Yes.
2. Yes.
3. It is not a matter of space. It is a matter of not duplicating data.

It is not strictly necessary to have autonumber primary keys. It is better to have a primary key that will not change however, you can eliminate the autonumber on this lookup table and make the text field the primary key. You can (and should) still set up referential integrity between the lookup table and the related tables. This will prevent pepople from storing invalid values.

I would never allow a user to create or change objects in a shared database or even in his own copy of a common front end. If your users need to create their own objects, give them a totally separate database with linked tables. You do not want to be in a position of not being able to distribute a new front end because to do so would overlay user created objects.

 

[stepone]

1. Yes. 2. Yes. 3. It is not a matter of space. It is a matter of not duplicating data.

It is not strictly necessary to have autonumber primary keys. It is better to have a primary key that will not change however, you can eliminate the autonumber on this lookup table and make the text field the primary key. You can (and should) still set up referential integrity between the lookup table and the related tables. This will prevent pepople from storing invalid values.

Thanks to Pat and Mile-O-Phile for their answers. I am learning a lot about the process of putting one of these things together. It's been pretty hard as I'm learning from a book and it helps a lot to be able to 'chat' with you guys.

Is it really always necessary to set up a lookup table in this way ? In this example, the users were entering the Dose value manually into the field. Although in theory they could put any free text they like, there were 5 or 6 regular entries they were making, so I created a drop-down list with the entries in a lookup table so they could add new ones when they wanted. They can still, however, use free text, so I cannot set up referential integrity. Is this also bad design ? How would you go about changing it ?

One more thing Pat - I did set up the lookup table with no autonumber (just a single field which was also the primary key) but then the items appear in alphabetical order in the drop-down list - I wanted them to appear in a different order. What is the best way around that ?

Thanks again for all your help,
Peter.

 

[Pat Hartman]

Lookup tables are used for two things.
1. To ensure data integrity. If you want to ensure that only particular values may be stored, you would enforce referential integrity between the lookup table and its related tables.
2. To reduce keystrokes. In a short list where all the items can be seen when the list is opened, it doesn't matter what order the list is in. The user uses the mouse to select one of the choices. But, in a longer list or one where the user will be typing and does not want to remove his hands from the keyboard to use the mouse, order is very important. In this case, the visible field must be in alpha order. This is the only way that the fill as you type code built into the combobox process will work properly.

In order to control the sequence of rows presented by the combo, base the combo's RowSource on a query that includes an order by clause.