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
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