Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19

    Unanswered: "Find Record" button

    Hello,

    I have an invoice form that I've been developing for a client, that has a "Find Invoice" button that was created by using Access' wizard, and was set to find a record.

    The problem I have with this, is when a user opens the DB, opens the invoice form, and clicks on "Find Invoice", unless they're searching for a first name, it won't find anything, and states the obvious: an error telling the user that it couldn't find anything.

    Now, if the user uses my 'workaround technique' for this problem, by opening the find window back up, and changing the LOOK IN value from "First Name" to "Invoices", it searches every entire row of the INVOICES table, and returns results that are what the user is after.

    The other thing (and I did manage a workaround for this one) I noticed, was that whatever has focus at the time of pushing the FIND button is what LOOK IN is set to search, unless sometime during the time that Access has been running, someone has set LOOK IN to INVOICES, and then it stays set to that until someone closes Access completely...

    So, what I am trying to figure out, is if there is a way that I can set the focus to INVOICES every time the button is clicked, which I tried doing before several weeks ago by using the FORM.SETFOCUS function, but it either can't do what I need it to, or I'm just a newbie that doesn't know what he's doin! It gives me the error "The control 'FindInvoice' the macro is attempting to search can't be searched......."

    When I use the FORM.SETFOCUS function, it DOES set the LOOK IN value to "Invoices", and won't let you change the selection... it just doesn't work.

    So, if anyone has a thought about a way I could get around this problem, and have the find button completely search every single record in the table, I would be grateful!

    Thanks,
    Brian

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    you're close. You need to reference the control too though:

    me.invoiceid.setfocus

  3. #3
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Well, InvoiceID is the field name for the invoice numbers, and when I do the Me.InvoiceID.SetFocus thing, it only wants to search through the invoice numbers... Was worth a shot... Any other ideas?

    Thanks for the input,
    Brian

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Originally posted by big_t8r
    Well, InvoiceID is the field name for the invoice numbers, and when I do the Me.InvoiceID.SetFocus thing, it only wants to search through the invoice numbers... Was worth a shot... Any other ideas?

    Thanks for the input,
    Brian
    Brian,

    I have a similar situation (I think).

    Me.Form.SetFocus

    Allows me to search all fields on the form.

    Hope that helps.

    Chris

  5. #5
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Can anyone tell me why Me.Form.SetFocus gives me the error:

    "The control 'FindInvoice' the macro is attempting to search can't be searched.

    Try one of the following:
    * Add a GoToControl action before the FindRecord action.
    * For the FindRecord action, change the Only Current Field action argument from Yes to No.
    * Change the focus to a searchable control."

    Here's the code for the FINDINVOICE button as it has been for a couple weeks:

    ------------------------------------------
    Private Sub FindInvoice_Click()

    On Error GoTo Err_FindInvoice_Click

    FirstName.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_FindInvoice_Click:
    Exit Sub

    Err_FindInvoice_Click:
    MsgBox Err.Description
    Resume Exit_FindInvoice_Click

    End Sub
    ------------------------------------------

    Any clues? Thanks!

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Uhm, change FirstName.SetFocus to Me.Form.SetFocus

    Did you try that?

  7. #7
    Join Date
    Mar 2004
    Location
    Tigard, Oregon USA
    Posts
    19
    Yes, I tried that... That's why I'm asking the question:

    "Can anyone tell me why Me.Form.SetFocus gives me the error:

    "The control 'FindInvoice' the macro is attempting to search can't be searched.

    Try one of the following:
    * Add a GoToControl action before the FindRecord action.
    * For the FindRecord action, change the Only Current Field action argument from Yes to No.
    * Change the focus to a searchable control." "

    Looking back, I guess I wasn't too clear on whether or not I tried that--the code that I posted was the way it was before I tried Me.Form.SetFocus in place of FirstName.SetFocus, to see if anyone saw something in there that might prevent what I'm trying to do.

    Thanks!
    Last edited by big_t8r; 04-22-04 at 19:05.

  8. #8
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Sorry, missed the part about that being old code.

    I wasn't able to recreate your error.

    Me.Form.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Acts like I expect it to.

    Maybe someone else has an idea. Good luck.

  9. #9
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    EXCELLENT!

    Thanks for your help. I never knew about the findrecord code before. I've done some searching and info seeking and got it to work so thanks for the heads up.

    Christyxo

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    FindRecord

    "Can anyone tell me why Me.Form.SetFocus gives me the error:

    "The control 'FindInvoice' the macro is attempting to search can't be searched.

    Try one of the following:
    * Add a GoToControl action before the FindRecord action.
    * For the FindRecord action, change the Only Current Field action argument from Yes to No.
    * Change the focus to a searchable control." "

    I'm guessing you might be getting this error if there is a field on the form that it can't do the search on.

    I'm not sure why this wouldn't work:

    Private Sub FindInvoice_Click()

    On Error GoTo Err_FindInvoice_Click

    me.FirstName.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Unless for some reason it can't set the control to the FirstName field. What happens if you just issue a me.FirstName.Setfocus?

    You can also try setting the focus to another control and then back to the Firstname field:

    me.LastName.SetFocus
    me.FirstName.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    As you might be getting the error that you can't set the focus to a control which already has the focus.

    Also keep in mind that the FindRecord may run slower verses if you were to open a recordset in vba based on the value in FirstName (creating an index on the FirstName field will help the speed):

    Refresh
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select FirstName from MyTable where FirstName Like """ & me!FirstName & """
    rs.open strSQL,currentproject.connection,adopenkeyset,adop enreadonly
    if rs.eof and rs.bof then
    rs.close
    set rs = nothing
    msgbox("No records match.")
    else
    dim strFN as string
    strFN = me!FirstName
    rs.close
    set rs = nothing
    msgbox("Found match...")
    docmd.close acform, "MyForm"
    docmd.openform "MyForm",,,"FirstName = """ & strFN & """"
    end if

    This may or may not be faster (something you can test) and it will open the form with only the matching records which you may or may not want.
    Last edited by pkstormy; 01-10-06 at 14:49.
    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
  •