Results 1 to 6 of 6
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    VBA statement for Filtering a Form (Access 2000)

    I've looked at this till my eyes cross and I can't see what I'm doing wrong.
    I'm trying to set the vba to filter a form with fields from an unbound form.
    I've got it work using one field - now I want two.

    Here is what I have currently (not working):
    ' Form_ImageRecordForm.Filter = (("DonorLabel='" & ChosenLabel & "'") And ("Imagefilename='" & Imagefile & "'"))

    Thanks for any help.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: VBA statement for Filtering a Form (Access 2000)

    Where have you put this statement?

    Try:
    Form_ImageRecordForm.Filter = "DonorLabel='" & ChosenLabel & "' And Imagefilename='" & Imagefile & "'"

    The alternative is to setup the criteria in the OpenForm statement.

    Have you also set FilterOn to True?

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

    Re: VBA statement for Filtering a Form (Access 2000)

    The opening and closing parentheses and "And" should be within the quotes, because they must be part of the resulting string. The way tou do it now, they are outside the quotes, and so VBA will try to interpret them. This is what I think it should be:<pre>Form_ImageRecordForm.Filter = _
    "((DonorLabel='" & ChosenLabel & "') And (Imagefilename='" & ImageFile & "'))"</pre>

    HTH

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA statement for Filtering a Form (Access 2000)

    Well, that works - but opens up a new can of worms <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.
    Now, the user has to make a selection from both options. I was aiming to make it an And/Or choice.

    hmmm. back to the drawing board.

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: VBA statement for Filtering a Form (Access 2000)

    You can allow the users to leave a choice blank:
    <pre>Dim strFilter As String
    If Not IsNull(Me.ChosenLabel) Then
    strFilter = " And (DonorLabel='" & Me.ChosenLabel & "')"
    End If
    If Not IsNull(Me.ImageFile) Then
    strFilter = " And (ImageFileName='" & Me.ImageFile & "')"
    End If
    If strFilter <> "" Then
    ' Get rid of first " And "
    strFilter = Mid(strFilter, 6)
    End If
    Form_ImageRecordForm.Filter = strFilter</pre>


  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA statement for Filtering a Form (Access 2000)

    I solved it this way:
    I had assigned the value of label not present earlier in the code, if all of the combo boxes that could be used for the chosen label are empty
    <pre>If Not IsNull(Imagefile) Then
    If (ChosenLabel) = "label not present" Then
    Form_ImageRecordForm.Filter = "Imagefilename='" & Imagefile & "'"
    Else
    Form_ImageRecordForm.Filter = _
    "((DonorLabel='" & ChosenLabel & "') And (Imagefilename='" & Imagefile & "'))"
    End If
    Else
    If Not IsNull(ChosenLabel) Then
    Form_ImageRecordForm.Filter = "DonorLabel='" & ChosenLabel & "'"
    End If
    End If
    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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