append query based on items selected from list

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

Hello experts-

I have VBA code that allows users to add multiple records based on the items
selected from the list. The user populates certain fields in the form,
selects multiple items, say 5 items, and then hits the 'create' button to add
records - the user selected 5 items so there will be 5 separate records added
in the main table sharing the same information populated in different fields.
I was lucky enough to find vba code posted in this forum to help me code
this, and I think I'm almost 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
Dim strProjectRegion As String
Dim strEngr 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 Each varNumber In lstSwitches.ItemsSelected

strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)

strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectRegion = Forms![Multiple
Project]!lstSwitches.Column(2, 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

strEngr = Forms![Multiple Project]![Engineer ID]


With rst
.AddNew

!ProjectName = strProjectName + "-" + strSwitchName + " (" &
Year([Planned Start]) & ") "
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate
![Region ID] = strProjectRegion
![Engineer ID] = strEngr
End With

Next varNumber
MsgBox "Records Added"
'rst.Close
Set rst = Nothing
Set cnn = Nothing

End Sub


The records are added to the table, but it always excludes the last item
selected in the list - so if I select 5 items in the list, it is ignoring the
very last item that is selected and only adds 4 items to the table. The list
multiselect option is 'Extended'
Could anyone tell me why it is doing this? I'm suspecting the varNumber is
not reaching up to the last item selected, but I cannot figure out why.

Always thank you in advance for your time.
dave
 
dave said:
Hello experts-

I have VBA code that allows users to add multiple records based on the
items
selected from the list. The user populates certain fields in the form,
selects multiple items, say 5 items, and then hits the 'create' button to
add
records - the user selected 5 items so there will be 5 separate records
added
in the main table sharing the same information populated in different
fields.
I was lucky enough to find vba code posted in this forum to help me code
this, and I think I'm almost 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
Dim strProjectRegion As String
Dim strEngr 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 Each varNumber In lstSwitches.ItemsSelected

strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)

strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectRegion = Forms![Multiple
Project]!lstSwitches.Column(2, 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

strEngr = Forms![Multiple Project]![Engineer ID]


With rst
.AddNew

!ProjectName = strProjectName + "-" + strSwitchName + " ("
&
Year([Planned Start]) & ") "
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate
![Region ID] = strProjectRegion
![Engineer ID] = strEngr
End With

Next varNumber
MsgBox "Records Added"
'rst.Close
Set rst = Nothing
Set cnn = Nothing

End Sub


The records are added to the table, but it always excludes the last item
selected in the list - so if I select 5 items in the list, it is ignoring
the
very last item that is selected and only adds 4 items to the table. The
list
multiselect option is 'Extended'
Could anyone tell me why it is doing this? I'm suspecting the varNumber is
not reaching up to the last item selected, but I cannot figure out why.

Always thank you in advance for your time.
dave
Click to expand...


Try adding .Update just before your End With statement:

.Update
End With

My guess is, each .AddNew implicitly calls .Update, but the last one doesn't
since there's no .AddNew following.


Carl Rapson
 
dave said:
Hello experts-

I have VBA code that allows users to add multiple records based on the
items
selected from the list. The user populates certain fields in the form,
selects multiple items, say 5 items, and then hits the 'create' button to
add
records - the user selected 5 items so there will be 5 separate records
added
in the main table sharing the same information populated in different
fields.
I was lucky enough to find vba code posted in this forum to help me code
this, and I think I'm almost 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
Dim strProjectRegion As String
Dim strEngr 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 Each varNumber In lstSwitches.ItemsSelected

strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)

strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectRegion = Forms![Multiple
Project]!lstSwitches.Column(2, 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

strEngr = Forms![Multiple Project]![Engineer ID]


With rst
.AddNew

!ProjectName = strProjectName + "-" + strSwitchName + " ("
&
Year([Planned Start]) & ") "
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate
![Region ID] = strProjectRegion
![Engineer ID] = strEngr
End With

Next varNumber
MsgBox "Records Added"
'rst.Close
Set rst = Nothing
Set cnn = Nothing

End Sub


The records are added to the table, but it always excludes the last item
selected in the list - so if I select 5 items in the list, it is ignoring
the
very last item that is selected and only adds 4 items to the table. The
list
multiselect option is 'Extended'
Could anyone tell me why it is doing this? I'm suspecting the varNumber is
not reaching up to the last item selected, but I cannot figure out why.

Always thank you in advance for your time.
dave
Click to expand...

Further explanation: both .Edit and .AddNew need a call to .Update to "save"
the changes to the recordset.

Carl Rapson
 
Worked Great

Thank you, Carl-

Carl Rapson said:
dave said:
Hello experts-

I have VBA code that allows users to add multiple records based on the
items
selected from the list. The user populates certain fields in the form,
selects multiple items, say 5 items, and then hits the 'create' button to
add
records - the user selected 5 items so there will be 5 separate records
added
in the main table sharing the same information populated in different
fields.
I was lucky enough to find vba code posted in this forum to help me code
this, and I think I'm almost 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
Dim strProjectRegion As String
Dim strEngr 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 Each varNumber In lstSwitches.ItemsSelected

strSwitchID = Forms![Multiple Project]!lstSwitches.Column(0,
varNumber)

strSwitchName = Forms![Multiple Project]!lstSwitches.Column(1,
varNumber)
strProjectRegion = Forms![Multiple
Project]!lstSwitches.Column(2, 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

strEngr = Forms![Multiple Project]![Engineer ID]


With rst
.AddNew

!ProjectName = strProjectName + "-" + strSwitchName + " ("
&
Year([Planned Start]) & ") "
![Switch ID] = strSwitchID
!Description = strProjectDesc
!History = strProjectHist
!Scope = strProjectScop
![Type ID] = strProjectType
![Planned Start] = strStartDate
!Completion = strDueDate
![Region ID] = strProjectRegion
![Engineer ID] = strEngr
End With

Next varNumber
MsgBox "Records Added"
'rst.Close
Set rst = Nothing
Set cnn = Nothing

End Sub


The records are added to the table, but it always excludes the last item
selected in the list - so if I select 5 items in the list, it is ignoring
the
very last item that is selected and only adds 4 items to the table. The
list
multiselect option is 'Extended'
Could anyone tell me why it is doing this? I'm suspecting the varNumber is
not reaching up to the last item selected, but I cannot figure out why.

Always thank you in advance for your time.
dave
Click to expand...

Further explanation: both .Edit and .AddNew need a call to .Update to "save"
the changes to the recordset.

Carl Rapson
Click to expand...
 
Back
Top