VBA code not updated combo box as the record changes

[tbbrown32]

Following the guidelines to prevent cross posting inappropriately, I just want to be clear that I posted a sort of similar issue in another forum. The initial problem I had was sort of resolved, but as a result I am now running into another issue that I alluded to at the end of the other post but that is not resolved and has not been able to be resolved. I'll clarify the issue below, just wanted to point that out up front. The link is http://www.accessforums.net/showthread.php?t=63485&page=2

Details: I have 3 tables listed below.

tblClassificationTypes
ClassificationTypeID (PK)
ClassificationTypeName

tblClassifications
ClassificationID (PK)
Other fields...
ClassificationTypeID (FK)

tblIncidents
InternalIncidentID (PK)
Other fields...
ClassificationID (FK)

I have a form based on tblIncidents. On that form, I have 2 unbound combo boxes CmbType and CmbClass. CmbType displays the ClassificationTypeName and when a value is selected it requeries CmbClass to display the appropriate options. When a value is selected in CmbClass, the ClassificationID is written to tblIncidents. My issue is that when I scroll through Incident records CmbClass updates but CmbType will not update with the record change. I have tried the code below, but it does not seem to work. It runs on Form_Current (). I am open to any and all suggestions as I have been hitting me head against a wall trying to figure this out. The query in the code below returns nothing when it should return something.

Private Sub UpdateCombo700() ' Runs when the Agency combo box is updated and shows the existing record in the AgencyIncident table for that particular InternalIncidentID '
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim stringx As String
 
   Me.Combo700 = Empty
    
    
    ' I have tried this: stringx = "SELECT tblClassificationTypes.ClassificationTypeName FROM (tblClassificationTypes INNER JOIN tblClassifications ON tblClassificationTypes.ClassificationTypeID = tblClassifications.ClassificationTypeID) INNER JOIN tblIncidents ON tblClassifications.ClassificationID = tblIncidents.ClassificationID WHERE tblIncidents.InternalIncidentID = '" & Me.InternalIncidentID & "';"
    ' I have also tried this: stringx = "SELECT tblClassificationTypes.ClassificationTypeName FROM (tblClassificationTypes INNER JOIN tblClassifications ON tblClassificationTypes.[ClassificationTypeID] = tblClassifications.[ClassificationTypeID]) INNER JOIN tblIncidents ON tblClassifications.[ClassificationID] = tblIncidents.[ClassificationID] WHERE (((tblIncidents.InternalIncidentID)=[Forms]![frmNewMain]![InternalIncidentID]));"
    
 stringx = "SELECT tblClassificationTypes.ClassificationTypeName FROM tblClassificationTypes INNER JOIN tblClassifications ON tblClassificationTypes.ClassificationTypeID = tblClassifications.ClassificationTypeID WHERE tblClassifications.ClassificationID = " & Me.Combo698 & ";"
    
    Set rs = CurrentDb.OpenRecordset(stringx)
     If Not rs.BOF And Not rs.EOF Then
        Me.Combo700 = rs("ClassificationTypeName")
    End If
     rs.Close
    Set rs = Nothing
    Set db = Nothing
 End Sub

 

[Minty]

In your description you are referring to cmbType and cmbClass, but your code refers to combo700 . Is this just a typo/brain fade or the problem?

 

[Frothingslosh]

My first recommendation would be to go to the form's OnCurrent event and add

cmbType.Requery

Because combo-buttons are row-source based controls, that will force the combo box to requery without doing the same to the entire form. (Don't try that with a text box - if there's no 'Row Source' property, then the entire form is requeried instead.)

 

[Frothingslosh]

Or what Minty said. :-/

 

[tbbrown32]

Sort of a brain fade! I changed the name of the combo boxes in my description because I thought it might be easier for people who aren't familiar with my database to use for reference. The actual combo box names line up with what's in the code. They're combo700 and combo698 in reality. Combo698 updates and writes to the table, but combo700 is the one I can't get to update as the record changes. In reality I just made it more confusing...

 

[Frothingslosh]

Honestly, you should never stick with the default names. Your code will be FAR easier to understand - especially a year or two down the line - if all your objects have descriptive names instead of numbers.

Hell, using non-descriptive numbers instead of appropriate names makes initial design far, far harder than it needs to be.

 

[Minty]

Right - in that case I think you are over complicating things.
If I have grasped this correctly your second combo box row source should be being set to a filtered result of it's initial possible values (Based on the first combo box being updated) ?

I would revert you code to the correct names - as Frothy said - you've confused us already, and in 2 months time will confuse yourself.

 

[tbbrown32]

Yes, the second combo box is getting the filtered results, that part of it works correctly. It's the first combo box that fails to update as the InternalIncidentID changes. I'll update the code so that the names have a meaning instead of just using the default names.

 

[MarkK]

I have a form based on tblIncidents. On that form, I have 2 unbound combo boxes CmbType and CmbClass.

If the form is bound to tblIncidents, I would expect your cmbClass to be bound to the field ClassificationID. And since the Type is known to the Class, the Type could also appear in the RowSource of that combo, and then to show that Type on the form, you could change the cmbType combo to a textbox, txtType, and set the controlsource to something like...

=[cmbClass].[Column](3)

...which assumes your cmbClass has a RowSource like...

SELECT t1.ClassID, t1.ClassName, t2.ClassTypeID, t2.ClassTypeName
FROM tblClass As t1 INNER JOIN tblClassType As t2

...and you should be able to do all that without any code, and it's all bound directly to your data.

 

[tbbrown32]

Thanks MarkK, that solution should work with the exception of the fact that the users are asking for a dropdown instead of a textbox. CmbClass being bound to ClassificationID works great! I just need to find a way to keep CmbType as a dropdown, if that's possible.

 

[MarkK]

From the structure you posted, Type is determined by the Class and should not be editable in respect to an individual incident. If you edit the Type of a Class, it will be edited for all other incidents that bind to that Class, which is not really a sensible thing to do.

 

[tbbrown32]

That's correct that it should not be editable, which is why it is only for selection, not writing. I was trying to use Type to filter Class so that the user doesn't have to scroll through 75-100 choices to find the right one. Class is the only field that is written to a table, Type was just meant to help narrow the choices down by category and make them more manageable. Type is the parent table in the Type to Class relationship

 

[MarkK]

I'm sorry, I didn't realize that you are essential working on a selection tool, I thought it was more of a display problem as you move from record to record. Those are two very distinct problems.

The selection process you are talking about is called "cascading combos," and if you search using those terms you will find a ton of info. If you have questions after reading up on cascading combos, post back. The steps in that process are
1) selection of item in combo1 modifies the contents of combo2
2) selection of item in combo2 updates the data in the table.

For complex selection processes I like using modal popup forms as selection wizards, since they don't clutter my form, and they can be used in other places as your database grows.

Hope this helps,

 

[Frothingslosh]

Which is why way back in post #3 I suggested he start off by doing a Me.cmbType.Requery when OnCurrent fires before looking into anything more esoteric, since the problem is that a cascaded combo box containing pre-existing data isn't updating when the current record changes, which is an issue I've dealt with before.

 

[tbbrown32]

It is an issue with the combo box not updating. I am using cascading combo boxes, and they work just fine. The issue is that cmbtype does not update when the record changes. So yes, it is a selection, not a write but I do need the combo box to update when the record changes. Sorry for the confusion but that's the issue I need help resolving. Everything else works just fine.

 

[Frothingslosh]

It is an issue with the combo box not updating. I am using cascading combo boxes, and they work just fine. The issue is that cmbtype does not update when the record changes. So yes, it is a selection, not a write but I do need the combo box to update when the record changes. Sorry for the confusion but that's the issue I need help resolving. Everything else works just fine.

Have you even tried what I'm suggesting? I did, after all, specifically state that that is the solution to precisely the problem you have said several times now that you are encountering.

 

[tbbrown32]

Which is why way back in post #3 I suggested he start off by doing a Me.cmbType.Requery when OnCurrent fires before looking into anything more esoteric, since the problem is that a cascaded combo box containing pre-existing data isn't updating when the current record changes, which is an issue I've dealt with before.

Unfortunately, the requery OnCurrent doesn't update the combo box for me. I tried that, but to no avail.

 

[tbbrown32]

Have you even tried what I'm suggesting? I did, after all, specifically state that that is the solution to precisely the problem you have said several times now that you are encountering.

I have tried it several times before I ever posted on this forum, to no avail.

 

[Frothingslosh]

Unfortunately, the requery OnCurrent doesn't update the combo box for me. I tried that, but to no avail.

Well, that's definitely interesting. When I encountered the exact same issue, that was the solution. I'll look into it a bit further once I get home from work.

 

[tbbrown32]

It could be operator error on my part. I've been known to do that.