Write Value From form1 to table1 After Form2 is Closed

[AmandaA531]

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

[OBP]

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.

[AmandaA531]

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.

[OBP]

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.

[AmandaA531]

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;

[OBP]

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.

[AmandaA531]

So does the stbl_DocumentsSelected contain the QDOnumber?

I am not sure how to get the QDOnumber into stbl_DocumentsSelected.

[OBP]

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.

[AmandaA531]

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

:bow:
:beerchug:

[OBP]

My pleasure, I assume that it worked then?

[AmandaA531]

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