Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Question Unanswered: Using Visual Basic code to filter

    I'm trying to have a continuous form that allows my users to filter through a large database of names. I'm new to visual basic but need the functionalty that vb has over a macro. The problem occurs when filtering the letters Q and X because no data exists for them yet. I receive an error that states "Control can't be edited its data source has no current record." Then after I try to select another record (besides Q or X) I recieve the same error and have to completely exit out.

    I've attached the code.

    Please Help!

    Code for X (it doesn't work)

    If (alphabar = 24) Then
    ' Filter last names that start with X.
    DoCmd.ApplyFilter "", "[tblTrespass]![strLastName] Like ""X*"""
    End If

    Code for Z (it works)
    End If
    If (alphabar = 26) Then
    ' Filter last names that start with Z.
    DoCmd.ApplyFilter "", "[tblTrespass]![strLastName] Like ""Z*"""
    End If
    If (alphabar = 27) Then
    ' Show all names.
    DoCmd.ShowAllRecords
    End If



    If (RecordsetClone.RecordCount > 0) Then
    ' If records are returned for the selected letter, go to the AlphaBar control.
    DoCmd.GoToControl "alphabar"
    Exit Sub
    End If

    If (RecordsetClone.RecordCount = 0) Then
    ' If no records are returned for the selected letter, display a message.
    Beep
    MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
    ' Show all records.
    DoCmd.ShowAllRecords
    ' Press in the All button.
    alphabar = 27
    End If

    alphabar_AfterUpdate_Exit:
    Exit Sub

    alphabar_AfterUpdate_Err:
    MsgBox Error$
    Resume alphabar_AfterUpdate_Exit

    End Sub

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is a solution I use to easily scroll to a letter in the alphabet, and you only see letters that are available, so in your case, you would not see 'Q' or 'X'. I use it to scroll a list box to the first name found starting with the letter chosen, but it seems applicable to your filtering problem.

    Me!Combo1.RowSource = "SELECT Left([fieldName],1) AS Char1 FROM [tableName] GROUP BY Left([tableName]![fieldName],1);"

    Clicking on the down-arrow of the combo box will display a unique list of the first letters of your field, sorted A-Z. In the update event of the combo box you use the value in Combo1.text to do your filtering. The following code at the top of the combo update event will cause the event to do nothing if no letter is hilighted.
    If Combo1.Text = Null Or Combo1.Text = "" Then Exit Sub

    Hope this helps.
    Jerry

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Me!Combo1.RowSource = "SELECT Left([fieldName],1) AS Char1 FROM [tableName] GROUP BY Left([tableName]![fieldName],1);"

    ...should work
    also a select distinct would do the same job

    Me!Combo1.RowSource = "SELECT distinct Left([fieldName],1) AS Char1 FROM [tableName] ORDER BY Left([tableName]![fieldName],1);"

    whether there is any merit in either proposal is difficult to know, as who knows what happens when the SQL is optimised. I'd guess that the select distinct may be more effecient, especially on a large recordset.

    dumb point make sure your fieldname column is indexed otherwise it could get mighty slow. It may be worthwhile adding an index on hte first character, if performance turns out to be a problem.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    incidentally if you are handling surname it may be worthwhile implementing a soundex function to allow to typos and misspellings (inadvertant or deliberate)

Posting Permissions

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