Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter by combobox by form (Access 2000)

    Hello I am new to Access 2000 and I am not VBA programmer, but I have a combo box that have many fields and once item is selected in the combo box I want the associated records filtered to be executed. Example If I select doctors I want all the records with doctors to be filtered.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    You will need a bit of VBA code for this, but it is not too complicated. In the following, I will assume that the combo box is named cboSelect and that the field you want to filter on is a text field named RecordType. You will have to substitute the names you have. After the code, I will indicate what to change if the field you want to filter on is numeric.
    <UL><LI>Open the form in design view.
    <LI>Select the combo box.
    <LI>Activate the Properties window (View | Properties)
    <LI>Activate the Events tab in the Properties window.
    <LI>Click in the After Update box.
    <LI>Click the dropdown arrow, and select "Event Procedure" from the list.
    <LI>Click the Builder button - the three dots ... to the right of "Event Procedure".

    You will be taken to the Visual Basic Editor. The code module of the form will open, and the first and last line of the event procedure have already been created for you:

    Private Sub cboSelect_AfterUpdate()

    End Sub

    <LI>The blinking cursor is in the empty line between Sub and End Sub. Enter the following code (I like to indent the code within a sub by pressing Tab before starting to type the code):

    If Not IsNull(Me.[cboSelect]) Then
    Me.Filter = "[RecordType] = " & Chr(34) & Me.[cboSelect] & Chr(34)
    Me.FilterOn = True
    End If

    The mysterious Chr(34) represents a double quote ". It is needed to surround the value selected in the combo box. For example, if you select Doctors, the resulting value of Me.Filter will be

    [RecordType] = "Doctors"

    <LI>Switch back to Access (use the Windows Task Bar, or Alt+Tab, or Alt+F11)
    <LI>Close and save the form.
    <LI>Open the form and test the combo box.[/list]It is possible that your combo box has a hidden key column (hidden = column width zero); for instance, Doctors could be 3 and Nurses 5, etc. If this key column is numeric, you can omit both occurrences of & Chr(34) in the code.

  3. #3
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    1) Getting a Message Box [Enter Parameter Value] ? Not sure why

    2) (See attached relationship document) I am still having problems, I want filtering to happen from DocSecID combo box

    3) Should the the combo box be initiated by the row source (Query) or control source from the table

    See Attached document
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    Can you look up the following properties of the DocSecID combo box and post them? Thanks.

    - Name
    - Row Source
    - Bound Column
    - Column Count
    - Column Widths

    Plus the code you have now for this combo box.

  5. #5
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    Hans did you also get a chance to look at the document I sent!

    - Name (DocSecID)
    - Row Source (Table/Query)
    SELECT tblDocSect.DocSectID, tblSection.SectName, tblOwn.Own, tblPAM.PAM, tblType.Type FROM tblType INNER JOIN (tblPAM INNER JOIN (tblOwn INNER JOIN (tblSection INNER JOIN (tblDocument INNER JOIN tblDocSect ON tblDocument.DocID=tblDocSect.DocID) ON tblSection.SectID=tblDocSect.SectID) ON tblOwn.OwnID=tblDocument.OwnID) ON tblPAM.PAMID=tblDocument.PAMID) ON tblType.TypeID=tblDocument.TypeID;
    - Bound Column (1)
    - Column Count (5)
    - Column Widths(0";1.5";0.5";0.5";1")

    If Not IsNull(Me.[DocSectID]) Then
    Me.Filter = "[RecordType]= " & Chr(34) & Me.[DocSectID] & Chr(34)
    Me.Filter = True
    End If

    Sonya

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    Hi Sonya,

    I did look at the document, but it didn't provide enough information.

    You state that the combo box is named <big>DocSecID</big>, but the code refers to <big>DocSectID</big>. Notice the extra <big>t</big>. I suspect that that is the cause of the problem.

    If the field DocSectID in tblDocSect is numeric, you should omit the Chr(34)'s, they are meant for strings:

    Me.Filter = "[RecordType]= " & Me.[DocSectID]

  7. #7
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    Name is DocSecTID (Doc SectID is made up of multiple columns and it orginated from (table) tblDocSect the form is build from tblVarOrd

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    Thanks. If the field DocSectID is numeric, try

    Me.Filter = "[DocSectID]= " & Me.[DocSectID]

    If it is text, try

    Me.Filter = "[DocSectID]= " & Chr(34) & Me.[DocSectID] & Chr(34)

  9. #9
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    Form not filtering it is doing nothing!

    If Not IsNull(Me.[DocSectID]) Then
    Me.Filter = "[DocSectID]= " & Me.[DocSectID]
    Me.Filter = True
    End If

    OR

    If Not IsNull(Me.[DocSectID]) Then
    Me.Filter = "[DocSectID]= " & Chr(34) & Me.[DocSectID] & Chr(34)
    Me.Filter = True
    End If

    Not working

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    I'm afraid we're not going to solve your problem this way. Would it be possible to post a stripped-down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros, modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]Thanks.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: filter by combobox by form (Access 2000)

    You will need to change the line that says
    Me.Filter = True
    to:
    Me.FilterOn = True

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    Of course! Should have seen that. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks, Pat

  13. #13
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    the filtering is working a bit, but when I click on a DocSectID and there are no records(to be filtered) I don't want a new record to be created. I want a message box to pop up when there are no records to be filtered. (Not sure how to code that condition)

    This is the code I am using!

    Private Sub DocSectID_Click()

    If Not IsNull(Me.[DocSectID]) Then
    Me.Filter = "[DocSectID]= " & Me.[DocSectID]
    Me.FilterOn = True
    End If
    End Sub

    Thanks
    Sonya

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter by combobox by form (Access 2000)

    I hope that the Record source of the form is a table or query.

    If it is a SQL statement ("SELECT ...") instead of a table or query name, click in the Record Source property, then click the builder button (the three dots ... to the right). In the query design window that appears, click the Save button.

    You can then use code like this

    Private Sub DocSectID_Click()
    If Not IsNull(Me.[DocSectID]) Then
    ' Test if there are records that pass the filter
    If DCount("*", "table_or_query_name", "[DocSectID]= " & Me.[DocSectID]) = 0 Then
    ' Inform the user
    MsgBox "There are no records for this selection.", vbExclamation
    Else
    ' Set the filter
    Me.Filter = "[DocSectID]= " & Me.[DocSectID]
    Me.FilterOn = True
    End If
    End If
    End Sub

    Replace table_or_query_name by the name of the table or query that acts as Record Source of the form.

  15. #15
    Lounger
    Join Date
    Jul 2003
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by combobox by form (Access 2000)

    Not working, if have three records for Physician in the table and there are no records for Reporter, I select Reporter and it overwrite Physician in that current record with Reporter. The message box is not workiong.
    Not sure what (DCount means(assuming count all)

    If Not IsNull(Me.[DocSectID]) Then
    ' Test if there are records that pass the filter
    If DCount("*", "QryFrmVarOrdDocSectID", "[DocSectID]= " & Me.[DocSectID]) = 0 Then
    ' Inform the user
    MsgBox "There are no records for this selection.", vbExclamation
    Else
    ' Set the filter
    Me.Filter = "[DocSectID]= " & Me.[DocSectID]
    Me.FilterOn = True
    End If
    End If

    Thanks
    Sonya

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •