listbox, multiselect, append selected to subform datasheet (1 Viewer)

willsnake

Registered User.
Local time
Tomorrow, 07:53
Joined
Dec 3, 2018
Messages
52
good day to all,

I am here again seeking help on my DB..
Below is a code I found in the net and I modified it.
The code work when directly record the selected into the Table,
But what I want is that when I press the command button, it will add the selected data in the Subform "TblPOParticular Subform" from mainform "TblPurchaseOrder" such subform is bound to the Table "TblPOParticular"

Code:
Option Compare Database

Private Sub Command12_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TblPOParticular", dbOpenDynaset, dbAppendOnly)

  'add selected value(s) to table
  Set ctl = Me.List10
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!ItemDescription = ctl.ItemData(varItem)
    rs!POUnit = DLookup("[Unit]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POQuantity = DLookup("[Quantity]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POCost = DLookup("[Cost]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub
 

Attachments

  • subform.jpg
    subform.jpg
    92.9 KB · Views: 119
  • subform1.jpg
    subform1.jpg
    98.6 KB · Views: 119
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:53
Joined
Aug 30, 2003
Messages
36,118
If it's already working to add the data to the table, you may just need to requery the subform so that it shows the new records.
 

willsnake

Registered User.
Local time
Tomorrow, 07:53
Joined
Dec 3, 2018
Messages
52
Code:
Private Sub Command12_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TblPOParticular", dbOpenDynaset, dbAppendOnly)

  'add selected value(s) to table
  Set ctl = Me.List10
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!POID = DLookup("[POID]", "TblPurchaseOrder", "[POID]=" & Forms!TblPurchaseOrder.Form.POID)
    rs!ItemDescription = ctl.ItemData(varItem)
    rs!POUnit = DLookup("[Unit]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POQuantity = DLookup("[Quantity]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POCost = DLookup("[Cost]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
Forms!TblPurchaseOrder!TblPurchaseOrder_subform.Form.Requery
DoCmd.Close acForm, acSaveNo
End Sub

This is the code I made... but it does not populate the subform, maybe because It cannot link the POID maybe because I am still at encoding in the form..

I am attaching my DB for reference...

I am very sorry for the bother sir...

Thank you...
 

Attachments

  • William - Supply - Copy.zip
    487.1 KB · Views: 142

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:53
Joined
Aug 30, 2003
Messages
36,118
What would be the process you go through. The form comes up on a new record now, so it wouldn't be able to relate to records entered via the other form unless it was on the same ID.
 

willsnake

Registered User.
Local time
Tomorrow, 07:53
Joined
Dec 3, 2018
Messages
52
in the navigation, I will press the NEW PO
then the form TblPurchaseOrder will open as new record
one of the combobox(LotNumber) has afterupdate event that will open the form with listbox
after selecting the items, clicking the ADD button, it will close the form w/ listbox and populate the subform in the TblPurchaseOrder...
 

willsnake

Registered User.
Local time
Tomorrow, 07:53
Joined
Dec 3, 2018
Messages
52
accidentally solved my PROBLEM!!!!!!!!!!!!

Code:
Private Sub Command12_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TblPOParticular", dbOpenDynaset, dbAppendOnly)

  'add selected value(s) to table
  Set ctl = Me.List10
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!POID = Forms!TblPurchaseOrder.Form.POID
    rs!ItemDescription = ctl.ItemData(varItem)
    rs!POUnit = DLookup("[Unit]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POQuantity = DLookup("[Quantity]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs!POCost = DLookup("[Cost]", "TblLotParticular", "[LotParticularID]=" & ctl.ItemData(varItem))
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  DoCmd.Close acForm, "POParticularSelect"
  Forms!TblPurchaseOrder![TblPOParticular Subform].Requery
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

:banghead::banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:53
Joined
Aug 30, 2003
Messages
36,118
Geez, I didn't notice where you had those lines. :banghead:

Glad you got it sorted.
 

willsnake

Registered User.
Local time
Tomorrow, 07:53
Joined
Dec 3, 2018
Messages
52
yah... hahaha...

And now back to encoding from start....

Thank you for the Big help...
 

Wildboy99

New member
Local time
Tomorrow, 07:53
Joined
Mar 5, 2020
Messages
10
Thank you very much for your information,
it help me sorted out my problem.
 

Users who are viewing this thread

Top Bottom