Text Box values based on a list box

J

Joanne

Hello, I have the following code for an after update event on a textbox.
What I am trying to do is have the text box pick up the value of the second
column in the items selected collection from the list box. As I iterate
through the code, it does work but the macro is picking up the first column
values as well. The first column is the primary key, an indexed value for a
# that identifies the judge. For instance the first column (the bound
column) may have the #1 for the first judge on the list as the "judge id",
then in the second column will be the name of the judge, e.g. "Ruth Bader
Ginsberg". But the text box is displaying both the number and the name. I
can't see what I'm doing wrong anymore. I've just been working on this too
long. Thank you in advance for any help you can provide.

Private Sub JusticeAuthorOfMajority_AfterUpdate()
'On Error GoTo Err_JAOM

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim strSql As String
Dim strSql2 As String
Dim blnInTransaction As Boolean
Dim varItem As Variant
Dim varTxtSelect As String
Dim strList As String
Dim intI As Integer

If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)
Set db = ws.Databases(0)

'Delete existing choices for this record
ws.BeginTrans
blnInTransaction = True
strSql = "DELETE FROM tblChosenJustices WHERE CaseName=" &
Form_frmAllData.CaseName
db.Execute strSql, dbFailOnError

With Me.JusticeAuthorOfMajority

'Add new choices for this record
For Each varItem In .ItemsSelected
strSql = _
"INSERT INTO tblChosenJustices" & "" _
& "(CaseName,JudgeID)" & _
"VALUES (" & _
Form_frmAllData.CaseName & "," & .ItemData(varItem) & ")"
db.Execute strSql, dbFailOnError
'strList = strList & .Column(1, varItem) & vbCrLf
Next varItem
End With
ws.CommitTrans
blnInTransaction = False

For Each varItem In Me.JusticeAuthorOfMajority.ItemsSelected
For intI = 0 To Me.JusticeAuthorOfMajority.ColumnCount - 1
strList = strList & Me.JusticeAuthorOfMajority.Column(1, varItem) &
vbCrLf
Me.txtAuthorofMajority.Value = strList
Next intI
Next varItem
 

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

Top