2 fields and 2 listboxes not writing to tbl from form

[PinkLady50]

I have 2 fields and 2 listboxes not writing to tbl from the form. The 2 fields are getting data but not the correct data. I am not sure what is causing it.


I have code for the muti-select listboxes but the selections are not writing to the table. I was wondering if someone could take a look at the code and see what I am missing.

Private Sub cmdAddVAC_Click()
    
    Dim varItem As Variant    'Selected items
    Dim strDelim As String    'Delimiter for this field type.
    Dim i As Variant
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim db As DAO.Database
    Dim strCriteria As String
    
    Set rst = CurrentDb.OpenRecordset("tblPerfIssues")
   
    If Len(Me.cboAnalyst & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNumber & vbNullString) = 0 Then
        MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
        Exit Sub
    Else
        DoCmd.GoToRecord , , acNewRec
    End If
    Dim issuesValue As String, vacancyValue As String
    Dim i As Variant
    For Each i In Me.lstContractorIssues.ItemsSelected
        issuesValue = issuesValue & ", " & Me.lstContractorIssues.ItemData(i)
    Next i
    
    For Each i In Me.lstVacancyReason.ItemsSelected
        vacancyValue = vacancyValue & ", " & Me.lstVacancyReason.ItemData(i)
    Next i
    Me.lstContractorIssues = Right(issuesValue, Len(issuesValue) - 1)
    Me.lstVacancyReason = Right(vacancyValue, Len(vacancyValue) - 1)
                                  'All the fields are writing correctly to the table but the Contract Number,CLIN the 2 listboxes (ContractorIssues lstContractorIssues) (VACReason lstVacancyReason)
        .AddNew
        .Fields("Analyst") = cboAnalyst.Value
        .Fields("ReportDate") = txtReportDate.Value
        .Fields("ContractNumber") = cboContractNumber.Value     'This is writing the Contractor Name instead of the Contract Number
        .Fields("Contractor") = txtContractor.Value
        .Fields("MATO") = txtMATO.Value
        .Fields("ContractorOnset") = txtContractorOnset.Value
        .Fields("ContractorResolved") = txtContractorResolved.Value
        .Fields("ContractorComments") = txtContractorComments.Value
        .Fields("ContractorIssues") = lstContractorIssues.Value
        .Fields("VACTO") = cboTOvac.Value
        .Fields("VACCLIN") = cboCLINvac.Value                    'This is writing the Contract Number instead of the CLIN
        .Fields("VACCOR") = txtCORVAC.Value
        .Fields("VACMTF") = txtMTFVAC.Value
        .Fields("VACLabor") = txtLaborvac.Value
        .Fields("VACSite") = txtSiteVAC.Value
        .Fields("VACClinic") = txtClinicalVAC.Value
        .Fields("VACIndcov") = txtIndCovVAC.Value
        .Fields("MissedHours") = txtIndHours.Value
        .Fields("MissedShifts") = txtCovHours.Value
        .Fields("TOStart") = txtTOStart.Value
        .Fields("TOEnd") = txtTOEnd.Value
        .Fields("VACFrom") = txtVacFrom.Value
        .Fields("VACTo") = txtVacTo.Value
        .Fields("VACReason") = lstVacancyReason.Value
        .Fields("VACComments") = txtVACComments.Value
        .Update
        
        
        Set rst = Nothing
    
   
   
End Sub
Private Sub cboContractNumber_Click()
        Me.cboContractNumber = Me.cboContractNumber.Column(0)
        Me.txtContractor = Me.cboContractNumber.Column(1)
        Me.txtMATO = Me.cboContractNumber.Column(2)
        Me.cboTOvac.Requery
        Me.cboCLINvac.Requery
        'Me.cboTOHCW.Value = ""
        'Me.cboTOCDR.Value = ""
        'Me.cboTOTERM.Value = ""
End Sub
Private Sub cboTOVAC_Click()
        Me.txtTOStart = Me.cboTOvac.Column(1)
        Me.txtTOEnd = Me.cboTOvac.Column(2)
        Me.txtMTFVAC = Me.cboTOvac.Column(3)
        Me.txtCORVAC = Me.cboTOvac.Column(4)
        Me.cboCLINvac.Requery
        
End Sub
Private Sub cboCLINvac_Click()
        Me.txtLaborvac = Me.cboCLINvac.Column(3)
        Me.txtSiteVAC = Me.cboCLINvac.Column(4)
        Me.txtClinicalVAC = Me.cboCLINvac.Column(5)
        Me.txtIndVAC = Me.cboCLINvac.Column(6)
End Sub

[davegri]

If you have multi-column comboboxes, you can specify the column you want to use as:
cboContractNumber.column(x). X is zero for the first column, 1 for the second and so on

[PinkLady50]

I am doing just that for the Contract Number. However when I try to write the field with the selection it is not picking up the selection made.
I have Contractor Number, Contractor, MATO as 0,1,2. These are displaying correctly on the form but the selection once the Contract Number is selected is not being passed to the table.

[aytee111]

I suggest that you step thru the code, one line at a time. To do this, put a stop on a line of code where you wish to begin, such as the "Set rs" line. Click in the left margin of this line and it will put a stop and highlight the line.

Then click on the button on your form and when the code stops at this line, hit f8 to step thru the code one line at a time. Hold the mouse over a variable to see what it contains, also add "Debug.Print ...." lines here and there to see what variables contain at that particular point in time.

[PinkLady50]

All the cboboxes are writing correctly to the form. It's writing to the table that is getting the wrong information.

[davegri]

        .Fields("ReportDate") = txtReportDate.Value
        .Fields("ContractNumber") = cboContractNumber.column(x)    'This is writing the Contractor Name instead of the Contract Number
        .Fields("Contractor") = txtContractor.Value
        .Fields("MATO") = txtMATO.Value

Did you change the code like this?

[PinkLady50]

I changed it to .Fields("ContractNumber") = cboContractNumber.column(x) and it didn't work and then I tried .Fields("ContractNumber") = [Forms]![frmPerfIssues]![cboContractNumber] and this didn't work either.

[davegri]

See post #2 again. X has to be a number.

[PinkLady50]

I used .Fields("ContractNumber") = cboContractNumber.column(0)

[davegri]

OK. Look at the rowsource for the combobox. It's probably like "SELECT field0, field1, field2 from yourTable;"
combobox.column(0) will pull field0
combobox.column(1) will pull field1 and so on.
That's how to refer to specific columns in a combobox in your code.

If you don't specify a column, you will get the first column, column(0)

[PinkLady50]

This is how I fill the cboContractNumber . Attached is the qry for the cboContractNumber RowSource
[CODEPrivate Sub cboContractNumber_Click()
Me.cboContractNumber = Me.cboContractNumber.Column(0)
Me.txtContractor = Me.cboContractNumber.Column(1)
Me.txtMATO = Me.cboContractNumber.Column(2)
Me.cboTOvac.Requery
Me.cboCLINvac.Requery
'Me.cboTOHCW.Value = ""
'Me.cboTOCDR.Value = ""
'Me.cboTOTERM.Value = ""
End Sub
][/CODE]

[davegri]

Private Sub cboContractNumber_Click()
Me.cboContractNumber = Me.cboContractNumber.Column(0)
Me.txtContractor = Me.cboContractNumber.Column(1)
Me.txtMATO = Me.cboContractNumber.Column(2)
Me.cboTOvac.Requery
Me.cboCLINvac.Requery
'Me.cboTOHCW.Value = ""
'Me.cboTOCDR.Value = ""
'Me.cboTOTERM.Value = ""
End Sub

I don't think you want any of this code in the click event. If anywhere it should be in the after_update event.
The red line sets the combobox to itself. Makes no sense. Take that out.

I see that you have more combobox click events coded. The click event occurs when the combobox has the focus and the mouse is clicked. It doesn't care whether you have selected anything from the dropdown at that point. It will fire as soon as the mouse is clicked and if the combobox is empty, so be it - that's what the code will see.

IMHO, this DB code needs to be reworked in a major way. The only way I see for help to be forthcoming is for you to post the DB for someone to take a shot at.