Filtered Combo box and displaying items not in list.

[SpikeManZombie]

Hi everyone,
I really hope some friendly and knowledgeable person can help because I have
been wrestling with this for some time and it is driving me mad!

I am using MS Access 2003. I have a database (which I didn't build) that
allows a user to add records, which are assigned to a specific subject
(waste, water, soil etc). When browsing the records a user on the main menu
(1st form) is able to select what subject to browse. The resulting form then
automatically filters to display the records related to that subject.

Within each record is a combo box (drop down menu) that indicates the
sub-category e.g. For waste the sub-categories might include disposal of
waste, treatment of waste etc.

As time has gone on and more records added this list has grown, which we
have added to. However, for new records many of the original sub-categories
are now out of date. I want them to still be visible when browsing records,
but not available to a user when adding a new record.

I have got up to the point where my combo-box correctly filters the list for
just those I identify as 'in use' or 'not redundant' (I added another field
to the table where they are stored, which was a simple Yes/No field headed
'Redundant?'). Because it is limiting the list I found that it was blanking
out the older records, which have the now obselete sub-categories. To get
round this I had to select 'No' for the option 'Limit to List' in the combo
box properties. However, I can't seem to get it to display the words from the
table (column 3), only the ID numbers (column 2). Each time I try to get it
to do that I get a message stating that I can't set it Limit to List to no
because it is bound to the ListWidth property.

I can provide screenshots and further explanation if required, but hope
someone can help! I have a working knowledge of Access, but not too much.

Many thanks

 

[Klatuu]

As I understand it, you want to show obsolete sub categories in the combo,
but not allow them to be used for creating new records.

One way would to filter the combo's row source query depending on the
situation. To do this, you can use the Form Current event. At this time,
you have established a new record, but no data has been used. You know
whether the record is a new record or not by testing the form NewRecord
property.

It would be something like:

If Me.NewRecord Then
Me.cboSubCat.RowSource = "SELECT subCatID, subCatName, Redundant
FROM SomeTable;"
Else
Me.cboSubCat.RowSource = "SELECT subCatID, subCatName, Redundant
FROM SomeTable WHERE NOT Redundant;"
End If
Me.cboSubat.Requery