Results 1 to 8 of 8
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Converted Macro to VBA code... now Search box does NOT work any longer

    Experts:

    I'd like to get some assistance with converting a macro into VBA code. Attached are two sample database:

    1. "01 Before Macro Conversion.accdb"
    2. "02 After Macro Conversion.accdb"

    Both database includes the same objects:
    - Table "T103_N1S_Billets"
    - Query "Q122_N1S_Billets"
    - Forms "F123_N1S_BilletList" and "F124_N1S_Billet"
    - Macro "SearchBillets" (ultimately, I'd like to delete this macro from version "02 After Macro Conversion.accdb"



    Process in database "1. "01 Before Macro Conversion.accdb":
    a. Open "F123_N1S_BilletList".
    b. Click on "Open" next to, e.g., BIN=0002222. Thus, form "F124_N1S_Billet" opens with details about the CFO position.
    c. Close form "F124_N1S_Billet" and go back to form "F123_N1S_BilletList".
    d. Now, in the "Search" box (top left), enter e.g., "Man" and press ENTER.
    e. Having entered just a few characters resulted in a match for "Manager".

    Note: This macro originally stems from the MS-Access template "Contacts".

    Now, I copied v01 (Before) and created v02 (After). In here, I converted the macro code into VBA (form "F123_N1S_BilletList"). Reasons for attempting to do so is not relevant for this post.

    Macro to VBA Conversion of form "F123_N1S_BilletList":
    - Opened F123_N1S_BilletList in Design Mode
    - Clicked on "=IIf(IsNull([BilletIDpk]),"(New)","Open")"... then "Properties"
    - Clicked on "Convert Form's Macros to Visual Basic"
    - Unchecked "Include Macro Comments"
    - Click on Convert...
    - Resulted in Error message: "There was a problem opening the macro "Filters". Do you want to continue?
    - Clicked "Yes"
    - Resulted in Error message: "There was a problem opening the macro "Filters". Do you want to continue?
    - Clicked "Yes"
    - Resulted in "Conversion Finished"... clicked "Ok"

    Testing the Form (now with VBA):
    - Clicked on "Open" hyperlink
    - Resulted in an Error message: "TempVars can only store data..."
    - Clicked "Debug"
    - Commented out the 3 lines of the IF statement
    - Returned to form
    - Clicked on "Open"... billet details form now opens fine. Great!!

    Continued Testing the Form (now with VBA):
    - Entered, e.g., "Man" in search box
    - Resulted in error: "Microsoft Access cannot find the name 'Form' you entered in the expression.

    My questions:
    1. As I commented out the three IF related lines, the Open hyperlink brings up the details form. What is the purpose of the "TempVars"? Do I need them?
    2. The Search box does no longer work. Apparently, database is looking for a form (didn't have it in v01) where it worked. How can I get the search box to work in v02 (just like did in v01)?

    Any thoughts?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Macro converter has a few bugs. See if below mods work for you...
    Just copy and paste to replace entire sub.
    There's similar code in SearchGo_Click, but it seems to never be executed.

    Code:
    Private Sub SearchGo_GotFocus()
    On Error GoTo SearchGo_GotFocus_Err
    
    
        'If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then
        
        If Len(Me.SearchBox & vbNullString) = 0 Then
            DoCmd.ApplyFilter "", """""", ""
            DoCmd.GoToControl "SearchBox"
            DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
            DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        End If
        
        'If (Eval("[CurrentProject].[IsTrusted] And ([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""")")) Then
        
        If [CurrentProject].[IsTrusted] And (Len(Me.SearchBox & vbNullString)) = 0 Then
             Me.SearchBox.Text = ""
        End If
        
        'If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then
        
        If Len(Me.SearchBox & vbNullString) = 0 Then
            End
        End If
        If VarType(Me.SearchBox) <> 8 Then
            End
        End If
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        
        'If (Eval("([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""") And [SearchClear].[Visible]<>0")) Then
        
        If Len(Me.SearchBox & vbNullString) = 0 And [SearchClear].[Visible] <> 0 Then
            DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
            End
        End If
        TempVars.Add "strSearch", Replace(Forms!F123_N1S_BilletList!SearchBox, """", """""")
        TempVars.Add "strFilter", "([Ra_BIN] Like ""*" & [TempVars]![strSearch] & "*"" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Ra_Billet_Title] Like ""*" & [TempVars]![strSearch] & "*"" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Ra_Bin] Like ""*" & [TempVars]![strSearch] & "*"" )"
        
        'Notice spaces before and after stars below. I removed them in the 3 lines above.
        'I don't know why the converted code is referencing the fields that don't exist below...
        
        'TempVars.Add "strFilter", TempVars!strFilter & " OR ([TFF_BSC] Like "" * " & [TempVars]![strSearch] & " * "" )"
        'TempVars.Add "strFilter", TempVars!strFilter & " OR ([N_Code] Like "" * " & [TempVars]![strSearch] & " * "" )"
        'TempVars.Add "strFilter", TempVars!strFilter & " OR ([All_LastName] Like "" * " & [TempVars]![strSearch] & " * "" )"
        'TempVars.Add "strFilter", TempVars!strFilter & " OR ([All_FirstName] Like "" * " & [TempVars]![strSearch] & " * "" )"
        'TempVars.Add "strFilter", TempVars!strFilter & " OR ([All_RankTitle] Like "" * " & [TempVars]![strSearch] & " * "" )"
        
        DoCmd.ApplyFilter "", TempVars!strFilter, ""
        TempVars.Remove "strFilter"
        TempVars.Remove "strSearch"
        DoCmd.SetProperty "SearchClear", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1"
        DoCmd.GoToControl "SearchBox"
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    
    
    
    
    SearchGo_GotFocus_Exit:
        Exit Sub
    
    
    SearchGo_GotFocus_Err:
        MsgBox Error$
        Resume SearchGo_GotFocus_Exit
    
    
    End Sub
    Last edited by davegri; 03-09-2020 at 09:26 AM. Reason: more desc

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Davigri - thank you for the feedback... very much appreciate it.

    You provided the perfect solution!!!

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Happy to help. Too bad Microsoft generated BAD code.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Davegri:

    Please allow me to ask a follow-up question(s).

    At this time, I'd like to cleanup the form. That is, as part of the "Macro to VBA Code Conversion", I'd like to remove any unneccsary VBA code from form **F123_N1S_BilletList**.

    This will allow me to better perform future maintenance (e.g., adding new fields to the form). Also, as I have search forms for "Staff Members" and "Organization", it will enable me to more easily replicate all functionality
    across the three DB elements (billets, staff member, and organization).

    Now, as part of this exampl DB, I have included only the search form for "Billet List" and "Billet" (details) only. When bringing up form "F123_N1S_BilletList", I need to be able to do the following:

    1. Click on "Open" (next each "BIN") and bring up the details form.
    2. Click on "New Billet" and create a new record (Note: This will not work in the demo as other tables are needed. I want to keep this function but it will throw an error in the demo... that's ok!)
    3. Click on "Show/Hide" Fields and allow user to check/uncheck desired fields.
    4. Click on "Question Mark" and bring up help form (Note: This will not work in the demo as another form is needed. I want to keep this function but it will throw an error in the demo... that's ok!)
    5. Use the "Search" to find records based on fields BIN, Billet Title, Last Name, First Name, Rank/Grade, N-Code, and BSC.
    6. Click on the "X" in the Search box and remove the filtered values.

    My questions:
    - Sub "LabelWizard_Click()"... do I really need this sub?
    - Difference between sub "SearchGo_Click()" and sub "SearchGo_GotFocus()"... is one (e.g., "SearchGo_Click()") of them duplicative and thus is not needed? Or do I need to keep both of them? What's the difference between these two?
    - Sub "Sub ID_Click()" -- is this the one that opens the detail form? Are there unnecesary lines of VBA that could be scrubbed?
    - Sub "txtOpen_Click()" -- looks similar to "Sub ID_Click()"... do I need to keep both of them or can one sub be deleted? What's the difference between these two subs?

    Thank you,
    Tom
    Attached Files Attached Files

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I have commented out unnecessary lines of code. Some of the commented out has been replaced by my code, especially to get rid of the tempvars.
    ID_Click and txtOpen_click do the same thing. It just depends on whether you click on "OPEN" or "BilletIDpk". I would hide BilletIDpk on the form and get rid of its ID_Click event.

    Attachment 41252

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Davegri -- your solution is ABSOLUTELY PERFECT!!!! Not using macros AND having clean and straightforward VBA will be so much easier to maintain/modify now.

    Thousands thanks... you are awesome!!!

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Happy to help, and thanks for the rep vote.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-22-2018, 05:49 PM
  2. Cannot find Converted Macro VBA code
    By Lou_Reed in forum Access
    Replies: 10
    Last Post: 08-14-2017, 06:53 AM
  3. Help updating macro code to work in navigation form
    By NikoTheBowHunter in forum Access
    Replies: 10
    Last Post: 08-02-2017, 01:26 PM
  4. Converted macro to VBA not working
    By gemadan96 in forum Forms
    Replies: 5
    Last Post: 06-04-2014, 10:36 AM
  5. !!!!Urgent!!!! Search code doesn't work!
    By Laetilae in forum Programming
    Replies: 4
    Last Post: 12-13-2010, 10:34 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