Cascading 2 Comboxes from Another (1 Viewer)

[skilche1]

I am trying to get get 2 comboxes to refresh from one when cascading. The first combobox (Location) works, but the second one (Catches) doesn't. All I get are the Key values of the catches rather than the values...

I am wondering if it my relationship set up of if my VBA needs to be altered to accommodate the requering the two comboboxes.

Can anyone assist?

Thanks

VBA

Private Sub cboCategory_AfterUpdate()
    Dim sCatchLocation As String
    Dim sCatch As String
        
    sCatchLocation = "SELECT [tblLocation].[Location_ID], [tblLocation].[LocationCategory_ID], [tblLocation].[Location] " & _
                       "FROM tblLocation " & _
                       "WHERE [tblLocation].[LocationCategory_ID] = " & Me.cboCategory.Value
    
    Me.cboLocation.RowSource = sCatchLocation
    Me.cboLocation.Requery
    Me.Refresh
    
    'Seperation from Location
       
    sCatch = "SELECT [tblCatch].[Catch_ID], [tblCatch].[LocationCategory_ID], [tblCatch].[Catch] " & _
                        "FROM tblCatch " & _
                        "WHERE [tblCatch].[LocationCategory_ID] = " & Me.cboCategory.Value
    
    Me.CboCatches.RowSource = sCatch
    Me.CboCatches.Requery
    'Me.cboCatches.Visible = False
    Me.Refresh
    
   
End Sub

 

[JHB]

Compare the two comboboxes property.
"Column Counts" and "Column Widths"!

 

[skilche1]

Compare the two comboboxes property.
"Column Counts" and "Column Widths"!

Yes, I have already tried all that...

Here is the weird thing, if I select the Catch (key value), close out of the form, then reopen it, the value appears. The value goes away and the key reappears upon Requery when I select a value from the Location Category combobox...

 

[JHB]

Then I think you've some code running in association with the requery.
Could you post your database with some sample data + name of the form in which you've the problem.

 

[skilche1]

Could you post your database with some sample data + name of the form in which you've the problem.

Sure thing, here it is. AS far as data, there is nothing sensitive in it.

Thanks for taking a look.

Steve

 

[JHB]

The problem is you are using lookup field type in the table "tblCatch" which you are not in the table "tblLocation", (look at the attached picture)!
Another inappropriateness is that the two comboboxes are set for 3 columns but when you open the form, you only select data for 2 columns.
Below is the rowsource when the form open.

SELECT Location_ID, Location FROM tblLocation ORDER BY Location;

SELECT Catch_ID, Catch FROM tblCatch ORDER BY Catch;

Then you don't need to set the rowsource for the comboboxes in code, you can put it in directly from start and only requery the comboboxes in the "cboCategory_AfterUpdate"
Below is an example:

SELECT [tblLocation].[Location_ID], [tblLocation].[LocationCategory_ID], [tblLocation].[Location] FROM tblLocation WHERE [tblLocation].[LocationCategory_ID] = cboCategory;

 

[skilche1]

Good morning,

I forgot to mention which form I am using to input data, which is frmQCatch, hope you were viewing that one.

The problem is you are using lookup field type in the table "tblCatch" which you are not in the table "tblLocation", (look at the attached picture)!

The reason I have it set up this way is because I have tblCatches with the catches listed and using it to select catches for the Location Category (tblLocationCategory) as there are catches that can be caught in more than one category.

Another inappropriateness is that the two comboboxes are set for 3 columns but when you open the form, you only select data for 2 columns.

The reason why the Location combobox column count is set to 3 is because for some reason when I set it for column 2, I get the product key. Not sure why that is happening, never seen that before, so I left it

As for the Catches combobox, I have that set up for 3 because in the SQL, I am also referring to the tblCathes table as it has the LocationCategory ID that I need for the catches to relate to as this is what I am relating the catches to, not my Location.

Below is the rowsource when the form open.
Then you don't need to set the rowsource for the comboboxes in code, you can put it in directly from start and only requery the comboboxes in the "cboCategory_AfterUpdate"
Below is an example:

As for this this code, I am not having any issues selecting by Location unless I change the column count.

Thoughts?

 

[jdraw]

Do not use lookups at the table field level as JHB has said.
see these free videos for cascading combo setup and demo
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

These are older msaccess 2003, but concept is exactly the same for newer versions.

 

[skilche1]

Do not use lookups at the table field level as JHB has said.
see these free videos for cascading combo setup and demo

Which is why I had the tblecatches included in with the SQL for the Catches combobox. Not sure how else to include the LocationCategoryID and "true" values from the tblCatches table.

 

[skilche1]

OK, I found a work around. What I did was to build queries for both Location and Causes comboboxes, everything works now.

Thanks for everyone's help.