Difference makes the DIFFERENCE

gallery/tryjesus_bl_nobg

Consulting - Quality

Categorised Menu List

Using ADODB.Recordset to update data

How to works: 

  1. Create a User Form
  2. place required controls as per context
  3. place a command button 
  4. name it as SAVE
  5. on SAVE click, the data on the user form will be transfered to database.

Develop one spread sheet with such functionality, Distribute the same excel file to all the team members.  Ask team members to update religiously.  Monitor the output sheet from database.

Excel, inherently has some disadvantages when compared with databases, hence Ms.Access is one such quick alternatives and their compatibility and ease of use is one prime factor for small works.

 

Context: 

Work being done by the team on the floor is to be monitored, including their breaks.  So, without investing much money on sohpisticated software, this can be one quick alternative, which can save, really a lot of money.

WORKING WITH ADODB.RECORDSET

Get Values and update recordset

Dim rstInput As New ADODB.Recordset
Dim rstCheckList As New ADODB.Recordset


Public Sub rstOpenInput()
Dim sqlStr As String
    Set rstInput = New ADODB.Recordset
    rstInput.Open "SELECT * FROM TbProcessorData", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
End Sub


Select Case strQueue
    Case "Break", "Approved", "Non-Approved", "Training", "Surplus"
        rstOpenInput
        rstInput.AddNew
            rstInput.Fields(1) = Me.ProcessorID_Cmb.Value
            rstInput.Fields(2) = strPValue
            rstInput.Fields(3) = timePValue
            rstInput.Fields(8) = timeCValue
            rstInput.Fields(11) = timeCValue
            rstInput.Fields(12) = timeGap
            rstInput.Fields(13) = strUserName
        rstInput.Update
    End Select

        If rstInput.State = adStateClosed Then rstOpenInput
        rstInput.AddNew
            rstInput.Fields(1).Value = ProcessorID_Cmb.Value    '--- PROCESSOR ID
            rstInput.Fields(2).Value = Text19.Value             '--- QUEUE
            rstInput.Fields(3).Value = strGWISNo                '--- GWIS NUMBER
            rstInput.Fields(4).Value = blNoSign                 '-------------- No signature record
            rstInput.Fields(5).Value = blSV2                    '---------------------- SV2 account
            
            If blSV2 = True Then                                '------------ Update SV2 account
                rstInput.Fields(6).Value = Combo211.Value
                rstInput.Fields(7).Value = Text39.Value
                rstInput.Fields(8).Value = Text45.Value
            Else
                rstInput.Fields(6).Value = ""       '-------- branch
                rstInput.Fields(7).Value = ""       '-------- Customer Number
                rstInput.Fields(8).Value = ""       '-------- Customer Name
            End If
            
            If blNoSign = True Then rstInput.Fields(7).Value = Text50.Value     '------------ No Sign Acct
                       
            If cmbCheckers.Value <> "CheckerName" Then             '-------------- Checker Name

'~~~~~~~~~~~~~~~~~~~~~~ BEGIN PROCESSOR ERROR LOG CHECKER ~~~~~~~~~~~~~~~~~~~~~~~~~
            sqlExtErrors = "Select * from ExtErrors"
            If rstExtErrors.State = adStateClosed Then rstExtErrors.Open sqlExtErrors, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
                rstExtErrors.AddNew
                    rstExtErrors.Fields(1) = "Checker"
                    rstExtErrors.Fields(2) = Me.cmbCheckers.Value
                    rstExtErrors.Fields(3) = Me.Text19.Value
                    rstExtErrors.Fields(4) = Left(Me.GWIS_No.Value, 44)
                    rstExtErrors.Fields(5) = "Internal" & "~" & Me.ProcessorID_Cmb.Value
                    rstExtErrors.Fields(6) = Me.txtCheckerError.Value
                    rstExtErrors.Fields(7) = CInt("1")
                    rstExtErrors.Fields(8) = Date
                    rstExtErrors.Fields(9) = strUserName
                rstExtErrors.Update
                Set rstExtErrors = Nothing

'~~~~~~~~~~~~~~~~~~~~~~~ END PROCESSOR ERROR LOG CHECKER ~~~~~~~~~~~~~~~~~~~~~~~~~~`
                rstInput.Fields(9).Value = cmbCheckers.Value
            Else
                rstInput.Fields(9).Value = ""
            End If
            
            If txtCheckerError.Value = "" Then               '------------ Checker Error
                rstInput.Fields(10).Value = ""
            Else
                rstInput.Fields(10).Value = txtCheckerError.Value
            End If

            rstInput.Fields(11) = Me.txtReason.Value
            rstInput.Fields(12) = timeCGwis
                timeGap = CDate(timeCGwis - timePGwis)
            rstInput.Fields(13) = timeGap
                timePGwis = timeCGwis
            rstInput.Fields(14) = strUserName
        rstInput.Update
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ UPDATE END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        UpdateControls   '~~ Procedure, either to clear controls or to update with standard values.
 

Issues you may encounter: While you copy and paste the code from here to code window, replace double quotes in code window again, as these quotes will appear as smart quotes.

 

Original Code, Company names and user names are replaced with false names.

Code file in .txt format

It is always advisable to make best use of status bar.  Application.StatusBar = "some text"

This will help end user to know that the work is in progress...