Values from multiselect listbox being written to a table

[Guest]

Hello,
I have a multiselect list box within a form. The form (frmCaseData) is
based on a table (tblCaseData), but the list box itself is pulling its
information from another table (tblJustices). tblJustices is simply a list
of all justices who have ever served on the Supreme Court. The user can
select one or many justices from the list box. What I would like to do is
write their choices back to the tblCaseData table for each record in the
form, so for instance in a case Smith vs. Jones, if Judge Ginsberg and Judge
Souter wrote the opinion on this case, both names would be written to the
Smtih vs. Jones case. I know I have to some how set up a parent/child
relationship with this but it's not quite coming together? Could anyone help
me with this? Thank you so much in advance.

 

[jahoobob via AccessMonster.com]

You should use two tables for this, one your current tbl and another for
opinions written. The form for opinions would be the child (subform) and the
form for cases would be the parent (main form.)
tblCase Data should have an ID primary key (No duplicates) and the opinion
would have similar foreign key (Duplicate OK.)

 

[Guest]

Thank you for your help. But the user has requested a listbox within the
form and I was hoping to be able to write the values of the listbox to the
table.

 

[Rino]

try to play with this code. It works for me.
------------------------

Private Sub cmdOK2_Click()

If IsNull(Summary) Then
MsgBox "You must populate summary field."
Exit Sub
Else

'DoCmd.RunSQL ("INSERT INTO dbo_QuoteMain(Quote_ID, Org_ID, Per_ID,
Quote_Num, Summary, FirstName, LastName, Address1, Address2, Address3,
City, State, Zip, Country, PriPhone, FaxPhone, EmailAddress,
ExpireDate, ShipState, ShipZip, AddDate, SubTotal, GrandTotal) VALUES
('" & Me![txtQuoteID] & "' , '" & Me![Org_ID] & "' , '" & Me![Per_ID] &
"' , '" & Me![txtQuoteNo] & "' , '" & Me![txtSummary] & "' , '" &
Me![FirstName] & "' , '" & Me![LastName] & "' , '" & Me![Address1] & "'
, '" & Me![Address2] & "' , '" & Me![Address3] & "' , '" & Me![City] &
"' , '" & Me![State] & "' , '" & Me![Zip] & "' , '" & Me![Country] & "'
, '" & Me![PriPhone] & "' , '" & Me![FaxPhone] & "' , '" &
Me![EmailAddress] & "' , '" & Me![ExpireDate] & "' , '" &
Me![ShipState] & "' , '" & Me![ShipZip] & "' , '" & Me![AddDate] & "' ,
'" & Me![txtTotal2] & "' , '" & Me![txtTotal] & "')")

GrandTotal = txtTotal.Value

Dim db As DAO.Database
Dim strSQLBase As String
Dim strSql As String
Dim i As Long


strSQLBase = _
"INSERT INTO dbo_QuoteItem (QuoteItem_ID, Quote_ID, " & _
"Per_ID, Org_ID, Quan, PartNum, Description, ListEach) " & _
" VALUES ('" & _
Me![txtQuoteItemID] & "' , '" & _
Me![txtQuoteID] & "' , '" & Me![Org_ID] & "' , " & _
"'" & Me![Per_ID] & "' , "


Set db = CurrentDb


With Me!lstResults2


For i = Abs(.ColumnHeads) To (.ListCount - 1)


strSql = strSQLBase & _
"'" & .Column(0, i) & "' , " & _
"'" & .Column(1, i) & "' , " & _
"'" & .Column(2, i) & "' , " & _
"'" & .Column(3, i) & "')"

db.Execute strSql, dbFailOnError


Next i

End With

End If

DoCmd.Close

Exit_cmdClose_Click:
Exit Sub
End Sub