Split Form not displaying field in table until clicked.

[Torinjr]

Hi,

I have a split form that shows the Form and the Table it updates. The Table displays most of the data fine except for one field 'Item Description'. This field is updated using a cascaded combobox from the Form, the user selects an 'Item Type', which updates the 'Item Description' combo box. It looks like this, notice that the survey records item description is not displayed because a roading item is selected.





My first problem I have is that the Table will only display the text in the field when another item belonging to the same 'Item Type' is selected, when i change selection to a different 'Item Type' it then changes and shows all the 'Item Descriptions' belonging to item type currently selected. How do I make the table show all of the 'Item Description' all the time?

My second problem is a little more advanced and i wondered if anyone would know a possible solution. So what i want is after an incomplete record has been updated using the form and all fields now have some value i.e. 'Item Description' and 'Item Type' have been added to a record. Then i want that record to become invisible, i.e. if all fields have some value then the record in the Table split form view is no longer displayed.

[June7]

This is a known issue with cascading comboboxes with lookup alias on datasheet or continuous form. Since the RowSource is filtered, the alias values will not be available for display by records.

One approach is to filter the RowSource only for a new record or when the value in the primary control is changed in existing record. This means code would have to set the RowSource property with full SQL statement.

Another is to include the lookup table in the form RecordSource and display the alias value in a bound textbox set as Locked Yes and TabStop No to prevent editing.

[Torinjr]

Thanks June, I can't lock the text box because the user needs to be able to edit this. How would i go about the first approach, sorry im still coming to terms with access so I need a little more detail than usual?

[June7]

Where is the code you currently have that is causing the combobox to requery? Add code that sets the combobox RowSource property. Perhaps in the primary control AfterUpdate event set the property to filtered SQL statement. Something like:

Me.cbxDescription.RowSource = "SELECT ID, Description FROM tablename WHERE TypeID=" & Me.cbxType & ";"
Me.cbxDescription.Requery

Then in the cbxDescription AfterUpdate event, set the RowSource back to non-filtered SQL.
Me.cbxDescription.RowSource = "SELECT ID, Description FROM tablename;"
Me.cbxDescription.Requery


In the second approach the textbox is bound to field from the lookup table. User should not be able to edit that field on this form. It's only purpose is to display the description. User would still need combobox to select description.