Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Location
    Chicago, IL
    Posts
    5

    Red face Unanswered: passing form textbox data into query

    Hello,

    I'm a CS student / intern and am new to Access. I need some help for a debugging problem in my new internship.

    Here's the story:

    I'm working with a form with an imbedded subform. In the main form (the top part of the window) the user can do various things to filter the Request table's records. The recordset is returned below in the subform. Some data appears in the subform's textboxes, and other data can be entered by the user (a member of the configuration management team). The user can scroll one by one through the recordset.

    There is a combo box that is used to assign a specific Request to a member of my configuration management team. Once a selection is made from this box, an Access report is generated based upon a query and immediately sent to the developer who initially made the request to inform them that their request has been assigned to so-and-so from our group. The doCmd.OutputReport... command is used in the code to generate & ship off this report.

    In a majority of instances this has worked the way it should (these instances have been for brand new requests). If we try to reassign a record from one CMG person to another, the report generated has wrong information... data from another record (not of the record that we are currently viewing and making the change to).

    To prevent this, the best thing I can think of is to pass the number of the request (a unique identifier) from the "txtRequestID" textbox on the subform to the query. To do this, on the query design grid I have entered under Criteria for the RequestID column the following:

    =[Forms]![nameOfSubform]![txtRequestID].

    Now, when the user makes a change to the combobox, you get a parameter dialog box with the caption of [Forms]![...] This is NOT what I want. The user should not be prompted with anything!!!! I am certain that I have the names right, and that the textbox name is registered. I would very much appreciate some ideas! Thanks!

    Cindy

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Forms![FormName]![SubformName].Form![txtRequestID]

  3. #3
    Join Date
    Apr 2003
    Location
    Chicago, IL
    Posts
    5

    it didn't work

    Rocky, your suggestion unfortunately did not work. I believe you had a typo ( you put Forms![...]!Form.[txtRequestID], whereas I believe you meant ...Forms.[txtRequestID] - anyway, neither "Form" nor "Forms" worked. I'm wondering if there isn't something wrong with my copy of Access 97.

    Another thing I've tried is storing the value of txtRequestID in a publicly declared variable txtRequestID in the VB code, and then referencing the variable in my query. (I put as the condition "Where Requests.requestID = frmEdit!ID" (ID is the publicly declared variable)).

    Can anybody help?

    Thanks!

    Cindy

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Forms![FormName]![SubformName].Form![txtRequestID]

    This is correct if your main form is named "FormName" and the name of the subform CONTROL on the main form is named "SubformName". "SubformName" is not the actual name of the subform. Here's an example in Access 2K:

    http://66.179.41.117/ReferenceSubform.mdb

    Also - you can not directly reference variable in a query. You would need to call a function that returns the value of the variable. See example below.

    -Matt


    Place this function in a module
    ----------------------------------------------------
    Public Function JobID_Criteria() As Long

    On Error GoTo PROC_ERR

    JobID_Criteria = glCriteriaStr

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT

    End Function
    ----------------------------------------------------

    New query
    ----------------------------------------------------
    SELECT DISTINCT [Orders].[Job ID]
    FROM Orders
    WHERE ((([Orders].[Job ID]) Like (Left(JobID_Criteria(),7)+"*")))
    ORDER BY [Orders].[Job ID];
    ----------------------------------------------------

  5. #5
    Join Date
    Apr 2003
    Location
    Chicago, IL
    Posts
    5

    Cool thanks - got it

    Matt, sorry for being slow in following up. Just wanted to tell you that I did get it to work using your initial suggestion.

    I appreciate the help

    Cindy

Posting Permissions

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