[RESOLVED] Problem Writing to Database?

[jholokai02]

I am getting an error, not sure why, still learning db development, could someone please give me a hand.

ERROR: Current recordset does not support updating, this may be a limitation of the provider or a selected lock type.

Public Function CheckIfConnected()

DataBaseName = "Timesheet.mdb"
NewDataBasePath = ""

DataBasePath = "M:\Timesheet\Timesheet Program\" & DataBaseName

strConnecting = "Provider=Microsoft.Jet.OLEDB.4.0" _
                & ";Data Source=" & DataBasePath _
                & ";Persist Security Info=False"

dbCon.ConnectionString = strConnecting
dbCon.CursorLocation = adUseClient
dbCon.ConnectionTimeout = 3

On Error GoTo ErrorTrap

dbCon.Open strConnecting


ErrorTrap:

If dbCon.State = 1 Then
    'do nothing
    'MsgBox "M:\"
Else
    
    DataBasePath = "C:\" & DataBaseName
    
    strConnecting = "Provider=Microsoft.Jet.OLEDB.4.0" _
                    & ";Data Source=" & DataBasePath _
                    & ";Persist Security Info=False"
    
    dbCon.ConnectionString = strConnecting
    dbCon.CursorLocation = adUseClient
    dbCon.ConnectionTimeout = 3
    
    dbCon.Open
    'MsgBox "C:\"
End If

NewDataBasePath = DataBasePath

Private Sub cmdLogin_Click()

Dim PauseTime, Start, finish, TotalTime
Dim mb As String

CheckIfConnected

SqlStat = "SELECT Employees.Employee, Employees.Id_Number" _
& " From Employees" _
& " GROUP BY Employees.Employee, Employees.ID_Number" _
& " HAVING Employees.Id_Number='" & txtIDNumber.Text & "'"
                                                      
rs.Open SqlStat, dbCon

If rs.EOF Then
    
    MsgBox "Please Re-Type Your ID Number", vbInformation

GoTo Ended

End If

mb = MsgBox("Verify that you are " & rs!Employee & ".  Is this correct?", vbYesNo, "Verification?")

If mb = vbYes Then

    txtEmployee.Text = rs!Employee
    txtDate.Text = Format$(Now, "mm/dd/yyyy")
    
    SqlStat1 = "SELECT TimeCard.IdNumber, TimeCard.Employee, TimeCard.Date, TimeCard.[TimeIn], TimeCard.[TimeOut], TimeCard.[IntTimeIn], TimeCard.[IntTimeOut]" _
      & " From TimeCard" _
      & " GROUP BY TimeCard.IdNumber, TimeCard.Employee, TimeCard.Date, TimeCard.[TimeIn], TimeCard.[TimeOut], TimeCard.[IntTimeIn], TimeCard.[IntTimeOut]" _
      & " HAVING TimeCard.Employee='" & txtEmployee.Text & "' AND TimeCard.Date= #" & txtDate.Text & "#"
    
    rs2.Open SqlStat1, dbCon, adOpenStatic, adLockOptimistic
    
    If rs2.EOF Then
        
        txtEmployee.Text = rs!Employee
        txtDate.Text = Format$(Now, "mm/dd/yyyy")
        txtTimeIn.Text = Format$(Now, "hh:mm")
    
       rs.AddNew 'This is where the error is          
          rs![IdNumber] = txtIDNumber.Text
          rs![Employee] = txtEmployee.Text
          rs![Date] = txtDate.Text
          rs![TimeIn] = txtTimeIn.Text
          
        rs.Update
        
        PauseTime = 2
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
        
        finish = Timer
        TotalTime = finish - Start
        
        txtEmployee.Text = ""
        txtDate.Text = ""
        txtTimeIn.Text = ""
        txtTimeOut.Text = ""
        txtIntTimeIn.Text = ""
        txtIntTimeOut.Text = ""
        rs2.Close
        GoTo Ended
        
    Else
        
        If rs2.EOF = False And rs2!TimeOut = "0:00" Then

            txtEmployee.Text = rs2!Employee
            txtDate.Text = rs2!Date
            txtTimeIn.Text = rs2!TimeIn
            txtTimeOut.Text = Format$(Now, "hh:mm")
            
            rs2.Fields("TimeOut") = txtTimeOut.Text
            
            rs2.Update
            
            PauseTime = 2
            Start = Timer
            Do While Timer < Start + PauseTime
                DoEvents
            Loop
            
            finish = Timer
            TotalTime = finish - Start
    
            txtEmployee.Text = ""
            txtDate.Text = ""
            txtTimeIn.Text = ""
            txtTimeOut.Text = ""
            txtIntTimeIn.Text = ""
            txtIntTimeOut.Text = ""
            rs2.Close
            GoTo Ended
    
        Else
    
            If rs2.EOF = False And rs2!IntTimeIn = "0:00" Then
                
                txtEmployee.Text = rs2!Employee
                txtDate.Text = rs2!Date
                txtTimeIn.Text = rs2!TimeIn
                txtTimeOut.Text = rs2!TimeOut
                txtIntTimeIn.Text = Format$(Now, "hh:mm")
        
                rs.Fields("IntTimeIn") = txtIntTimeIn.Text
                
                rs.Update
                
                PauseTime = 2
                Start = Timer
                Do While Timer < Start + PauseTime
                    DoEvents
                Loop
                
                finish = Timer
                TotalTime = finish - Start
        
                txtEmployee.Text = ""
                txtDate.Text = ""
                txtTimeIn.Text = ""
                txtTimeOut.Text = ""
                txtIntTimeIn.Text = ""
                txtIntTimeOut.Text = ""
                rs2.Close
                GoTo Ended
        
            Else
    
                If rs2.EOF = False And rs2!IntTimeOut = "0:00" Then
                    
                    txtEmployee.Text = rs2!Employee
                    txtDate.Text = rs2!Date
                    txtTimeIn.Text = rs2!TimeIn
                    txtTimeOut.Text = rs2!TimeOut
                    txtIntTimeIn.Text = rs2!IntTimeIn
                    txtIntTimeOut.Text = Format$(Now, "hh:mm")
                    
                    rs2.Fields("IntTimeOut") = txtIntTimeOut.Text
                    
                    rs2.Update
                    
                    PauseTime = 2
                    Start = Timer
                    Do While Timer < Start + PauseTime
                        DoEvents
                    Loop
                    
                    finish = Timer
                    TotalTime = finish - Start
            
                    txtEmployee.Text = ""
                    txtDate.Text = ""
                    txtTimeIn.Text = ""
                    txtTimeOut.Text = ""
                    txtIntTimeIn.Text = ""
                    txtIntTimeOut.Text = ""
                    
                    rs2.Close
                    GoTo Ended
                Else
                    GoTo Ended:
                End If
            End If
        End If
    End If

End If

Ended:

txtIDNumber.Text = ""

'dbCon.Close

closeconnection

[si_the_geek]

You haven't provided enough parameters for rs.Open - so it opens with the default options, which includes ReadOnly.

For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of this forum)

[jholokai02]

Thanks si, i did review the article, and it was alot of help. I also found that I was calling the wrong recordset.