2nd column of lstbox value not writing value to table

[PinkLady50]

I have a lstbox with two columns. Column 0 is Issue and Column 1 is the IssueKey which in this case is the letter P.

Problem: All of the fields are writing but the IssueKey field.

LstboxContractorIssues is setup with the following:



Control Source is ContractorIssues which has two fields ContractorIssue(Primary Key) and IssueKey. Column 2; Column Width 1";1"

Row Source
SELECT tblContractorIssues.ContractorIssue, tblContractorIssues.IssueKey FROM tblContractorIssues ORDER BY tblContractorIssues.ContractorIssue;

My form writes to the tblPerfIssues. It contains a lot of fields but most importantly are ContractorIssues (yes there is an "s" at the end) and IssueKey.

Also, the fields don't clear when the record is written to the table even though I have code to clear them.

Private Sub cmdAddPerfIssue_Click()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim val As String
     
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblPerfIssues")
   
    If Me.lstContractorIssues > "" Then
      MsgBox "Please select at least one item from Contractor Issues"
Else
     Me.lstContractorIssues.Selected
     
End If
    
          If Len(Me.txtContractorOnset & vbNullString) = 0 Then
                MsgBox "Please enter a Contractor Issue Onset Date"
            Else
            
              With rs  '<<--!!!!
                .AddNew
                rs.Fields("AnalystSpec") = cboAnalyst.Value
                rs.Fields("ReportDate") = Me.txtReportDate.Value
                rs.Fields("ContractNumber") = Me.cboContractNumber.Column(0)
                rs.Fields("Contractor") = Me.txtContractor.Value
                rs.Fields("MATO") = Me.txtMATO.Value
                rs.Fields("ContractorOnset") = Me.txtContractorOnset.Value
                rs.Fields("ContractorResolved") = Me.txtContractorResolved.Value
                rs.Fields("ContractorComments") = Me.txtContractorComments.Value
                rs.Fields("ContractorIssues") = Me.lstContractorIssues.Column(0)
                rs.Fields("IssueKey") = Me.lstContractorIssues.Column(1)
                
            End With '<<--!!!!
        End If
   
                Me.lstContractorIssues.Requery
                Me.txtContractorOnset.Controls = Null
                Me.txtContractorResolved.Controls = Null
                Me.txtContractorComments.Controls = Null
                Me.txtIssuekey.Controls = Null

I would appreciate any help you can provide.

[June7]

Did you mean RowSource has two fields?

Why save both ContractorIssues and IssueKey? Why not just the IssueKey?

IssueKey is a text type field?

If the listbox is bound to a field, what table is this form bound to?

Why do you need code to save a record and not a BOUND form?

Why are you using .Controls in the code? Just remove that.

[ssanfu]

One thing is that you are missing the update command. (.Update)

I changed the lines like this
rs.Fields("ReportDate") = Me.txtReportDate.Value
to
!ReportDate = Me.txtReportDat

It doesn't hurt to use ".Value", but the value property is the default property, thus unnecessary.

MsgBox "Please select at least one item from Contractor Issues"

this indicates the list box is a multi-select list box. You will have to use looping to get all selection in the list box.

Private Sub cmdAddPerfIssue_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim val As String   '<<---NOTE:"val" is a reserved word

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblPerfIssues")

    If Me.lstContractorIssues > "" Then
        MsgBox "Please select at least one item from Contractor Issues"
        Me.lstContractorIssues.SetFocus
        Exit Sub
    Else
        Me.lstContractorIssues.Selected  '<<<-- what is the purpose of this line??????
    End If

    If Len(Me.txtContractorOnset & vbNullString) = 0 Then
        MsgBox "Please enter a Contractor Issue Onset Date"
        Me.txtContractorOnset.SetFocus
        Exit Sub
    Else

        With rs  '<<--!!!!
            .AddNew
             ' **** I edited/changed thefollowing 10 lines  ****
            !AnalystSpec = Me.cboAnalyst
            !ReportDate = Me.txtReportDat
            !ContractNumber = Me.cboContractNumber.Column(0)
            !Contractor = Me.txtContractor
            !MATO = Me.txtMATO
            !ContractorOnset = Me.txtContractorOnset
            !ContractorResolved = Me.txtContractorResolved
            !ContractorComments = Me.txtContractorComments
            !ContractorIssues = Me.lstContractorIssues.Column(0)
            !IssueKey = Me.lstContractorIssues.Column(1)
            .Update      '<<----Missing!!
        End With    '<<--!!!!
    End If

    Me.lstContractorIssues.Requery
    Me.txtContractorOnset = Null
    Me.txtContractorResolved = Null
    Me.txtContractorComments = Null
    Me.txtIssuekey = Null


    ' OTHER CODE?????



    'clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

[PinkLady50]

SSANFU and June7
This is a single select listbox. I have the message box because when I run this command I need to make sure the user has selected at least one item for this record to write. I also require the ContractorOnset(date).

There are 5 sections of the form and each command writes a record depending on what the user is tracking.

 Me.lstContractorIssues.Selected  '<<<-- what is the purpose of this line??????

I thought I needed this to get the selected item from the lstbox.

All the items in the .Add list are writing except the IssueKey. I can't just write the IssueKey because it is not unique. It's a key I am using for filtering. It is a text field. For the Contractor section of the form the IssueKey will write a P. For the Vacancy part of my form I write a "V" for the IssueKey when a Vacancy Issue is selected from that listbox.

The two columns in the lstContractorIssue display and when I select an item from the lstbox it is writing the correct selection.

[QUOTE]
If the listbox is bound to a field, what table is this form bound to?

Why do you need code to save a record and not a BOUND form?

Why are you using .Controls in the code? Just remove that. [QUOTE]

The lstbox is bound using the tblContractorIssue and the form is bound to tblPerfIssues.

I need a command to save the record because I am only writing certain parts of the form.

I am using .Controls to clear the fields because I read it somewhere.

As you can see I'm a bit of a novice.

[Missinglinq]

If Me.lstContractorIssues > "" Then
MsgBox "Please select at least one item from Contractor Issues"

Also, this doesn't seem to make sense: You're saying if a selection for a Contractor Issue has been selected...(If Me.lstContractorIssues > "")...please make a selection!

Linq ;0)>

[PinkLady50]

Thanks. I changed it to

If IsNull(lstContractorIssues.Column(0)) Then
MsgBox "Please select at least one item from Contractor Issues"

Still can't figure out why my IssueKey won't write to my table. It is picking up Column(0) but not Column(1).

[PinkLady50]

I got it!!!
My txtIssuekey was pulling from the Control Source instead of the code.

Thanks to all for your patience and help!

[Missinglinq]

Glad you got it figured out!

Linq ;0)>