OK, it's hard to tell without knowing a few details. When you said you want
to insert the antibodies into the patient's record it sounded as if you
meant to insert the list into the main record. Perhaps you meant you are
using a multi-value field from Access 2007.
If it was me I would probably create a subform and select the anitbodies one
at a time. No doubt what you want can be done, but it could be rather
involved.
This is something I saved a while ago from a posting Allen Browne made in a
newsgroup:
********
To add new records with a loop:
Function MakeData(HowMany As Long)
Dim lngI As Long
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("MyTable")
With rs
For lngI = 1 To HowMany
.AddNew
!SomeField = SomeValue
!AnotherField = AnotherValue
'etc.
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
It may be easier to execute one Append query statement, and use a Cartesian
product to generate the desired number of records. That's just a matter of
having a table that counts from 1 to the maximum number of records you would
ever need to add. Include this table in your append query, with no join
between tables. Use criteria under the counter field to get the desired
number of records.
*********
To get the SomeValue items you culd use something like this. lstTest is the
name of the multi-select list box. This could be a command button code:
Dim varItem As Variant 'Selected items
Dim colAB As New Collection 'Collection of selected items
Dim i As Integer
'Loop through the items Selected in the list box.
With Me.lstTest
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
colAB.Add .ItemData(varItem)
End If
Next
End With
For i = 1 To colAB.Count
Debug.Print colAB(i)
Next i
This shows the code with Debug.Print, but I expect you could do something
like this to combine the two:
Function MakeData()
Dim lngI As Long, lngParent as Long, lngCount as long
Dim rs As DAO.Recordset
Dim varItem As Variant 'Selected items
Dim colAB As New Collection 'Collection of selected items
lngParent = Me.PrimaryKeyField
'Loop through the items Selected in the list box.
With Me.lstTest
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
colAB.Add .ItemData(varItem)
End If
Next
End With
lngCount = colAB.Count
Set rs = DBEngine(0)(0).OpenRecordset("JunctionTable")
With rs
For lngI = 1 To lngCount
.AddNew
!FK_Field = lngParent
!AntibodyID = colAB( LngI - 1)
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
I believe LngI -1 is necessary because the collection items numbering is
zero-based.
I learned about looping through list box items from Allen Browne, and about
using a collection from Albert Kallal. I have not seen them put together in
quite this way. I'm afraid I don't have the time to fully test the code
now, but I think it will work, or at least point you in the right direction.
Of course, you will need to tweak it for the names of your tables, fields,
etc.
Note that the code could be directly in a command button click event, or the
function could be called from the click event.
When you say there is no problem with the table setup, do you mean there
is
a Patient table, and Antibody table, and a PatientAntibody junction table?
If so, do you intend to select from a listing of all antibodies the ones
that will be entered into the PatientAntibody table, then have several of
the related records created at the same time?
- Show quoted text -
Yes, That's exactly what I mean.
Dave
