Appending a table using selected data from several listbox's (1 Viewer)

[japaley]

Hi All,

I'm trying to build a training database with a group people in it. I have 1 table named 1PeopleProTable and a 2nd table named 2ProcedureTable. I have created a form that has several listbox's in it that is using a query that is breaking the 2nd table in to several different procedure classes, one Procedure Class/Listbox. Now what I would like to do pull the first and last name from the 1PeopleProTable and use the selected data from the various listbox's and have all that data append a 3rd table called 3CompleteProTable.

Here is how I have it coded so far but I'm having problems with appended the 3rd table with the selected data from the listbox's

Private Sub AddProBtn_Click()

Dim strMyText As String

strMyText = Me.txtFirstName & Space(1) & Me.txtLastName & Space(1) & Me.lboCertPro.Column(1)
        
DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Name], [Procedure Version])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboCertPro.Column(1) & "', '" & _
Me.lboCertPro.Column(2) & "')"

MsgBox strMyText & Space(1) & "was added to the database"

End Sub

any help on my dilemma would be greatly appreciated
Thanks Josh

 

[JHB]

Do you get some error description?

 

[japaley]

Hi JHB

Thanks for getting back to me, I don't actually get a error message the problem is that only one selection is actually appended to the table. I would like more then one selection to append the table. I have 5 listbox's in this form. I would like any group of selections from any or all of the listbox's to be appended to the table.

Hope that helps a little more.
Thanks Josh

 

[JHB]

I would like any group of selections from any or all of the listbox's to be appended to the table.

Hope that helps a little more.

Sorry - no actually it didn't!
Do you want to create a record in the table for each selection, in which field(s) go the selection?

 

[japaley]

Hi JHB,

I have finished the coding that I'm using, I have got all the listbox's to append the 3rd table now. There's really only one real problem I can't get multiple sections from the same listbox to append the 3rd table. It'll only append one of the selections not multiple selections.

Private Sub AddProBtn_Click()

Dim strMyText As String

strMyText = Me.txtFirstName & Space(1) & Me.txtLastName
        
DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboCertPro.Column(0) & "', '" & _
Me.lboCertPro.Column(1) & "', '" & _
Me.lboCertPro.Column(2) & "', '" & _
Me.lboCertPro.Column(3) & "', '" & _
Me.lboCertPro.Column(4) & "', '" & _
Me.lboCertPro.Column(5) & "')"

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboHsPro.Column(0) & "', '" & _
Me.lboHsPro.Column(1) & "', '" & _
Me.lboHsPro.Column(2) & "','" & _
Me.lboHsPro.Column(3) & "', '" & _
Me.lboHsPro.Column(4) & "', '" & _
Me.lboHsPro.Column(5) & "')"

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboOffPro.Column(0) & "', '" & _
Me.lboOffPro.Column(1) & "', '" & _
Me.lboOffPro.Column(2) & "','" & _
Me.lboOffPro.Column(3) & "', '" & _
Me.lboOffPro.Column(4) & "', '" & _
Me.lboOffPro.Column(5) & "')"

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboDsPro.Column(0) & "', '" & _
Me.lboDsPro.Column(1) & "', '" & _
Me.lboDsPro.Column(2) & "','" & _
Me.lboDsPro.Column(3) & "', '" & _
Me.lboDsPro.Column(4) & "', '" & _
Me.lboDsPro.Column(5) & "')"

DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
& "Values('" & Me.txtFirstName & "', '" & _
Me.txtLastName & "', '" & _
Me.lboANOsPro.Column(0) & "', '" & _
Me.lboANOsPro.Column(1) & "', '" & _
Me.lboANOsPro.Column(2) & "','" & _
Me.lboANOsPro.Column(3) & "', '" & _
Me.lboANOsPro.Column(4) & "', '" & _
Me.lboANOsPro.Column(5) & "')"

MsgBox strMyText & Space(1) & "was added to the database"


End Sub

And a continued thanks for helping me work through this, any idea's why it won't append multiple selections from the same listbox. I have gone into the setting and turned on multi select.

Thanks Josh

 

[JHB]

Hi Josh.
You have to cycle trought the whole list to get the selected items.
Because the code is doing the same only the control name change, then I've made a (sub) procedure for this.


Try the code:

Private Sub AddProBtn_Click()
  Dim strMyText As String
  DoCmd.SetWarnings False
  strMyText = Me.txtFirstName & Space(1) & Me.txtLastName
  Call PutValueInTable(Me.lboCertPro.Name)
  Call PutValueInTable(Me.lboHsPro.Name)
  Call PutValueInTable(Me.lboOffPro.Name)
  Call PutValueInTable(Me.lboDsPro.Name)
  Call PutValueInTable(Me.lboANOsPro.Name)
  MsgBox strMyText & Space(1) & "was added to the database"
  DoCmd.SetWarnings True
End Sub
 
Private Sub PutValueInTable(ListControl As String)
  Dim ctlSource As Control
  Dim intCurrentRow As Integer
 
  Set ctlSource = Me(ListControl)
 
  For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
      DoCmd.RunSQL "INSERT INTO 3CompleteProTable([First Name], [Last Name],[Procedure Classifcation], [Procedure Name], [Procedure Version], [Procedure Status], [Procedure File Location], [Date Last Reviewed])" _
      & "Values('" & Me.txtFirstName & "', '" & _
      Me.txtLastName & "', '" & _
      Me(ListControl).Column(0, intCurrentRow) & "', '" & _
      Me(ListControl).Column(1, intCurrentRow) & "', '" & _
      Me(ListControl).Column(2, intCurrentRow) & "', '" & _
      Me(ListControl).Column(3, intCurrentRow) & "', '" & _
      Me(ListControl).Column(4, intCurrentRow) & "', '" & _
      Me(ListControl).Column(5, intCurrentRow) & "')"
    End If
  Next intCurrentRow
End Sub

 

[japaley]

Hi JHB

Sorry it took so long to get back to you but work been crazy lately. So that does the job thanks for the help, that code is much clearing then mine and to boot I've learned something.

Thanks Josh

 

[JHB]

You're welcome - luck with you project.