Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    10

    Unanswered: Find as you Type Help

    I need help on a code that I use to allow my users to search within my forms. Here is the link of the code I used Microsoft Access tips: Splash screen with version information

    The code works perfectly however I've having troubles with the code working on a filtered form. Upon entry, if a user has a Supt code of CS the form will only show info pertaining to that Code. However when a user tries to use the search instead of it searching only CS info it searches everything including the CS. I don't want the user to see other codes when searching. I tried using the query as the record source.. that worked but I have a custom add new record button on the form and that function no longer works. 2105 error is what I get. I need to know how can I get my search to work.
    Below is a snippet of my code. It determines which form to open depending on user permissions:

    Private Sub cmdEditBucketData_Click()
    'Open the Bucket form filtered to only show CurrentUser
    'Close the EnterEditData form
    Users

    If glbSuperUser = True Then
    DoCmd.OpenForm "frmBucket"
    DoCmd.Close acForm, "frmEnterEditData"
    ElseIf glbSupt = "NR" Then
    DoCmd.OpenForm "frmBucketNR", , "qryNRBucket"
    DoCmd.Close acForm, "frmEnterEditData"
    Else
    DoCmd.OpenForm "frmBucketBySupt", , "qryBucket"
    DoCmd.Close acForm, "frmEnterEditData"
    End If
    Exit Sub

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When you say 'filtered' do you mean you're actually using the Filter type command or does the recordsource of the form have criteria in it. If you're using the Filter type command, I'd avoid it. It's given me nothing but headaches for many, many years.

    Instead of this:
    DoCmd.OpenForm "frmBucketNR", , "qryNRBucket"

    Perhaps try something like this (note the extra comma (,) to utilize the WHERE versus the FILTER):

    Docmd.openform "frmBucketNR",,,"[SomeField] = '" & Forms!MyformName!MyFieldName & "'" (for string value matching)

    Docmd.openform "frmBucketNR",,,"[SomeIntegerField] = " & Forms!MyformName!MyIntegerFieldName & "" (for number value matching)
    or
    Docmd.openform "frmBucketNR",,,"[SomeField] = '" & somevariableName & "'" (if matching against a variable you've defined and set a value for)
    Last edited by pkstormy; 03-17-10 at 01:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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