Combo boxes in Datasheet View

  • Thread starter Thread starter Wesley Appeldorn
  • Start date Start date
W

Wesley Appeldorn

I have a Subform
I have three combo boxes

1 Contact
2 Source
3 Subsource

I am able to get it to filter the source based on what is entered in the
contact field. Only problem is that it filters every record source field.

Say i have two records
Contact | Source | SubSource|
_______________________
Record 1| Order | Mag | Dec |
Record 2| Cont | | |

When i would select Cont in Record 2 for the contact Record 1 data would
desipear. How do i get it to stay when i requery source in record 2
Contact | Source | SubSource|
_______________________
Record 1| Order | | |
Record 2| Cont | Receipt | |
 
This is a limitation of combo boxes. There is only one RowSource for a
combo, not one for every row. That means that if you filter the combo in
such as way that other records don't have a matching value, and the visible
column is not the bound column, the combo in the other records goes blank.

Workarounds:
1. Don't hide the bound column.
If the combo is listing items from a simple lookup table, you may be able to
make the text field the primary key instead of an autonumber. This means the
bound column is visible in the subform. As a result, Access has the value to
display, even if it is not in the combo's RowSource.

2. Use a query as the RecordSource for the subform.
By including the combo's RowSource table in the RecordSource of your
subform, you can display the field from the lookup table in a text box in
your subform. You can position the text box on top of the combo, so it shows
in every row, but set its TabStop to No, and use its Got Focus event to
SetFocus to the combo. This actually brings the combo forward only in the
current record row.

With #2, you can run into problems with trying to add new records in the
subform if any of the fields in the lookup table have a Default Value
defined. Access tries to create a record with these values. Removing the
Default Value property from the table solves the problem. You can also work
around this by using a DLookup() as the Control Source for the text box
instead of a multi-table query as the RecordSource of the subform, but the
DLookup() seriously hinders performance.
 
Thanks. I took what you said and adapted it a bit. Instead of using fields
with number to identify the choices i took the autonumber out and just used
the actual text for the field. It works great. My rows are now staying they
way the should be even after the filter.

Thanks for your help.
 
Back
Top