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