VBA code not updated combo box as the record changes

[tbbrown32]

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.

Just to further clarify, my SQL code for cmbType is below. I think the reason the requery doesn't work is due to the SQL I'm using. However, since it's a cascading combo box, I can't really alter the SQL query to get what I want I don't think. It needs to use this SQL to pull in all the choices initially so they can be selected, but the requery doesn't work because the combo box isn't associated with any particular InternalIncidentID to begin with.

SELECT tblClassificationTypes.ClassificationTypeID, tblClassificationTypes.ClassificationTypeName
FROM tblClassificationTypes;

 

[Frothingslosh]

Hmmm...okay, for some reason I was thinking Type queried off Class, not the reverse.

Okay, let's back up a step. Based on your original post, but cmbType and cmbClass are unbound. As you described it, cmbType is used simply to filter cmbClass, and the selection made in cmbClass is what is actually saved to a field on the record.

But then it sounds like you want them to act as if they ARE bound when you move to an existing record.

Is that correct? It can be done, but it's going to involve changing RowSource on the fly based on whether the record is new or existing.

 

[tbbrown32]

Yes that's basically it. I get my terminology mixed up, but the combo boxes started off unbound. CmbClass is now bound to ClassificationID, which seems to work fine and it updates from record to record as well as writing the FK to the Incidents table.

As you stated, cmbType is only used to filter cmbClass. So cmbClass functions fully, but the issue is that cmbType is unbound so it fails to change based on the record.

 

[Frothingslosh]

Then, since an unbound control isn't going to have a matching field in a record and cascading combo boxes are unidirectional, you basically have two options:

The easy one is to set it to NULL at each new record (which is what it sounds like it's doing anyway). The other would be to take the value in cmbClass, look up in the Class table the TypeID the selected ClassID is tied to, and then programmatically set the value of cmbType to that.

Sample:

    If IsNull(Me.cboClass.Value) Then
        Me.cboType.Value = Null
    Else
        Me.cboType.Value = DLookup("ClassificationTypeID", "tblClassifications", "[ClassificationID] = " & Me.cboClass.Value)
    End If

You would, of course, need to set the actual button names to whatever you're actually using. (I used the table and field names from your first post, and I'm assuming Long Integer for the ID fields.)

 

[tbbrown32]

I put the code in the OnCurrent event and it works perfect! Thanks for the assistance and the help with the code. I've spent a long time trying to figure this out, so this help is very much appreciated!