Page 7 of 7 FirstFirst 1234567
Results 91 to 104 of 104

Combo Box filter value for a 2nd Combo Box

  1. #91
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    Steve is correct about this line:
    If x = True Then

    I changed it to:
    If x <> 0 Then
    ... and it does execute the Insert to the table.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  2. #92
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    It works! Thanks! The only problem now is getting the delete to work. I need to figure out something other than having it delete if x is null because I need it to delete if someone changes the agency to a different one...

  3. #93
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    I apologize for miss leading you on that expression. If instead you had written: "If x Then" it would have executed with any non zero value. Steve can verify this for us. Thanks Steve. Really!
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #94
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    No problem! Anyone have any ideas as to how I could handle the deletes when a different agency is selected in the cbo?

  5. #95
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    Anyone have any ideas as to how I could handle the deletes when a different agency is selected in the cbo?
    In your current code for the Combo573_AfterUpdate event, the delete SQL deletes the record for the NEW "AgencyID" (x) instead of deleting the record for the OLD "AgencyID". I'm thinking there wouldn't already be a record with the InternalIncidentID/AgencyID (because it is the NEW Agency)??



    So, for a given "InternalIncidentID", can there be multiple agencies?
    For example, if "InternalIncidentID" = 100, in tblAgencyIncident, can you have records:
    InternalIncidentID
    AgencyID
    100 4
    100 5

    ??

    I thought about using the OldValue property of the combo box, and compare the old "AgencyID" to the new "AgencyID", but the OldValue property is only available with bound combo boxes.
    The problem is that on the form/tab, you don't know which AgencyID is currently connected to the incident number because of the unbound combo box.

    But I don't think you need the "IF x<>0" test - if you are changing the "AgencyID" for an incident, wouldn't you delete any record with the old "AgencyID" for the incident number, THEN insert a record with the NEW "AgencyID" and incident number???

    So a delete, then an append (insert) should happen. (and have a message "Are you sure you want to do this" type of question before the delete/insert)


    Otherwise, you will have to use recordset or DLookup to get the current "AgencyID" for an incident and decide what to do.



    Just trying to understand the process.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #96
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    The way the process works is that an InternalIncidentID will only ever be associated with one AgencyID. My reasoning behind needing the delete functionality was if a user selected the wrong Agency from the combo box and needed to change it and select the correct one from the dropdown. There should never be two AgencyIDs for a single InternalIncidentID. Hopefully that clears it up a little! I should've clarified before, sorry about that

  7. #97
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    Since there will only be 1 record in "tblAgencyIncident" for a specific "InternalIncidentID",
    consider :
    Code:
    Private Sub Combo573_AfterUpdate()
        On Error GoTo Combo573Error
    
        '    Dim x As Integer
        Dim sSQL As String
    
        '    x = Me.Combo573
    
        'Delete all records for "InternalIncidentID" (should only be 1)
        sSQL = "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';"
        '    Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    
        'insert new record for "InternalIncidentID" and Agency
        sSQL = "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ('" & Me.InternalIncidentID & "', " & Me.Combo573 & ");"
        '    Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    
        '    MsgBox "The value of x is " & x
    
        'requery the subform
        Me.fsubAgencyIncident.Requery
    ExitCombo573:
        Exit Sub
    
    Combo573Error:
        MsgBox Err & ": " & vbCrLf & Err.Description
        Resume ExitCombo573
    
        '    Me.Combo579 = 0
        '    Me.Combo579.Requery
        '
        '    Me.Combo587 = 0
        '    Me.Combo587.Requery
        '
        '    Me.Requery
    
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #98
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thanks Steve! The code is definitely writing to the tables now. Just a couple of things:

    The code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly? Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.

    Also, when I clear out a combo box (say I needed to delete the record and leave the box blank, it deletes it in the underlying table after it errors out and says "syntax error in the INSERT INTO statement". Is there any way for the DELETE to happen when I clear the box out without having the error pop up?

    Thanks again!

  9. #99
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    The code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly?
    I don't understand


    Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.
    The form is bound to "tblIncidents". There are no Agency, Department and Program fields in that table. So those controls are unbound. You might be able to write code to display those values in the unbound combo boxes.



    Also, when I clear out a combo box (say I needed to delete the record and leave the box blank, it deletes it in the underlying table after it errors out and says "syntax error in the INSERT INTO statement". Is there any way for the DELETE to happen when I clear the box out without having the error pop up?
    Something like this?
    Code:
    Private Sub Combo573_AfterUpdate()
        On Error GoTo Combo573Error
    
        '    Dim x As Integer
        Dim sSQL As String
    
        '    x = Me.Combo573
    
        'Delete current record for "InternalIncidentID"
        sSQL = "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "';"
        '    Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    
        'check if an Agency has been selected
        If Not IsNull(Me.Combo573) Then
            'insert new record for "InternalIncidentID" and Agency
            sSQL = "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values ('" & Me.InternalIncidentID & "', " & Me.Combo573 & ");"
            '    Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        End If
    
        'requery the subform
        Me.fsubAgencyIncident.Requery
    
    
    ExitCombo573:
        Exit Sub
    
    Combo573Error:
        MsgBox Err & ": " & vbCrLf & Err.Description
        Resume ExitCombo573
    
        '    Me.Combo579 = 0
        '    Me.Combo579.Requery
        '
        '    Me.Combo587 = 0
        '    Me.Combo587.Requery
        '
        '    Me.Requery
    
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #100
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    The code writes but it won't update as I page through the different InternalIncidentIDs. Is it possible to get the code to do that so it will update accordingly?

    By that I mean that on my form, which displays the InternalIncidentID info, I can use arrows to access the Next Record and look at a different InternalIncidentID. As I do that, the values in Agency, Department, and Program should change accordingly to show the accurate info for each particular InternalIncidentID. Is there any way to do that? Basically it's related to my question about:

    Also when I open the form, it displays the first record in the InternalIncidentID table, but no values for the Agency, Department, or Program even if they're in the tables already.


  11. #101
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    Gak!! I thought I was down to my last brain cell.

    I was looking at the tab "Classifications" and couldn't understand how the check boxes were being changed because the check box controls are unbound.
    Found it finally!! It is being done in code in the form current event.


    So that is how you will have to change the combo boxes on the tab "568". Write code to set the combo boxes, just like the check boxes (in the form current event).



    ------------------------------------
    Couple of other things:
    EVERY code module should have
    Code:
    Option Compare Database
    Option Explicit
    as the top two lines.



    A rule I learned was:
    If you create it, destroy it.
    If you open it, close it.

    So in the sub "UpdateClassificationCheckBoxes" (and others), you create a reference to a database and a recordset. You open a recordset - but NOT the database.
    Instead of this
    Code:
        rs.Close
        db.Close
    you should use
    Code:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #102
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Below is the code I was using to update the check boxes when the InternalIncidentID changed. I know enough to change the table names, but being relatively new to Access how do I update the combo boxes when the code below was based on the check boxes being numbered 1 through 31 (the "For x = 1 to 31 part). I'm sorry to be helpless, but I'm in over me head for sure

    Code:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim x As Integer
        For x = 1 To 31
          Me.Controls("Chk" & x) = False
    Next x
    Set db = CurrentDb
    strSQL = "SELECT ClassificationID FROM tblIncidentClassifications WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "'"
    Set rs = db.OpenRecordset(strSQL)
    Do While Not rs.EOF
        Me.Controls("Chk" & rs!ClassificationID) = True
        rs.MoveNext
    Loop
    rs.Close
        Set rs = Nothing
        Set db = Nothing

  13. #103
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    Add this code:
    Code:
    Private Sub UpdateCombo573()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
    
        Me.Combo573.Value = Empty
    
        strSQL = "SELECT [tblAgencyIncident].[AgencyID]" 
        strSQL = strSQL & " FROM tblAgencyIncident"
        strSQL = strSQL & " WHERE tblAgencyIncident.InternalIncidentID = '" & Me.InternalIncidentID & "';"
        '        Debug.Print strSQL
        Set rs = CurrentDb.OpenRecordset(strSQL)
    
        If Not rs.BOF And Not rs.EOF Then
            Me.Combo573 = rs("AgencyID")
        End If
    
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    End Sub

    Add the line in BLUE to this sub:
    Code:
    Private Sub Form_Current()
        ' This code runs the check box updates as the InternalIncidentID changes '
        UpdateClassificationCheckBoxes
        UpdateActionCheckBoxes
        UpdateReferralCheckBoxes
        UpdateRecommendationCheckBoxes
        UpdateCombo573
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #104
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Great! Thank you so much! The code works great except in those circumstances where I select a value in each cbo (Agency, Department, Program) and the decide to change it to a different Agency (the top of the hierarchy). When that happens the cbos reset but the values for the department and program are still sitting in the tables. However, I need them to delete if that happens because changing the agency means that the other two values that depend on it are no longer valid and should be deleted. Am I trying to do too much in the code by trying to work that in too, or is that possible at all?

Page 7 of 7 FirstFirst 1234567
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  2. Replies: 4
    Last Post: 06-18-2014, 08:31 PM
  3. Combo box to filter a combo box
    By svrich in forum Access
    Replies: 20
    Last Post: 04-13-2014, 10:36 PM
  4. Replies: 1
    Last Post: 10-01-2013, 09:25 PM
  5. Combo Box filter help!
    By catat in forum Forms
    Replies: 1
    Last Post: 08-24-2010, 04:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums