Data in my database always have blank Characteres

'Cylinder TestForm
'Requirements: frm20.dll needs to be register.
'References Microsoft ActiveX Data Object 2.8 library
'           Microsoft Calendar Control 11.0
' the calendar Control needs to be register first by using the MSCAL.OCX
'Table used in SQL is PSHTable1

Option Explicit
Dim cn As New ADODB.connection
Dim Rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim binChanged As Boolean
Dim strsql As String


'This is loaded when the form is open first
Private Sub UserForm_Initialize()
    ' set the calendar of the date of today
    Calendar1.Today
    'set the variable to false (no change has been made to the form)
    binChanged = False
    'Retrieve the Work Order # from the WorkOrderEntry window
    lblWorOrd = WorkOrderEntryUpdate.WorkOrderNumber.Value
    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    'Check in the database if the record exists in the database
    strsql = " Select count(*) from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
    Set Rst = cn.Execute(strsql)

' If records exists, we load the data to the form
    If Rst.Fields(0).Value = 1 Then
        strsql = " Select * from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
        Set Rst = cn.Execute(strsql)
        txtRepTech = Rst.Fields("REPTECH").Value
        txtTesTech = Rst.Fields("TESTTECH").Value
        txtOilTemp = Rst.Fields("OILTEMP").Value
        txtBrePres = Rst.Fields("BREPRES").Value
        txtRodExt = Rst.Fields("RODEXT").Value
        txtRodRet = Rst.Fields("RODRET").Value
        txtBloPor = Rst.Fields("BLOPOR").Value
        txtNoExt = Rst.Fields("NOEXT").Value
        txtRelA = Rst.Fields("RELA").Value
        txtRelB = Rst.Fields("RELB").Value
        txtDate = Rst.Fields("DATE").Value
        Calendar1.Value = Rst.Fields("DATE").Value
        binChanged = False
    End If
End Sub

' change the binChanged value when a user click on one of  the field. it shows that something has been changed on the form
Private Sub txtBloPor_Change()
    binChanged = True
End Sub

Private Sub txtBrePres_Change()
    binChanged = True
End Sub

Private Sub txtNoExt_Change()
    binChanged = True
End Sub

Private Sub txtOilTemp_Change()
    binChanged = True
End Sub

Private Sub txtRelA_Change()
    binChanged = True
End Sub

Private Sub txtRelB_Change()
    binChanged = True
End Sub

Private Sub txtRepTech_Change()
    binChanged = True
    txtRepTech.Value = UCase(txtRepTech)
End Sub

Private Sub txtRodExt_Change()
    binChanged = True
End Sub

Private Sub txtRodReT_Change()
    binChanged = True
End Sub

Private Sub txtTesTech_Change()
    binChanged = True
End Sub

Private Sub txtDate_Change()
    binChanged = True
End Sub

Private Sub Calendar1_click()
    txtDate.Text = Calendar1.Value
End Sub

' this sub reformat the date field to be proper
Private Sub txtDate_AfterUpdate()
    Dim slash As String
    Dim num, num1 As String
    slash = "//"
    'num hold the data from the date field without the //
    num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))
    num1 = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 2))
    'if 12/12/1212
    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
    'if 12/12/12
    ElseIf Len(txtDate) = 8 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num1) = True Then
            txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 4, 2) & "/20" & Mid(txtDate, 7, 2))
            MsgBox ("Date good!!!!???!")
    ElseIf IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/20" & Mid(txtDate, 5, 2))
            ElseIf Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            Else
                MsgBox ("Wrong Date Format")
            End If
    Else
            MsgBox ("wrong Date format")
    End If
End Sub

Private Sub CmdSave_Click()
    Dim dummieString As String
    dummieString = "ok"
    SavingData (dummieString)
End Sub

'The x button on top of the form is clicked to close the form
Private Sub UserForm_QueryClose(cancel As Integer, CloseMode As Integer)
Dim AnswerFromBox As String
AnswerFromBox = ""
'if x is pressed(0) and the form has been modified then call the yesnomessage
    If CloseMode = 0 And binChanged = True Then
        ' changed has been made do we want to save the change?
        AnswerFromBox = YesNoMessageBox()
        If AnswerFromBox = "YesAndSaved" Then
             cancel = 0
        ElseIf AnswerFromBox = "NotSaved" Then
            cancel = 1
        ElseIf AnswerFromBox = "Cancel" Then
            cancel = 1
        ElseIf AnswerFromBox = "NoNeedSave" Then
            cancel = 0
        End If
    Else
        ' nothing changed in the form we can close safely
        cancel = 0
    End If
End Sub


Sub SavingData(ByRef IsSavedGood As String)
' The button Saved is clicked so we save the record
    If ValidateForm Then
        strsql = " Select count(*) from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
        Set Rst = cn.Execute(strsql)
        ' If records exists, we do an update
        If Rst.Fields(0).Value = 1 Then
            Call UpdateTable
        Else
        ' Else we do an insert
            Call InsertTable
        End If
        binChanged = False
        IsSavedGood = "GOOD"
    Else
    IsSavedGood = "BAD"
    End If
    
End Sub


 Function YesNoMessageBox() As String
    Dim Answer As String
    Dim IsSavedGood As String
    IsSavedGood = "123"
    Answer = MsgBox("Do you Want To save your changes?", vbQuestion + vbYesNoCancel, "Save?")
    If Answer = vbYes Then
        Call SavingData(IsSavedGood)
        If IsSavedGood = "GOOD" Then
            YesNoMessageBox = "YesAndSaved"
        Else
            YesNoMessageBox = "NotSaved"
        End If
    ElseIf Answer = vbCancel Then
        YesNoMessageBox = "Cancel"
    ElseIf Answer = vbNo Then
        YesNoMessageBox = "NoNeedSave"
    End If

End Function

' this function validate the form to make sure everything is ok in the data
Private Function ValidateForm() As Boolean
    Dim Errors As String
    Dim NumericCheck As Boolean
    Dim EmptyCheck As Boolean
    'if the field contains something different than a number then
    If IsNumeric(txtOilTemp) = False And Trim(txtOilTemp) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Oil Temp is wrong" & vbCrLf
    End If
    If IsNumeric(txtBrePres) = False And Trim(txtBrePres) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Breakway Pressure is wrong" & vbCrLf
    End If
    If IsNumeric(txtRodExt) = False And Trim(txtRodExt) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Rod Extended is wrong" & vbCrLf
    End If
    If IsNumeric(txtRodRet) = False And Trim(txtRodRet) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Rod Retracted is wrong" & vbCrLf
    End If
    If IsNumeric(txtBloPor) = False And Trim(txtBloPor) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Blocked is wrong" & vbCrLf
    End If
    If IsNumeric(txtNoExt) = False And Trim(txtNoExt) <> "" Then
        NumericCheck = True
        Errors = Errors & "-No Extarnal Leaks is wrong" & vbCrLf
    End If
    If IsNumeric(txtRelA) = False And Trim(txtRelA) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Relief A is wrong" & vbCrLf
    End If
    If IsNumeric(txtRelB) = False And Trim(txtRelB) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Relief B is wrong" & vbCrLf
    End If
    If Trim(txtDate) = "" Then
        NumericCheck = True
        Errors = Errors & "-Date Field is empty" & vbCrLf
    End If
    If Trim(Errors) <> "" Then
        MsgBox (Errors)
        ValidateForm = False
    Else
        ValidateForm = True
    End If
End Function

' this sub send the data in the table the record exists so we do an update
Sub UpdateTable()
        If binChanged = True Then
            cmd.CommandText = "UPDATE PSHTable1 set REPTECH='" & txtRepTech & "',TESTTECH='" & txtTesTech & "', " & _
            " OILTEMP ='" & txtOilTemp & "',BREPRES='" & txtBrePres & "', RODEXT = '" & txtRodExt & "', RODRET = '" & txtRodRet & "', " & _
            " BLOPOR = '" & txtBloPor & "', NOEXT = '" & txtNoExt & "', RELA = '" & txtRelA & "', RELB = '" & txtRelB & "' ," & _
            "DATE = '" & txtDate & "' WHERE WORKORDNUM ='" & lblWorOrd & "'"
            cmd.Execute
            binChanged = False
        Else
        End If
End Sub

' this sub send the data in the table the record doesn't exists so we create it
Sub InsertTable()
        If binChanged = True Then
            cmd.CommandText = "insert INTO PSHTable1 (WORKORDNUM,REPTECH,TESTTECH,OILTEMP,BREPRES,RODEXT,RODRET,BLOPOR,NOEXT,RELA,RELB,DATE)" & _
            "VALUES ('" & lblWorOrd & "' ,'" & txtRepTech & "' , '" & txtTesTech & "', '" & txtOilTemp & "', '" & txtBrePres & "', " & _
            " '" & txtRodExt & "', '" & txtRodRet & "', '" & txtBloPor & "', '" & txtNoExt & "', '" & txtRelA & "', '" & txtRelB & "', " & _
            "'" & txtDate & "') "
            cmd.Execute
            binChanged = False
        Else
        End If
End Sub

'this give the focus back to the type field in my WorkOrderEntryUpdate form
Private Sub UserForm_Terminate()
    WorkOrderEntryUpdate.Type.Focus
End Sub

Select allOpen in new window