Consulting

Results 1 to 11 of 11

Thread: Write Value From form1 to table1 After Form2 is Closed

  1. #1

    Question Write Value From form1 to table1 After Form2 is Closed

    Good Morning!
    New member and first-time poster
    I'm using Access 2016 and looking for help with my database.

    I have a Document Control database that is used to control revisions to documentation.
    The way this works is that a document change is created by the user that will show all documents that are affected by the change in a List Box.

    frm_QDO has a text box with the QDOnumber, a command button that opens frm_DocumentList, and a list box called List62 that shows selected values.
    From frm_DocumentList, the user selects each affected document and then clicks btn_Done.
    The selections are written to stbl_DocumentsSelected.sSelectedItems, stbl_DocumentsSelected.ItemData and then frm_DocumentList is closed.
    (Here is where my problem comes in!)
    Frm_QDO should now show the QDOnumber, sSelectedItems, and ItemData from stbl_DocumentsSelected that were selected SPECIFICALLY for that QDO.

    What I'm currently getting is all selected documents and all QDO numbers in the list box, no matter which QDO number my form is on.

    I have added snippet screenshots of my forms and table and here is my pertinent code. Any help is appreciated!

    ' btn_SelectDocuments_Click
    Private Sub btn_SelectDocuments_Click()
    On Error GoTo btn_SelectDocuments_Click_Err
        DoCmd.OpenForm "frm_DocumentList", acNormal, "", "", , acNormal
    btn_SelectDocuments_Click_Exit:
        Exit Sub
    btn_SelectDocuments_Click_Err:
        MsgBox Error$
        Resume btn_SelectDocuments_Click_Exit
    End Sub
    'btn_Done click
    Private Sub btn_Done_Click()
     
    Const KEY_VIOLATION = 3022
    Dim ctl As Control
    Dim strCriteria As String
    Dim strSQL As String
    Dim varItem As Variant
    Dim n As Integer
     
    Set ctl = AffectedDocs
     
    'if no items are selected then show message
    If ctl.ItemsSelected.Count = 0 Then
        MsgBox "Please make a selection from the list or Cancel this transaction.", vbOKOnly, "Input Required"
        Exit Sub
    End If
     
    'if item has been selected then insert row into table
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strSQL = "INSERT INTO stbl_DocumentsSelected ( sSelectedItems, ItemData ) VALUES ('" & ctl.Column(1, varItem) & "', '" & ctl.Column(2, varItem) & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Next varItem
    End If
     
    'requery list box to show new rows
        Form_frm_QDOs.List62.Requery
       
    'close form
        DoCmd.Close acForm, "frm_DocumentList"
     
    Exit_Here:
        Exit Sub
               Err_Handler:
        Select Case Err.Number
            Case KEY_VIOLATION
     
    'resume at next item
         Resume Next
         Case Else
     
    'unknown error
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
    End Select
    End Sub
    Attached Files Attached Files
    Last edited by SamT; 07-18-2017 at 12:48 PM.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So, let's see if I understand this process, from the QDO form the user opens frm_DocumentList, selects the required documents which are then placed in a table
    stbl_DocumentsSelected

    And you now want to display this data in the lower section of the QDO form, is that section a list box or subform?
    Why is the data placed in a table rather than directly in to the QDO form.

  3. #3
    Hi OBP!
    Yes, you understand the process correctly
    The lower section is a list box called List62.
    That's VERY good question! I'm not sure why I chose to have the data in the table vs the QDO form. I think because I need to be able to run various reports and wanted the data in a table to be able to create queries.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So assuming that your code updates your table correctly, (I notice it doesn't store any reference to the record on the QDO form), and that the List62's record source is the
    stbl_DocumentsSelected

    the you should Requery the list box with
    me.list62.requery
    to refresh the data.

  5. #5
    Correct, but I'm not sure how to copy the QDOnumber from the frm_QDOs to the stbl_DocumentsSelected.
    The row source for List62 is qryQDOs_2a:

    SELECT stbl_DocumentsSelected.sSelectedItems, stbl_DocumentsSelected.ItemData, stbl_DocumentsSelected.QDOnumber
    FROM stbl_DocumentsSelected;
    "The quality of a man's life is in direct proportion to his commitment to excellence, regardless of his chosen field of endeavor.” ~ Sherman Alexie, The Absolutely True Diary of a Part-Time Indian

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So does the stbl_DocumentsSelected contain the QDOnumber?
    If it does you need to change the query to include the current form QDOnumber in the query QDOnumber Criteria row.
    If you add
    forms![frm_QDO]![QDOnumber]
    to the first criteria row with the query in design mode it will restrict the outpout to that QDOnumber.

  7. #7
    Quote Originally Posted by OBP View Post
    So does the stbl_DocumentsSelected contain the QDOnumber?
    I am not sure how to get the QDOnumber into stbl_DocumentsSelected.
    "The quality of a man's life is in direct proportion to his commitment to excellence, regardless of his chosen field of endeavor.” ~ Sherman Alexie, The Absolutely True Diary of a Part-Time Indian

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Oh right, so can I suggest that you change the table updating from an SQL to a VBA Recordset?
    You need to Dim the recordset ie
    dim rs as object
    you then need to set the object ie
    Set rs = CurrentDb.OpenRecordset("stbl_DocumentsSelected")

    then replace this

    strSQL = "INSERT INTO stbl_DocumentsSelected ( sSelectedItems, ItemData ) VALUES ('" & ctl.Column(1, varItem) & "', '" & ctl.Column(2, varItem) & "');"
    CurrentDb.Execute strSQL, dbFailOnError

    with

    With rs
    .AddNew
    ![ItemData] = ctl.Column(1, varItem) ' if this is the Itemdata that you want in the file
    ![QDOnumber] = forms![frm_QDO]![QDOnumber]
    .Update
    .Bookmark = .LastModified
    End With

    after sending the data to the table you need to reset the rs object with
    rs.Close
    Set rs = Nothing

    You must ensure that you have Error trapping to debug this code as I am trying to modify my code to fit your data.

  9. #9
    Thank you! Thank you! Thank you!!!!!
    You are my Hero of the year for solving this



  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    My pleasure, I assume that it worked then?

  11. #11
    It totally worked and I have been stumped on this for WEEKS!
    I really can't express how thankful I am to get this part of the project complete!!!

    Here is the final code:



    'btn_Done click


    Private Sub btn_Done_Click()


    Const KEY_VIOLATION = 3022
    Dim ctl As Control
    Dim strCriteria As String
    Dim RS As Object
    Dim varItem As Variant
    Dim n As Integer


    Set ctl = AffectedDocs
    Set RS = CurrentDb.OpenRecordset("stbl_DocumentsSelected")


    'if no items are selected then show message
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please make a selection from the list or Cancel this transaction.", vbOKOnly, "Input Required"
    Exit Sub
    End If


    'if item has been selected then insert row into table
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
    With RS
    .AddNew
    ![sSelectedItems] = ctl.Column(1, varItem)
    ![ItemData] = ctl.Column(2, varItem)
    ![QDOnumber] = Forms![frm_QDOs]![QDOnumber]
    .Update
    .Bookmark = .LastModified
    End With

    Next varItem

    RS.Close
    Set RS = Nothing


    'requery second list box to show new rows
    Form_frm_QDOs.List62.Requery

    'close form
    DoCmd.Close acForm, "frm_DocumentList"


    Exit_Here:
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case KEY_VIOLATION
    'resume at next item
    Resume Next
    Case Else
    'unknown error
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here
    End Select
    End If
    End Sub
    "The quality of a man's life is in direct proportion to his commitment to excellence, regardless of his chosen field of endeavor.” ~ Sherman Alexie, The Absolutely True Diary of a Part-Time Indian

Tags for this Thread

Posting Permissions

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