Results 1 to 6 of 6
  1. #1
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110

    Converted macro to VBA not working

    I've taken a MS template and modified for my purposes. Now that I have it just about where I want it I'm going through each form, after having backed up the data base, and converting macros to VBA. One macro for searching several fields in a record set doesn't quite work. The macro worked flawlessly. THE VBA doesn't give an error. After entering your criteria and clicking the button to search it filters out the entire record set. The buttons to show all records or remove the filters do so and restore the entire record set.

    This macro came with the template and has only been modified due to different fields being used. The converted code is below.

    Private Sub cmdGo_Click()
    On Error GoTo cmdGo_Click_Err


    If (Eval("[Forms]![Promotion List]![SearchBox] Is Null")) Then
    DoCmd.RunCommand acCmdRemoveFilterSort
    DoCmd.SetProperty "cmdShowAll", acPropertyEnabled, "0"
    End
    End If
    TempVars.Add "strSearch", Replace(Forms![Promotion List]!SearchBox, """", """""")
    TempVars.Add "strFilter", "([Member Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
    TempVars.Add "strFilter", TempVars!strFilter & " OR ([Rank] Like "" * " & [TempVars]![strSearch] & " * "" )"
    TempVars.Add "strFilter", TempVars!strFilter & " OR ([Description] Like "" * " & [TempVars]![strSearch] & " * "" )"
    TempVars.Add "strFilter", TempVars!strFilter & " OR ([RankTitle] Like "" * " & [TempVars]![strSearch] & " * "" )"
    DoCmd.ApplyFilter "", TempVars!strFilter, ""
    TempVars.Remove "strFilter"
    TempVars.Remove "strSearch"
    DoCmd.SetProperty "cmdShowAll", acPropertyEnabled, "1"




    cmdGo_Click_Exit:
    Exit Sub


    cmdGo_Click_Err:
    MsgBox Error$
    Resume cmdGo_Click_Exit




    End Sub

    Any suggestions to resolve this are appreciated.

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I see an extra End ? Besides this, when you debug and step thru it, do you get any errors? Some Macros just wont work after conversion. VBA is better, but any reason why you can't use the Macro?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    I've never used TempVars. The code is behind form [Promotion List]? Try:
    Code:
    Dim strSearch As String, Dim strFilter As String
    If IsNull(Me.SearchBox) Then
         Me.Filter = ""
         Me.FilterOn = False
    Else
        strSearch = Replace(Me.SearchBox, """", """""")
        strFilter = "[Member Name] Like ' * " & strSearch & " * ' )"
        strFilter = strFilter & " OR ([Rank] Like ' * " & strSearch & " * ' )"
        strFilter = strFilter & " OR ([Description] Like ' * " & strSearch & " * ' )"
        strFilter = strFilter & " OR ([RankTitle] Like ' * " & strSearch & " * ' )"
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110
    Burrina - There is no reason I cant use the macro. It just would be nice to be able to have everything in VBA. Don't get any erros just no result.

    June7 - I tried your code and got an error stating that strSearch and strFilter weren't declared. I declared them and get the same result as the original code.

    I have to believe there's a way to get this search feature to work in VBA.

  5. #5
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Only to be sure: is it wanted to have these whitespaces between the wildcard * and your search text? or should it be:
    Code:
    strFilter = "[Member Name] Like '*" & strSearch & "*'"
    ...
    It would only search for full words in a text that starts end ends with a whitespace in your case. I don't think that this is wanted behaviour.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    I have the variable declarations in my sample code.

    Good catch hapm. I did not see the spaces.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Macro's not working... please help
    By MFriend in forum Access
    Replies: 4
    Last Post: 06-02-2014, 03:00 PM
  2. Assign converted macro to navigation panel button
    By nhylan in forum Programming
    Replies: 4
    Last Post: 04-17-2013, 01:33 PM
  3. Replies: 7
    Last Post: 08-10-2012, 03:09 PM
  4. converted coding issues.
    By d4jones in forum Database Design
    Replies: 2
    Last Post: 07-11-2012, 09:48 AM
  5. RunCode in macro not working
    By LilMissAttack in forum Access
    Replies: 0
    Last Post: 05-27-2010, 05:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums