Cascading Combo Boxes Not Syncing With Data In Table

[VSCurtis]

Hi Guys,

I'm having a problem with a pair of combo boxes on my form. The second combo boxes values get filtered based on the selection made in the first combo box. The actual filtering does work, but when viewing the records the second combo box isn't synced and does not show the data unless I go back to the first combo and reselect it's value then the value in the second combo appears. I think I'm missing some small detail here. Here's the code from the first combo's after update procedure.

Private Sub cmbCardCategory_AfterUpdate()
cmbCardAttribute.RowSource = "SELECT lkpAttributes.[Attribute ID], lkpAttributes.Description FROM lkpAttributes INNER JOIN jctCategoryAttributes ON lkpAttributes.[Attribute ID] = jctCategoryAttributes.[Attribute ID] WHERE jctCategoryAttributes.[Category ID] = '" & cmbCardCategory & "' ORDER BY jctCategoryAttributes.[Category ID];"
cmbCardAttribute.Requery
CheckCardCategory
End Sub

The first combo is cmbCardCategories and the second combo is cmbCardAttribute. Any help you can provide is appreciated.

[CJ_London]

the reason is the second combo will only display values that are in the rowsource. If you change the value in the first combo then the second combo rowsource is changed.

the fix is to have a default original rowsource without a criteria for the second combo.

my own method is to assign the default original rowsource in the second combo onexit event and the filtered rowsource in the second combo onenter event (rather than the first combo after update event)

[VSCurtis]

I'm not sure I understand what you're saying. The second combo cannot have a default rowsource without criteria because they are cascading combos and the value selected in the first determines what values are displayed in the second combo.

[CJ_London]

perhaps you had better clarify what you mean by 'when viewing the records'

[VSCurtis]

While entering a record the cascading combos work as expected but when I scroll back through the records I have entered the second combo's value is not visible. But if I reselect the value in the first combo the second combo's correct value appears. The correct value is written to the database, but the second combo refuses to stay in sync. Hope that makes sense.

[andy49]

Use msgbox cmbcardcategory to confirm what value you're getting from the combobox. Then use that value in the query design mode to see if the query returns the values you want?


Sent from my iPhone using Tapatalk

[CJ_London]

records I have entered the second combo's value is not visible

in that case, in the form current event put cmbCardCategory_AfterUpdate to run the sub to repopulate the cmbCardAttribute rowsource with the relevant data for the current record

[Missinglinq]

I think you probably need to make sure that you're only doing this for an existing Record, to prevent a Null usage error, so something like this, per Ajax's suggestion:

Private Sub Form_Current()
  If Not Me.NewRecord Then
   Call cmbCardCategory_AfterUpdate
  End If
End Sub

Linq ;0)>

[June7]

The issue you describe is expected on continuous or datasheet form with cascading combobox using lookup alias. Applying filtered RowSource causes removed alias values to not be available for display. This affects ALL instances of the control because there is only the one control, regardless that you see it repeated on every record. One approach is to apply filtered RowSource only when changing value of the primary control and then to change back to unfiltered RowSource when leaving the dependent combobox. With Ajax's suggestion in post 2 doesn't matter if the primary combobox value is changed.

[VSCurtis]

All records are preexisting. The records were preloaded into the database because of the number of BLOBs in the database, 4 per record. I needed to see how big the database was going to be. No AddNew is executed by the program except for insertomg recprds into the junction table. So that check is poimtless

[June7]

Then don't use it. Reset the dependent combobox when the primary is changed and then set it back when leaving. The values will disappear briefly. Make sure the dependent combobox gets focus after the primary is changed.

[CJ_London]

have a look at this suggestion about cascading combos - https://www.access-programmers.co.uk...d.php?t=275155

[VSCurtis]

The only data in the table is the BLOB data. It was done that way so I would know exactly how big the database would get. Let me give you a rundown on my process so you can better understand. Tables were built and most relationship defined except for primary key and relationships for card table. All fields were marked as not required. BLOB data for all card images loaded into database. BLOB data extracted from database to ensure integrity of BLOB images. Primary table key field populated with temporary keys and remaining relationships defined. Data entry for loaded records begins. The field in question is in the primary cards table and not a junction table field so that's what makes this so strange. There shouldn't be any issues updating the field.

[CJ_London]

now really getting confused. You originally inferred the issue was with a single form when moving from one record to another, then it appears it is a continuous form. And now we are talking about tables and blob data. Perhaps it is time to start again, provide a screenshot of your form? table?

[VSCurtis]

Nevermind guys thanks for your efforts