appending multiple selected records from listbox

G

Guest

hello,
I am trying to write vba code for a form.
I want to append records to a table based upon a list box - the user fills
out all the fields on the form and then selects multiple items, say 20 items,
from a list and on the click event of a command button, it will append 20
records to the "Project" table with 20 records having the same information
EXCEPT the field that was chosen from the list box. There was a similar post
to this which was answered by ChrisJ, and I derived my code from there;

Private Sub cmdCreateMultipleProjects_Click()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim varNumber As Variant
Dim strProjectName As String
Dim strProjectDesc As String
Dim strProjectHist As String
Dim strProjectScop As String
Dim strProjectType As String
Dim strStartDate As String
Dim strSwitchID As String
Dim strSwitchName As String
Dim strDueDate As String

If IsNull(Name) Then
MsgBox "You must fill out a project name.", vbInformation, "Add
multiple projects"
End If

If lstSwitches.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 switch.", vbInformation, "Add
multiple projects"
Exit Sub
End If

Set cnn = CurrentProject.Connection
rst.Open "Project", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
For varNumber = 0 To lstSwitches.ListCount - 1
strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)
'' strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectName = Forms![Multiple Project]!Name
strProjectDesc = Forms![Multiple Project]!Description
strProjectHist = Forms![Multiple Project]!History
strProjectScop = Forms![Multiple Project]!Scope
strProjectType = Forms![Multiple Project]![Type ID]
strStartDate = Forms![Multiple Project]![Planned Start]
strDueDate = Forms![Multiple Project]!Completion

With rst
.AddNew

!ProjectName = strProjectName + strSwitchID
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate

End With
Next varNumber
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub



It is suppose to add records for those switch ids that are selected from the
lstSwitches, but for some reason it is adding everything in the lstSwitches.
Also I get an error at the end on the

rst.Close

saying that you cannot use this type of operation here.

Could someone point out what I am doing wrong?

any help would be greatly appreciated Thanks!
 
G

Guest

Dave,
"For varNumber = 0 To lstSwitches.ListCount - 1" would loop through all the
rows of your list box. I think you'd want to use the ItemsSelected property.
VB Help gives an example of debug printing values of each selected row in a
list box. I think you could use the help examples to do what you want to do.

HTH,
Miki

dave said:
hello,
I am trying to write vba code for a form.
I want to append records to a table based upon a list box - the user fills
out all the fields on the form and then selects multiple items, say 20 items,
from a list and on the click event of a command button, it will append 20
records to the "Project" table with 20 records having the same information
EXCEPT the field that was chosen from the list box. There was a similar post
to this which was answered by ChrisJ, and I derived my code from there;

Private Sub cmdCreateMultipleProjects_Click()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim varNumber As Variant
Dim strProjectName As String
Dim strProjectDesc As String
Dim strProjectHist As String
Dim strProjectScop As String
Dim strProjectType As String
Dim strStartDate As String
Dim strSwitchID As String
Dim strSwitchName As String
Dim strDueDate As String

If IsNull(Name) Then
MsgBox "You must fill out a project name.", vbInformation, "Add
multiple projects"
End If

If lstSwitches.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 switch.", vbInformation, "Add
multiple projects"
Exit Sub
End If

Set cnn = CurrentProject.Connection
rst.Open "Project", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
For varNumber = 0 To lstSwitches.ListCount - 1
strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)
'' strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectName = Forms![Multiple Project]!Name
strProjectDesc = Forms![Multiple Project]!Description
strProjectHist = Forms![Multiple Project]!History
strProjectScop = Forms![Multiple Project]!Scope
strProjectType = Forms![Multiple Project]![Type ID]
strStartDate = Forms![Multiple Project]![Planned Start]
strDueDate = Forms![Multiple Project]!Completion

With rst
.AddNew

!ProjectName = strProjectName + strSwitchID
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate

End With
Next varNumber
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub



It is suppose to add records for those switch ids that are selected from the
lstSwitches, but for some reason it is adding everything in the lstSwitches.
Also I get an error at the end on the

rst.Close

saying that you cannot use this type of operation here.

Could someone point out what I am doing wrong?

any help would be greatly appreciated Thanks!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

append query based on items selected from list 3
Append records to table based upon listbox selected items 5
Save selected items NOT selected in an Extended List Box based onother condition 6
Filtered Records as source for appending records to a table 1
Clear items selected from multiselect list box 3
Code to Update Recordset only works intermittently 3
exporting data to project 12
rst.Find '3001' problem.... 5

Top