Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: Issue pulling data from a continuous form

    I'm relatively new to Access so this may be (and probably is) a simple question.

    I have a simple interface I've been working on that allows users to enter requests then generates document ID numbers for these requests as part of a document control process.

    The form in question displays all open requests then displays the details of any selected request in a continuous sub-form. The sub-form has a label with an on-click event that is supposed to generate IDs based on the quantity control of that record. However, whenever a user clicks the "Issue Datasheets" label, it only ever pulls the value in the Quantity control in the first record. (If it helps, I included a jpg of the form in question as an attachment. Is there any way to post the jpg straight to the post? I couldn't see a way to do that.) In the jpg, if I click the third record down, it should return "3" as the quantity but instead always return "9".

    Here is the code I'm using to populate the sub-form and pull the value from the control:

    Private Sub OpenReqs_Click()

    ' Populate details of selected datasheet from Open Requests list

    Dim sSQL As String
    Dim CurrReq As Integer

    CurrReq = Me!OpenReqs.Value

    sSQL = "SELECT b.ProductName, a.AQSStudy, c.StudyType, a.PN, a.SOPFormProtocol, "
    sSQL = sSQL & "a.Appendix, a.Quantity, DueDate FROM dbo.[tbl_RequestDetails] a "
    sSQL = sSQL & "INNER JOIN dbo.[tbl_Product] b on a.ProductID = b.ProductID "
    sSQL = sSQL & "INNER JOIN dbo.[tbl_StudyType] c on a.StudyTypeID = c.StudyTypeID "
    sSQL = sSQL & "WHERE a.RequestID = " & CurrReq & ";"

    Me.Subfrm_Issued.Form.RecordSource = sSQL

    End Sub


    Private Sub IssueDatasheets_Click()

    ' Populates tbl_datasheets with Datasheet information. Generates datasheet IDs

    Dim UserReply As Integer
    Dim SheetQty As Integer
    Dim sSQL As String

    UserReply = MsgBox(Prompt:="Do you wish to issue these datasheets?", _
    Buttons:=vbYesNo, Title:="Submit Datasheets")

    If UserReply = vbYes Then
    SheetQty = Me!Quantity.Value
    MsgBox SheetQty
    Else 'User selected No. Return to form
    End If

    End Sub

    Any help is appreciated.
    Attached Thumbnails Attached Thumbnails Issue.jpg  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A bit tricky but it works:

    1) Change the label for a textbox (say Text_IssueDataSheet) and link it to the datasheet ID with its ControlSource property.

    2) Set its Format property to ""IssueDataSheet", set its Locked property to True, its SpecialEffect to 2D and its BorderWidth to 0 (so it looks like a label).

    3) In the OnClick event of this TextBox add code such as:
    Code:
    Private Sub Text_IssueDataSheet_Click()
        Me.Quantity.SetFocus
        MsgBox ...
        .........
        SheetQty = Me!Quantity.Value
    End Sub
    This way, when you click on the "label like" text box, it changes the current record. You have to move the focus to another control immediately othewise it will display the value of its ControlSource in place of "IssueDataSheet" because the format mask is removed when the control receives the focus.

    You cannot do that with a label because if you link the label to another control (so that it can change the current record when you click on it) it de-activates the events for that label.

    Have a nice day!

Posting Permissions

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