Results 1 to 7 of 7
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    2 comboboxes and their updates

    Hi,

    i have 2 combobox.
    Code :

    Code:
    Private Sub Combo_pesel_AfterUpdate()
    
       Dim SqlString As String
        Dim ComboSpolkaNr As Double
        
        Dim rst As Recordset
        
        If Nz(Combo_pesel, 0) = 0 Then
            GoTo Koniec
        End If
        
        ComboSpolkaNr = Combo_pesel.Column(0)
    
    
        SqlString = "SELECT DISTINCT QryPersonID.Person_ID, QryPersonID.Employee_ID_FK FROM QryPersonID WHERE LEN(QryPersonID.Person_ID)>1 AND [QryPersonID.Employee_ID_FK]= " &              Combo_pesel & ";"
        
        Combo_sap.RowSource = SqlString
        
    Koniec:
    End Sub
    after user choice one record In combobox_pesel below combobox_sap is updated.
    So If i have PESEL number 111 and and for them it is attached SAP_number 444SAP, 555SAP only these 2 values will be shown.

    It is working like here:

    https://www.youtube.com/watch?v=rdAqkg6bGT0

    When user will choose record from combobox_sap also code will run:

    Code:
    Private Sub Combo_sap_AfterUpdate()
    
        Dim SqlString As String
        Dim ComboSpolkaNr As Double
       
        Dim rst As Recordset
        
        If Nz(Combo_sap, 0) = 0 Then
            GoTo Koniec
        End If
        
         ComboSpolkaNr = Combo_sap.Column(1)
    
    
        SqlString = "SELECT DISTINCT QrySurnames.PESEL, QrySurnames.Imię_Nazwisko FROM QrySurnames WHERE LEN(QrySurnames.PESEL)>1 AND [QrySurnames.PESEL]= " & ComboSpolkaNr & ";"
        
        Combo_pesel.RowSource = SqlString
     
    Koniec:
    End Sub
    and it will be filter PESEL only for choosen record.

    What if user will want to have once again wide list of choices?


    He updated combobox_pesel, updated combobox_sap and now he has only filter values.

    How to get back to original state, original rowsource of combobox ?
    I have tried with events but i failed.

    Please help,
    Jacek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I would like to add that i can simple add button in order to set initial/orginal rowsource but i think in Access has to be another , better solution.

    Best Wishes,
    Jacek

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Code:
        If Nz(Combo_pesel, 0) = 0 Then
    ..set both combobox's row source to what they were when the form opened
        Else
    ...set the row source with new criteria (as above)
    I would think that you need to requery each combobox after you have updated their row source, but you say this is working?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In your test for the combo containing something, add your code to reset if it doesn't.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Guys,

    yes it is working without requering.

    pbaldy wthank you, but should i do it using what event ?
    I tried with MouseDown Event but sometimes is working, sometimes not...

    Jacek

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    aytee gave you the code above, same event you're already using.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Okey thank you Guys !

    I think i will handle with it

    Best Wishes
    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2016, 12:16 PM
  2. Validation of the comboboxes
    By dr223 in forum Access
    Replies: 1
    Last Post: 01-13-2012, 07:31 AM
  3. Cascade of 3 or more ComboBoxes
    By tomullus in forum Forms
    Replies: 4
    Last Post: 10-08-2011, 06:22 PM
  4. Comboboxes go where?
    By PaulCW in forum Database Design
    Replies: 12
    Last Post: 10-04-2011, 02:34 AM
  5. Code for two comboBoxes
    By t_dot in forum Forms
    Replies: 3
    Last Post: 08-24-2010, 10:20 AM

Posting Permissions

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