Change from DAO to ADO causing issues

[PinkLady50]

I had a post about Listboxes under the Forms heading. It fixed the listboxes but it error'd on DAO so I changed all the code to ADO. (extreme novice).

Now it is erroring on rs ("Analyst") = cboAnalyst.Value

Private Sub cmdAddVAC_Click()
    
    Dim db As ADODB.Connection
    Set db = CurrentProject.Connection      ' Access gives an ADODB object too!
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "tblPerfIssues", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
   
    
    Dim i As Variant
    Dim issuesValue As String, vacancyValue As String
    On Error GoTo ErrorHandler
    
    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"
    Else
        If Not IsNull(txtContractorOnset) And Me.lstContractorIssues.ItemsSelected.Count = 0 Then
            MsgBox "Please select at least one item from Contractor Issues"
        Else
            'loop through the multi select list box lstContractorIssues to get issues
            For Each i In Me.lstContractorIssues.ItemsSelected
                issuesValue = issuesValue & ", " & Me.lstContractorIssues.ItemData(i)
            Next i
            'loop through the multi select list box lstVacancyReason to get reasons
            For Each i In Me.lstVacancyReason.ItemsSelected
                vacancyValue = vacancyValue & ", " & Me.lstVacancyReason.ItemData(i)
            Next i
            issuesValue = Right(issuesValue, Len(issuesValue) - 2)
            vacancyValue = Right(vacancyValue, Len(vacancyValue) - 2)
            With rs  '<<--!!!!
               rs.AddNew
               rs("Analyst") = Me.cboAnalyst.Value
                rs("ReportDate") = Me.txtReportDate.Value
                rs("ContractNumber") = Me.cboContractNumber.Column(0)
                rs("Contractor") = Me.txtContractor.Value
                rs("MATO") = Me.txtMATO.Value
                rs("ContractorOnset") = Me.txtContractorOnset.Value
                rs("ContractorResolved") = Me.txtContractorResolved.Value
                rs("ContractorComments") = Me.txtContractorComments.Value
                rs("ContractorIssues") = issuesValue
                rs("VACTO") = Me.cboTOvac.Value
                rs("VACCLIN") = Me.cboCLINvac.Column(2)
                rs("VACCOR") = Me.txtCORVAC.Value
                rs("VACMTF") = Me.txtMTFVAC.Value
                rs("VACLabor") = Me.txtLaborvac.Value
                rs("VACSite") = Me.txtSiteVAC.Value
                rs("VACClinic") = Me.txtClinicalVAC.Value
                rs("VACIndcov") = Me.txtIndVAC.Value
                rs("MissedHours") = Me.txtIndHours.Value
                rs("MissedShifts") = Me.txtCovHours.Value
                rs("TOStart") = Me.txtTOStart.Value
                rs("TOEnd") = Me.txtTOEnd.Value
                rs("VACFrom") = Me.txtVacFrom
                rs("VACTo") = Me.txtVacTo.Value
                rs("VACReason") = vacancyValue
                rs("VACComments") = Me.txtVACComments.Value
               rs.Update
            End With
        End If
    End If
ErrorHandler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
    Resume ErrorHandler_Exit
End Sub

[jwhite]

ADO hasn't been updated in years. You are better off sticking with DAO and find the root of your problem.

But if you must, this may help you: http://www.accessallinone.com/updati...n-a-recordset/

[PinkLady50]

Thanks... I 'changed it all back to DAO. I got this error.
Dim db As DAO.Database - 'User defined type not defined. I am not sure what to do to troublehoot this. Any suggestions. I posted my code in case it helps.

Option Compare Database  '<-- should be at the top of every code page
Option Explicit          '<-- should be at the top of every code page
 
Private Sub cmdAddVAC_Click()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Variant
    Dim issuesValue As String, vacancyValue As String
    
    On Error GoTo ErrorHandler
    Set db = CurrentDb()
    Set rs = db.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"
    Else
        If Me.lstContractorIssues.ItemsSelected.Count = 0 Then
            MsgBox "Please select at least one item from Contractor Issues"
        Else
            'loop through the multi select list box lstContractorIssues to get issues
            For Each i In Me.lstContractorIssues.ItemsSelected
                issuesValue = issuesValue & ", " & Me.lstContractorIssues.ItemData(i)
            Next i
            'loop through the multi select list box lstVacancyReason to get reasons
            For Each i In Me.lstVacancyReason.ItemsSelected
                vacancyValue = vacancyValue & ", " & Me.lstVacancyReason.ItemData(i)
            Next i
            issuesValue = Right(issuesValue, Len(issuesValue) - 2)
            vacancyValue = Right(vacancyValue, Len(vacancyValue) - 2)
            'All the fields are writing correctly to the table but the the 2 listboxes (ContractorIssues lstContractorIssues) (VACReason lstVacancyReason)
            With rs  '<<--!!!!
                .AddNew
                .Fields("Analyst") = Me.cboAnalyst.Value
                .Fields("ReportDate") = Me.txtReportDate.Value
                .Fields("ContractNumber") = Me.cboContractNumber.Column(0)
                .Fields("Contractor") = Me.txtContractor.Value
                .Fields("MATO") = Me.txtMATO.Value
                .Fields("ContractorOnset") = Me.txtContractorOnset.Value
                .Fields("ContractorResolved") = Me.txtContractorResolved.Value
                .Fields("ContractorComments") = Me.txtContractorComments.Value
                .Fields("ContractorIssues") = issuesValue
                .Fields("VACTO") = Me.cboTOvac.Value
                .Fields("VACCLIN") = Me.cboCLINvac.Column(2)
                .Fields("VACCOR") = Me.txtCORVAC.Value
                .Fields("VACMTF") = Me.txtMTFVAC.Value
                .Fields("VACLabor") = Me.txtLaborvac.Value
                .Fields("VACSite") = Me.txtSiteVAC.Value
                .Fields("VACClinic") = Me.txtClinicalVAC.Value
                .Fields("VACIndcov") = Me.txtIndCovVAC.Value
                .Fields("MissedHours") = Me.txtIndHours.Value
                .Fields("MissedShifts") = Me.txtCovHours.Value
                .Fields("TOStart") = Me.txtTOStart.Value
                .Fields("TOEnd") = Me.txtTOEnd.Value
                .Fields("VACFrom") = Me.txtVacFrom.Value
                .Fields("VACTo") = Me.txtVacTo.Value
                .Fields("VACReason") = vacancyValue
                .Fields("VACComments") = Me.txtVACComments.Value
                .Update
            End With '<<--!!!!
        End If
    End If
ErrorHandler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
    Resume ErrorHandler_Exit

End Sub

[jwhite]

Check in VB Editor, Tools / References that DAO is selected and does not show as MISSING:


For older mdb's: Microsoft DAO 3.6 Object Library
For newer accdb's: Microsoft Office XX.X Access Database Engine Object Library

[PinkLady50]

Thank you that fixed the db As DAO.Database error but now it is stopping on. When I hover over the fields it is showing the correct value from the form.

.Fields("Analyst") = Me.cboAnalyst.Value

[ohmydatabase]

I've never used db.OpenRecordset("tblX") before, but I am slightly suspicious of it. You may have to add a TableDef for that tblX in order for DAO to reference it. I could be wrong.

[PinkLady50]

thank you for your suggestion. Can you show me what code I should add.

[jwhite]

Instead of: .Fields("Analyst")
Use: !Analyst

and the same for the rest of the fields.

Reference: https://msdn.microsoft.com/en-us/lib.../ff845624.aspx

[ohmydatabase]

If you want to manipulate the table itself, is when you have to Dim tblX as DAO.TableDef if you're just pulling data from the table and using them you don't have to use it.
https://msdn.microsoft.com/en-us/lib.../ff195790.aspx

Definitely use bang (!) instead of dots, there is an official difference, I read it once and forgot it. What I do know is that when you open your recordset use bang. (analystStr = rs!Analyst)
I use SQL Queries with OpenRecordset. Theyre pretty straightforward and you can tailor them to grab pretty much any pieces of data you want. And then you don't have to mess with all the confusing structure of the recordset. Something along the lines of "SELECT * FROM tblPerfIssues WHERE Anaylist = 'Bobby'" would select everything about Analyst Bobby. You could also use INSERT INTO, DELETE, UPDATE, and many other to manipulate the table.

If what you got going on is working for you, please ignore me. This is just how I do it.

[PinkLady50]

I tried jwhite's suggestion and changed all the .fields to ! It still error'd on !Analyst but I was able to write the record using the Next Record at the bottom. It wrote all the fields except the two listboxes which are part of the code.

Still not sure why this is failing on that line. As always I appreciate the help.

[Micron]

a few things come to mind.
I always use rs.fields() with no problem. Here's info behind the bang operator
http://stackoverflow.com/questions/2...-and-ms-access
http://bytecomb.com/the-bang-exclama...erator-in-vba/

Always post the error number and a bit about the message. "It errors on.." is of little help to us. Possibly you have mis-spelled the table/field name, or there is an issue with the table or field reference. Details of the error would point us in the right direction, otherwise we're just guessing.

Also, be careful with these approaches:

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"

As of now, I don't see an issue, but if you add code between this If and the next, it will be processed as you are not explicitly exiting after the message. Right now, you don't have anything other than the cleanup routine.
Resume Next
If the error occurs within the With rs block, your resume statement should go to the next .Fields() line and continue anyway. Is that really what you want?

[PinkLady50]

Still errors even when is use rs.fields()
Run Tim Error 3265
Item not found in this collection.

rs.Fields(Anaylst) = Me.cboAnalyst.Value

[ssanfu]

Item not found in this collection.

In this case, usually means the field name is misspelled or the field in not in the record source.

rs.Fields(Anaylst) = Me.cboAnalyst.Value

Is the spelling of the field name correct??? Looks like the 'y' and the 'l' are transposed......

[HiTechCoach]

I would verify you have the field name Anaylst spelled the same as the name in the table.

I would use:

rs.Fields("Anaylst") = Me.cboAnalyst

or

rs!Anaylst = Me.cboAnalyst

[PinkLady50]

Thanks to All! Got it working!