Combo Box will not update when record changes

[tbbrown32]

Hi everyone,

In my current database, I have a table named tblIncidents asthe table behind my main form. My main data entry form is based on the Incident#(the PK for tblIncidents). In most cases, all tables are related to this onevia associative tables to resolve the many to many relationship issue. However,I have one table that is one relationship further away from tblIncidents.Please see the attachment for the table layout.
I have a cboClassType that allows me to selectClassificationType which filters my next cboClassName and allows me to choose aClassificationName that falls under the selected type. As I scroll through myrecords, I can get cboClassName to update but not cboClassType because myrecords are based off of InternalIncidentID which is not present intblClassificationTypes.


I am at a loss how to get both cbos to update as I scrollthrough records based on tblIncidents. My current code that works for cboClassName is below. Is there a way I can adapt that or do a JOIN so that I can get cboClassType to update as the record changes? Any help would be much appreciated!!!

Private Sub UpdatecboClassName() ' Runs when the Classification combo box is updated and shows the existing record in the IncidentClassifications table for that particular InternalIncidentID '
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Me.cboClassName.Value = Empty
    strSQL = "SELECT [tblIncidentClassifications].[ClassificationID]"
    strSQL = strSQL & " FROM tblIncidentClassifications"
    strSQL = strSQL & " WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "';"
    '        Debug.Print strSQL
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If Not rs.BOF And Not rs.EOF Then
        Me.cboClassName = rs("ClassificationID")
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

[ranman256]

The combo box MUST be updated after the record changes.
either click the UPDATE ALL button on the toolbar,
or
refresh in code:
cboBox.requery

[tbbrown32]

When I go to a different record and click update all it doesn't refresh cboClassType, I think because there is no code telling Access that an InternalIncidentID and the corresponding ClassificationID are related through multiple tables to a specific ClassificationTypeID.

[davegri]

What's the rowsource for cboClassType?

[tbbrown32]

The rowsource is

SELECT tblClassificationTypes.ClassificationTypeID, tblClassificationTypes.ClassificationTypeName FROM tblClassificationTypes;

[davegri]

Private Sub UpdatecboClassName() ' Runs when the Classification combo box is updated and shows the existing record in the IncidentClassifications table for that particular InternalIncidentID '
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Me.cboClassName.Value = Empty
    strSQL = "SELECT [tblIncidentClassifications].[ClassificationID]"
    strSQL = strSQL & " FROM tblIncidentClassifications"
    strSQL = strSQL & " WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "';"
    '        Debug.Print strSQL
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If Not rs.BOF And Not rs.EOF Then
        Me.cboClassName = rs("ClassificationID")
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

First, try deleting all the red. Single quotes indicate a string, not a long or autonumber.

[davegri]

When scrolling thru records, the cboUpdate events do not fire.
In the form_current event, try calling sub UpdatecboClassName.

[tbbrown32]

I still am having the same issue. The code I posted works for cboClassName but not cboClassType because of the lack of a shared field based on InternalIncidentID. As all my forms are based primarily off of the InternalIncidentID, the ClassificationType table needs to be able to update, but I'm not sure how to go about structuring that code.

[John_G]

If you are not using a main form - sub form arrangement, then I think you need to do so. Your relationship diagram shows a one-to-many relationship between incident and IncidentClassification, which needs a main form - subform. Your combos would apply only to records in the subform.

[tbbrown32]

Thanks to everyone for the responses. I appreciate the assistance!