Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    macro for search (2003)

    I have a form that has a find record command button on it - I can make the focus go to the button when the form opens, but I cant figure out a macro that will then auto execute the command by having the find and replace popup window auto open - might anyone know of a way? Thanks!

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

    Re: macro for search (2003)

    Instead of invoking the button, you can display the Find or Replace dialog in the On Load event of the form:

    Private Sub Form_Load()
    RunCommand acCmdFind
    End Sub

    If you want to display the Replace dialog, you must set focus to an editable control first:

    Private Sub Form_Load()
    Me.txtSomething.SetFocus
    RunCommand acCmdReplace
    End Sub

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: macro for search (2003)

    Edited by HansV to replace picture in Word document in zip file with a picture of the error message

    Thanks for the info, Hans - you always come through for me! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    I must be doing something wrong - and dont work in code enough to know - I get this error message when I did as you suggested - do you know what I am doing wrong?
    Thanks
    Attached Images Attached Images
    • File Type: png x.png (6.3 KB, 0 views)

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

    Re: macro for search (2003)

    Try setting focus to an editable text box, as in the second suggestion I posted.

  5. #5
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: macro for search (2003)

    I apparently dont know what an "editable text box" is, because I added tried to add in the macro and it does not work
    Would you mind walking me through? <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

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

    Re: macro for search (2003)

    By an "editable text box" I meant a text box on the form that is bound to a field that the user can edit.

    If you still have problems, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: macro for search (2003)

    I cant post the dbase unfortunately.... but was hoping you could trouble shoot one more question on the issue -
    I put an editable text box on the form, went into properties of the control, on focus, code builder, then pasted your code from the posting... this is the error I get - any easy fix?
    Attached Files Attached Files

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

    Re: macro for search (2003)

    You can have only one Form_Load event procedure for a form. If you create two such procedures for the same form, you'll get the error message "The expression "On Click" you entered ... produced the following error: Ambigous name detected: Form_Load".

  9. #9
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: macro for search (2003)

    <img src=/S/frown.gif border=0 alt=frown width=15 height=15> I just cant get it to work - keep getting different errors when I try different things... I have resigned myself to not having that function automated.
    As usual, the assistance was greatly appreciated!

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

    Re: macro for search (2003)

    I have attached a small sample database with a form based on the Customers table from the Northwind sample database. The Replace dialog should be displayed automatically when you open the form.
    Attached Files Attached Files

  11. #11
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: macro for search (2003)

    I cant drop it!
    Thanks, I got it to somewhat work, please see attached error message - but:
    the fields are not controls
    they do have data
    and there are fields to search...
    Any other thoughts?
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    May 2004
    Location
    Pardeeville, Wisconsin, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='724105' date='29-Jul-2008 02:09']I have attached a small sample database with a form based on the Customers table from the Northwind sample database. The Replace dialog should be displayed automatically when you open the form.[/quote]

    I have gotten the above code to work when the user selects the option button the Field is selected and the find Message Box opens up.

    I would like to Match: Any Part of Field

    Private Sub LName_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.Last.SetFocus
    RunCommand acCmdFind
    End Sub


    I have found where AcFindMatch is the piece of code I am looking for and the name should be anyWhere and the value is 0. I just haven't been able to figure out how to put it all together. I was very excited that I actually got the first part of the code working and am looking at learning more.

    As always your help is greatly appreciated.

    Delta

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    acAnywhere is used in DoCmd.FindRecord, i.e. when you want to perform a search where you specify the arguments and options in VBA code. It can't be used for displaying the find or replace dialog.

    You can use SendKeys to specify search options in the find or replace dialog:

    SendKeys "%ha%n"
    RunCommand acCmdFind

    Explanation: % is the "code" for the Alt key in SendKeys.
    %h = Alt+h, this is the keyboard shortcut for the Match dropdown.
    a is the first letter of "Any part of field".
    %n = Alt+n, this is the keyboard shortcut for the Find What box.

Posting Permissions

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