none
set combobox to null RRS feed

  • Question

  • Hi All,

    I am at my whits end with this issue, I have set up my form to filter available options depending on the results in a combobox however I cant get the options to be invisible before the options are entered which is frustrating.

    One issue is caused by the number of samples to be collected which ranges from 1-4 with a drop down box for each sample, so one box is available for 1 sample, 2 for 2, 3 for 3 and 4 for 4.

    Can anyone help me find a way of getting the combo to filter as null upon form load?

    Kind Regards

    Richard

    Sunday, July 8, 2018 7:31 PM

Answers

  • Hi R.Nock

    I'm not entirely sure whether I got your initial question right.

    What I understood is:

    • On a form you have a Combobox and at least 4 other controls (you call them options, named SampleType1..4)
    • Upon opening the form you want to hide the SampleType1-4 controls
    • Depending on the users choice you want to show SampleType1 - SampleType4 

    Is this correct?

    If so, below you'll find some code that does exactly that. You can try it out by adding a Combobox to your form and make sure you'll have at least 4 options to select from. Then drop 4 additional controls (I used Textboxes, but feel free to use whatever you want) and make sure to match the names of those controls to the names in Sub ShowHideOptions(). 

    Private Sub Form_Load()
        cboNumber_of_Collections = Null
        ShowHideOptions 0
    End Sub
    
    Private Sub cboNumber_of_Collections_AfterUpdate()
        ShowHideOptions cboNumber_of_Collections.ListIndex + 1
    End Sub
    
    Private Sub ShowHideOptions(lOptionsToShow As Long)
        Dim colOptions As New Collection
        Dim i As Long
        
        With colOptions
            .Add txtSampleType1
            .Add txtSampleType2
            .Add txtSampleType3
            .Add txtSampleType4
            For i = 1 To .Count
                .Item(i).Visible = (i <= lOptionsToShow)
            Next i
        End With
    End Sub


    • Edited by papa_bear Monday, July 9, 2018 12:03 PM typos
    • Proposed as answer by papa_bear Monday, July 9, 2018 12:08 PM
    • Marked as answer by R.Nock Monday, July 9, 2018 4:37 PM
    Monday, July 9, 2018 12:02 PM

All replies

  • Since these are comboboxes for selecting filter options, they should be unbound, meaning nothing in their ControlSource property.

    Then they will be blank when the form opens.

    To programmatically set them to blank (e.g. after clicking "Clear Filters"), you write:
    Me.myCombobox = Null

    Again, this is for UNBOUND scenarios only.


    -Tom. Microsoft Access MVP


    Sunday, July 8, 2018 8:27 PM
  • Hi,

    The combobox is unbound but it still isnt working even with the above coding.

    Kind Regards

    Richard

    Sunday, July 8, 2018 9:03 PM
  • Then you will want to make a copy of your database, strip it down to the bare minimum to show the problem, remove any PII and post it in a public place like a free OneDrive account, because I am looking at my test form here and it works exactly as I said. Something else is going on that has been left unsaid. Let us inspect it and we'll find it.

    -Tom. Microsoft Access MVP

    Sunday, July 8, 2018 9:13 PM
  • One issue is caused by the number of samples to be collected which ranges from 1-4 with a drop down box for each sample, so one box is available for 1 sample, 2 for 2, 3 for 3 and 4 for 4.
    That suggests that you might be storing multiple values of the same attribute, sample in your case, in multiple columns in a table.  If so this would mean the table is not normalized to First Normal Form (1NF), whose formal definition is:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

    The correct way is to store the multiple values is in a single column in separate rows in a related table.  You can then filter on that single column.  In a form the multiple samples would be represented as rows in a subform.  You'll find an example in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes two options for filtering on a subform value, one which filters the parent form only, and one which filters both the parent form and subform.  A single unbound combo in the parent form is used to select a value on which to apply the filter in each case, which is done by code in the combo box's AfterUpdate procedure.


    Ken Sheridan, Stafford, England

    Sunday, July 8, 2018 9:33 PM
  • Hi Ken,

    The combo boxes work fine and add to the form table columns that they are linked to (one for each combo) the only glitch is getting them to be invisible until the quantity has been selected.

    Kind Regards

    Richard

    Monday, July 9, 2018 10:25 AM
  • Here is the coding used;

    Form

    On Load

    Private Sub Form_Load()
        On Error GoTo Error_Handler

        Dim stDocName As String

        LogEvent = "Form opened: " & Me.Name
        User = Forms!LoginForm.txtUserName
       LogEvt
        
        Me.Text32 = Forms!LoginForm.txtUserName
        Me.Number_of_Collections = Null
         
    PROC_EXIT:
      Exit Sub


    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub

    Error_Handler:
        LogError Err.Number, Err.Description, "ProcName"
        Resume Error_Handler_Exit
    End Sub

    Quantity Combo

    Before Update

    Private Sub Number_of_Collections_BeforeUpdate(Cancel As Integer)
      If IsNull(Number_of_Collections) = True Then
          Me.SampleType1.Visible = False
          Me.SampleType2.Visible = False
          Me.SampleType3.Visible = False
          Me.SampleType4.Visible = False
      End If
    End Sub

    After Update

    Private Sub Number_of_Collections_AfterUpdate()
      If IsNull(Number_of_Collections) = True Then
          Me.SampleType1.Visible = False
          Me.SampleType2.Visible = False
          Me.SampleType3.Visible = False
          Me.SampleType4.Visible = False
      End If
      If Number_of_Collections = "1" Then
          Me.SampleType1.Visible = True
          Me.SampleType2.Visible = False
          Me.SampleType3.Visible = False
          Me.SampleType4.Visible = False
      End If
      If Number_of_Collections = "2" Then
          Me.SampleType1.Visible = True
          Me.SampleType2.Visible = True
          Me.SampleType3.Visible = False
          Me.SampleType4.Visible = False
      End If
      If Number_of_Collections = "3" Then
          Me.SampleType1.Visible = True
          Me.SampleType2.Visible = True
          Me.SampleType3.Visible = True
          Me.SampleType4.Visible = False
      End If
      If Number_of_Collections = "4" Then
          Me.SampleType1.Visible = True
          Me.SampleType2.Visible = True
          Me.SampleType3.Visible = True
          Me.SampleType4.Visible = True
      End If
    End Sub

    Kind Regards

    Richard

    Monday, July 9, 2018 10:28 AM
  • Hi R.Nock

    I'm not entirely sure whether I got your initial question right.

    What I understood is:

    • On a form you have a Combobox and at least 4 other controls (you call them options, named SampleType1..4)
    • Upon opening the form you want to hide the SampleType1-4 controls
    • Depending on the users choice you want to show SampleType1 - SampleType4 

    Is this correct?

    If so, below you'll find some code that does exactly that. You can try it out by adding a Combobox to your form and make sure you'll have at least 4 options to select from. Then drop 4 additional controls (I used Textboxes, but feel free to use whatever you want) and make sure to match the names of those controls to the names in Sub ShowHideOptions(). 

    Private Sub Form_Load()
        cboNumber_of_Collections = Null
        ShowHideOptions 0
    End Sub
    
    Private Sub cboNumber_of_Collections_AfterUpdate()
        ShowHideOptions cboNumber_of_Collections.ListIndex + 1
    End Sub
    
    Private Sub ShowHideOptions(lOptionsToShow As Long)
        Dim colOptions As New Collection
        Dim i As Long
        
        With colOptions
            .Add txtSampleType1
            .Add txtSampleType2
            .Add txtSampleType3
            .Add txtSampleType4
            For i = 1 To .Count
                .Item(i).Visible = (i <= lOptionsToShow)
            Next i
        End With
    End Sub


    • Edited by papa_bear Monday, July 9, 2018 12:03 PM typos
    • Proposed as answer by papa_bear Monday, July 9, 2018 12:08 PM
    • Marked as answer by R.Nock Monday, July 9, 2018 4:37 PM
    Monday, July 9, 2018 12:02 PM
  • ..............table columns that they are linked to (one for each combo)
    Everything still points to your design being fundamentally flawed.  You really should be looking to eliminate the iceberg rather than moving the deckchairs on the Titanic.

    The principles of the database relational have been developed over a period of nearly half a century since Codd's first introduction of the model in his seminal paper in 1970 in Communications of the Association for Computing Machinery.  We ignore them at our peril.


    Ken Sheridan, Stafford, England

    Monday, July 9, 2018 4:09 PM
  • Hi R.Nock

    I'm not entirely sure whether I got your initial question right.

    What I understood is:

    • On a form you have a Combobox and at least 4 other controls (you call them options, named SampleType1..4)
    • Upon opening the form you want to hide the SampleType1-4 controls
    • Depending on the users choice you want to show SampleType1 - SampleType4 

    Is this correct?

    If so, below you'll find some code that does exactly that. You can try it out by adding a Combobox to your form and make sure you'll have at least 4 options to select from. Then drop 4 additional controls (I used Textboxes, but feel free to use whatever you want) and make sure to match the names of those controls to the names in Sub ShowHideOptions(). 

    Private Sub Form_Load()
        cboNumber_of_Collections = Null
        ShowHideOptions 0
    End Sub
    
    Private Sub cboNumber_of_Collections_AfterUpdate()
        ShowHideOptions cboNumber_of_Collections.ListIndex + 1
    End Sub
    
    Private Sub ShowHideOptions(lOptionsToShow As Long)
        Dim colOptions As New Collection
        Dim i As Long
        
        With colOptions
            .Add txtSampleType1
            .Add txtSampleType2
            .Add txtSampleType3
            .Add txtSampleType4
            For i = 1 To .Count
                .Item(i).Visible = (i <= lOptionsToShow)
            Next i
        End With
    End Sub


    Hi,

    The selections reveal either a single combo box, two combo boxes, three combo boxes or four combo boxes depending on the selection but I want all 4 to be invisible until a selection has been made.

    Kind Regards

    Richard

    Monday, July 9, 2018 4:21 PM
  • This is exactly what the code above does.

    In the beginning ALL 'selections' are invisible. (Done through a call to ShowHideOptions 0)

    Just replace txtSampleType1 … txtSampleType4 in ShowHideOptions by the names of your controls.

    Monday, July 9, 2018 4:24 PM
  • Hi,

    That worked, thank you.

    Kind Regards

    Richard

    Monday, July 9, 2018 4:36 PM