Append from multilistbox AND current subform record?

[bhammer]

I want to append multiple records into tblObservations based on selections
the user makes in a form, frmAddRecords. I already have the form setup. It
has an unbound multiselect listbox on the left that lists txtImageName, and a
bound subdatasheet on the right that lists the Catalog records bound to
CatalogID. All the selected ImageNames need to go with the selected CatalogID
into the tblObservations.

tblObservations has CatalogID and txtImageName. How do I setup an append
query or SQL on a command button to add the selected ImageNames (with the
associated CatalogID) into the tblObservations?

 

[Tom van Stiphout]

On Tue, 4 Nov 2008 00:24:00 -0800, bhammer

You create a parameterized Append query to add one record. Then loop
over the listbox items and test for the Selected property to be True,
and call this query.

-Tom.
Microsoft Access MVP

 

[bhammer]

I got it! Here's what I have:

***************Start Code*****************
Private Sub cmdAssoc_Click()
Dim ctl As Control, db As Database, rs As Recordset
Dim varSelectedItem As Variant
Dim strCatID As String, strDocNum As String
Dim intCount As Integer
Dim msg, style

Set ctl = Me.lstPhotos
SQL = "SELECT * From tblObservations"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
intCount = 0

If ctl.ItemsSelected.Count < 1 Then
MsgBox "You must first select one or more Photos from the list.",
vbInformation, "Select Photos to Assign"
Exit Sub
End If

For Each varSelectedItem In ctl.ItemsSelected
intCount = intCount + 1
'strCatID = Me.frmCatJPGsAssocSub.Form.txtCatalogID
strCatID = Me.lstInspections.Column(0)
'MsgBox "Is this CatalogID? " & Me.lstInspections.Column(0)
strDocNum = ctl.Column(1, varSelectedItem)
rs.AddNew
rs![CatalogID] = strCatID
rs![ObservationID1] = strCatID
rs![ObservationID2] = intCount
rs![Doc_Number] = strDocNum
rs![EvidenceTypeID] = "P"
rs.Update
'Append the record
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblCatalog_Temp " & _
"SET tblCatalog_Temp.Assigned = True " & _
"WHERE (((tblCatalog_Temp.FileName)= " & """" & strDocNum & """" &
"));"
Next varSelectedItem
DoCmd.SetWarnings True

rs.Close
Set db = Nothing
Set rs = Nothing
***************End Code*******************
-Brad