Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: Access 2003 - Command Button problem

    I an Access form I created three Command Buttons. One to "exit" the form; one to run a report (all records); one to select a specific record. I have an error in my code for the thrid button and need help to debug it (I'm a novice with VBA.) The code for the third button is:

    Private Sub SelectedContract_Click()
    On Error GoTo Err_SelectedContract_Click

    strFilter = "[LeaseMasterContractId] = '" & Me![LeaseMasterContractId] & "'"
    DoCmd.OpenReport "OPTIMIZEIT-Audit1", acViewPreview, , strFilter

    Exit_SelectedContract_Click:
    Exit Sub

    Err_SelectedContract_Click:
    MsgBox Err.Description
    Resume Exit_SelectedContract_Click

    End Sub

    Appreciate any help you can provide.

    Krazy

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What's the error? If that field is numeric, you don't want to surround the value with single quotes.
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is the LeaseMasterContractID numeric or a string? The only things that could cause an error in that code are

    * if your ID is numeric
    * the fieldname is spelled incorrectly
    * Option Explicit is defined
    * the reportname is spelled incorrectly

    It sure would help if you let us know what the error message is.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Command Button

    Field is text.
    Field name is spelled correctly.
    Report name is spelled correctly.
    Option Exprecit is not specified in VBA code.

    Appreciate al your help.
    Any other ideas?

    Krazy

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It sure would help if you let us know what the error message is... and on what like the code stops.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Command Button problem

    Error message is "Runtime Error 2465."
    "Microsoft Access can't find the field 'LeaseMasterContractId' referred to in your expression."
    Debug stops on line:
    strWhere = "[LeaseMasterContractId]= " & Me!LeaseMasterContractId & ""

    Over code is:
    Private Sub SelectedContract_Click()

    Dim strDocName As String

    strDocName = "OPTIMIZEAudit1"
    strWhere = "[LeaseMasterContractId]= " & Me!LeaseMasterContractId & ""
    DoCmd.OpenReport strDocName, acPreview, , strWhere

    End Sub

    Table name is OPTIMIZEITAudit1
    Field name is LeaseMasterContractId

    All I'm trying to do is be able to select one contract out of the entire list and display it on screen.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you think the error message is trying to tell you?

    I'd guess its saying it cannot find the control Me!LeaseMasterContractId
    it could be that you need to qualify the Me!LeaseMasterContractId to be
    Me!LeaseMasterContractId.text
    OR
    Me!LeaseMasterContractId.value

    nutI think its far more likely its a typo or missing control
    check for typos check the control exists

    BTW option explicit is in my books a "MUST" have.... makes tracking down typos much much easier, personally Ive never understood why its even allowed to be turned off

  8. #8
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Command Button problem

    Not sure how to "qualify the Me!LeaseMasterContractId to be Me!LeaseMasterContractId.text. But I found an article at www.tek-tips.com which provided the very simple code for a Single Selection:

    strFilter = "[fldState] = '" & Me!lstState & "'"
    DoCmd.OpenReport "rptCustomer", acViewPreview, , strFilter

    I copied their text into VBA and replaced their field [fldState] with my field [LeaseMasterContractId] and replaced their lstState with lstLeaseMasterContractId

    It still didn't work. I got the same error message (Can't find field lstLeaseMasterContractId) . NOTE: My field is a text field 15 characters; theirs was a text field also.

    Here is the link for their solution.
    http://www.tek-tips.com/faqs.cfm?fid=2657

    If you can tell me how to "qualify the field for text" I'll be happy to try it.
    Also, what is the link for your book(s)?
    Thanks,

    Krazy Kasper

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the clue (i'd hoped) was in the text....
    it could be that you need to qualify the Me!LeaseMasterContractId to be
    Code:
    Me!LeaseMasterContractId.text
    OR
    Code:
    Me!LeaseMasterContractId.value
    I still think the first thing to recheck is that you actually do have a control called LeaseMasterContractId in the relevant form

    what is the name of the control that has the master contracxt id in it.. the one you want to search for or limit results to.

    replace that into the forms!<myformname>!<myvariablename>
    where <myformname> is the name of the source form
    where <myvariablename> is the naem of the control, variable or column name

    if that fails then I'd suggest posting the form with associated events etc here. remove any redundant code, comapct and repair the db first, then attach it to a post as a zip file

  10. #10
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Command Button problem

    I qualified the the Me!LeaseMasterContractId to be Me!LeaseMasterContractId.text No change.

    Not sure how to recheck that their really is a control called LeaseMasterContractId in the relevant form. (If you can tell me how, I'll check it.)

    In the interim, I compacted and repaired the database, and zipped it.
    It is attached to this reply. Hope this helps.

    Thanks again,

    Krazy
    Attached Files Attached Files

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
    Paul

  12. #12
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Command Button problem - SOLVED

    Thank you ever so much.
    It's amazing how something so simple can cause such a great deal of work and frustration.

    Krazy

    p.s. What is the link to purchase your book(s)?

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're welcome. I don't have a book, and if you're referring back to this by Healdem:

    BTW option explicit is in my books a "MUST" have

    That's just an expression that means "in my opinion".
    Paul

Posting Permissions

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