[RESOLVED] form pulls half values from the DB

[salsa31]

hey
somwthing very strange
when i save a data in a form i see it in the listview and everything is ok
but when i want to edit i see only 4 values and the rest diappears from the list view and in the form text box and the combobox
its blank whan i edit.
cant figure out why
this is the code for the save and update

Private Sub Form_Activate()
    If NewRec Then
        Me.Caption = "New"
        BttnSave.Caption = "Save"
     PickDate.Value = Now
     DTfinal.Value = Now
    
    Else
        BttnSave.Caption = "Update"
        With FrmExpenses.LsVw.SelectedItem
            Me.Caption = "Update Expens"
            PickDate.Value = Format(.text, "dd/mm/yyyy")
            .SubItems(1) = txt(1).text
            .SubItems(2) = txt(2).text
            .SubItems(3) = txt(3).text
            .SubItems(4) = txt(4).text
            .SubItems(5) = Cmb1.text
            .SubItems(6) = Cmb2.text
            .SubItems(7) = Cmb3.text
            DTfinal.Value = Format(.text, "dd/mm/yyyy")
            
        End With
        ChkRepeat.Enabled = False
    End If
End Sub

Private Sub BttnSave_Click()
 
    If Len(Trim$(txt(1).text)) = 0 Then
        MsgBox "missing fields", vbInformation, ""
        txt(1).SetFocus
        Exit Sub
    ElseIf txt(2).text = 0 Then
        MsgBox "missing fields", vbInformation, ""
        txt(2).SetFocus
        Exit Sub
    ElseIf txt(3).text = 0 Then
        MsgBox "missing fields", vbInformation, ""
        txt(3).SetFocus
        Exit Sub
    End If
    
    If NewRec Then
        Dim NewID As Long
        NewID = NextID("ExpID", "Expenses")
        Dim strSQL As String
strSQL = "INSERT INTO Expenses "
strSQL = strSQL & "(ExpID, ExpDate, ExpType, ExpQuantity, ExpCost, ExpInvoice, ExpPament, ExpCredit, ExpBank, ExpFinal) "
strSQL = strSQL & "VALUES("
strSQL = strSQL & NewID & ","                           
strSQL = strSQL & "#" & MyDate(PickDate.Value) & "#,"    
strSQL = strSQL & "'" & RplS(txt(1).text) & "',"       
strSQL = strSQL & "'" & txt(2).text & "',"               
strSQL = strSQL & CCur(txt(3).text) & ","                
strSQL = strSQL & "'" & RplS(txt(4).text) & "',"           
strSQL = strSQL & "'" & Cmb1.text & "',"
strSQL = strSQL & "'" & Cmb2.text & "',"
strSQL = strSQL & "'" & Cmb3.text & "',"
strSQL = strSQL & "#" & MyDate(DTfinal.Value) & "#"

strSQL = strSQL & ")"
CN.Execute strSQL

        Set Itm = FrmExpenses.LsVw.ListItems.Add(, , Format(PickDate.Value, "dd/mm/yyyy"), , "dolar")
        Itm.Tag = NewID
        Itm.SubItems(1) = txt(1).text
        Itm.SubItems(2) = Replace$(txt(2).text, ",", ".")
        Itm.SubItems(3) = Replace$(txt(3).text, ",", ".")
        Itm.SubItems(4) = Replace$(txt(4).text, ",", ".")
        Itm.SubItems(5) = Replace$(Cmb1.text, ",", ".")
        Itm.SubItems(6) = Replace$(Cmb2.text, ",", ".")
        Itm.SubItems(7) = Replace$(Cmb3.text, ",", ".")
        Itm.SubItems(8) = Replace$(DTfinal.Value, ",", ".")
        
        If ChkRepeat.Value Then
            PickDate.Value = Now
            txt(1).text = ""
            txt(2).text = 1
            txt(3).text = 0
            txt(4).text = ""
            Cmb1.ListIndex = -1
            Cmb2.ListIndex = -1
            Cmb3.ListIndex = -1
            DTfinal.Value = Now
            
            ChkRepeat.Value = 0
            PickDate.SetFocus
            Exit Sub
        End If
    Else
        With FrmExpenses.LsVw.SelectedItem
         strSQL = "UPDATE Expenses SET "
strSQL = strSQL & "ExpDate = #" & MyDate(PickDate.Value) & "#,"
strSQL = strSQL & "ExpType = '" & RplS(txt(1).text) & "',"
strSQL = strSQL & "ExpQuantity = '" & txt(2).text & "',"
strSQL = strSQL & "ExpCost = " & CCur(txt(3).text) & ","
strSQL = strSQL & "ExpInvoice = '" & RplS(txt(4).text) & "',"
strSQL = strSQL & "ExpPament = ' " & Cmb1.text & "',"
strSQL = strSQL & "ExpCredit = ' " & Cmb2.text & "',"
strSQL = strSQL & "ExpBank = ' " & Cmb3.text & "',"
strSQL = strSQL & "ExpFinal = #" & MyDate(DTfinal.Value) & "#"
strSQL = strSQL & " WHERE ExpID = " & .Tag
CN.Execute strSQL
            .text = Format(PickDate.Value, "dd/mm/yyyy")
            .SubItems(1) = txt(1).text
            .SubItems(2) = txt(2).text
            .SubItems(3) = txt(3).text
            .SubItems(4) = txt(4).text
            .SubItems(5) = Cmb1.text
            .SubItems(6) = Cmb2.text
            .SubItems(7) = Cmb3.text
            .text = Format(DTfinal.Value, "dd/mm/yyyy")
        End With
    End If
    Unload Me
    FrmExpenses.Loadentries
End Sub

tnx for the help

[SuperDre]

Haven't checked the code, so sorry for that, but I do see one thing that might create problems (depending on your local settings).. you use CCur to write a value to the query, with my default local settings this would export 1,2 when you would export 1.2.. I do see you do a replace of ',' to '.' when you retrieve the data from the input and set it to the listview.. Also if you would enter "1,2" CCur would propably convert it to "12" which can't be what you want..
Are you sure the values you write to the database are stored correctly? if not for the decimal problems I mentioned, at first glance I can't see anything wrong with the code..

[salsa31]

the values are correct and they are stored in the DataBase the problem is
when i want to edit then i see only half of what is stored.

[SuperDre]

I don't see any code of the editing part, only code for saving your changes... So it's not clear how you even set your editing stuff..

[salsa31]

        With FrmExpenses.LsVw.SelectedItem
         strSQL = "UPDATE Expenses SET "
strSQL = strSQL & "ExpDate = #" & MyDate(PickDate.Value) & "#,"
strSQL = strSQL & "ExpType = '" & RplS(txt(1).text) & "',"
strSQL = strSQL & "ExpQuantity = '" & txt(2).text & "',"
strSQL = strSQL & "ExpCost = " & CCur(txt(3).text) & ","
strSQL = strSQL & "ExpInvoice = '" & RplS(txt(4).text) & "',"
strSQL = strSQL & "ExpPament = ' " & Cmb1.text & "',"
strSQL = strSQL & "ExpCredit = ' " & Cmb2.text & "',"
strSQL = strSQL & "ExpBank = ' " & Cmb3.text & "',"
strSQL = strSQL & "ExpFinal = #" & MyDate(DTfinal.Value) & "#"
strSQL = strSQL & " WHERE ExpID = " & .Tag
CN.Execute strSQL
            .text = Format(PickDate.Value, "dd/mm/yyyy")
            .SubItems(1) = txt(1).text
            .SubItems(2) = txt(2).text
            .SubItems(3) = txt(3).text
            .SubItems(4) = txt(4).text
            .SubItems(5) = Cmb1.text
            .SubItems(6) = Cmb2.text
            .SubItems(7) = Cmb3.text
            .text = Format(DTfinal.Value, "dd/mm/yyyy")
        End With
    End If

[SuperDre]

huh, you're talking about editing, and all you show is some code which saves your current edit to the database and put it's values in the currently selected listview item.. Also I see you set .text (so column1) twice.. Maybe you should show it through some screenshots, because I really don't understand from you description what is going wrong.. I never see the data you have stored in the database being set into your 'editing' screen..

[SamOscarBrown]

@SuperDre
I believe Salsa is referring to UPDATING, vice EDITING. I was trying to understand/determine what he means by 'the values are correct and they are stored in the DataBase the problem is when i want to edit then i see only half of what is stored. '---like, what is 'half'? So, IRC (I Remain Confused).

[salsa31]

hey sam
i accedently used a old project
i fixed this problem
tnx anyway
for you all
good day

[SamOscarBrown]

Roger!....please mark as Resolved....U2 have a great day.