dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] I Cant figure this Out Update Sold Remain Via Sql

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Resolved [RESOLVED] I Cant figure this Out Update Sold Remain Via Sql

    i have a form that handles treatments subscriptions magazines

    e.x a customer buy 12 subscriptions of a magazine
    now he pays me all the money for the 12 subscriptions
    and every time he takes a magazine i reduce from the 12 1 magazine or even 2
    the problem is i got messed up with the sold & left(remain) magazines form this customer
    + it updates the same customer name in 2 diffrent rows.
    or if he take 2 magazines of the 12 then 10 is left right?
    well the table shows me - 70
    i dont knw why
    i tried to figure it out for a hour.
    this is my code
    Code:
    strSQL = "INSERT INTO Subscribers "
    strSQL = strSQL & "(ID, SubEmpName, SubDate, SubCust, SubTreatment, SubQuantity, SubSold, SubLeft, SubPaymentMethod, SubPrice, SubTashlomim, SubTotal) "
    strSQL = strSQL & "VALUES("
    strSQL = strSQL & NewID & ","
    strSQL = strSQL & "'" & RplS(CmbName.text) & "'," 
    strSQL = strSQL & "#" & MyDate(DTstart.Value) & "#,"
    strSQL = strSQL & "'" & RplS(TxtDesc.text) & "'," ' name of the customer
    strSQL = strSQL & "'" & RplS(CmbTreat.text) & "',"
    strSQL = strSQL & "'" & RplS(Txt(1).text) & "',"'quantity of the magazines the customer purcase
    strSQL = strSQL & "0,"
    strSQL = strSQL & "'" & RplS(Txt(1).text) & "'," ' quantity of the magazines the customer purcase
    strSQL = strSQL & "'" & RplS(CmbPayMethod.text) & "',"
    strSQL = strSQL & CCur(RplS(Txt(6).text)) & ","
    strSQL = strSQL & "'" & RplS(CmbTashlomim.text) & "',"
    strSQL = strSQL & "'" & RplS(Txt(0).text) & "'"
    strSQL = strSQL & ")"
    CN.Execute strSQL
    Code:
        With FrmSubscribe.LsVw.SelectedItem
             strSQL = "UPDATE Subscribers SET "
    strSQL = strSQL & "SubDate = #" & MyDate(DTstart.Value) & "#"
    strSQL = strSQL & " WHERE ID = " & .Tag
    CN.Execute strSQL
    strSQL = "Update Subscribers set SubSold = SubSold - " & RplS(Txt(1).text & ",")
    strSQL = strSQL & " SubLeft = SubLeft - " & RplS(TxtWorked.text)
    strSQL = strSQL & " where SubCust = '" & RplS(TxtDesc.text) & "'"
    CN.Execute strSQL
    txt(1).text 'Hold's the Quantity Purchased
    TxtWorked.text Hold's the magazines he takes 'each month or even 2 in a month
    TxtLeft.text ' the remain of the customers magazines
    i am really confused here
    what ever method i try its getting messy

    these is the table values
    Code:
     Name of the table Subscribers
    ID Number
    SubEmpName text
    SubDate Date/Time
    SubCust text
    SubTreatment text
    SubQuantity text
    SubSold text
    SubLeft text
    SubPaymentMethod text
    SubPrice Currency
    SubTashlomim text
    SubTotal Currency
    if any more info is needed i will add
    appreciate any help
    regards salsa31

  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,346

    Re: I Cant figure this Out Update Sold Remain Via Sql

    This is the same as the hair dresser program you were/are working on. I am sure you have already been given the answer in one of those threads.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  3. #3
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,423

    Re: I Cant figure this Out Update Sold Remain Via Sql

    The 2nd code you posted, where is it called from? from an event/sub.. where?

    Maybe that code is being executed so many times in your code that SubSold becomes negative.

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,182

    Re: I Cant figure this Out Update Sold Remain Via Sql

    How come all your numeric 'values' in your table are TEXT fields? Those really should be NUMERIC fields. Computers have a difficult time subtracting STRINGS (text) from Strings.
    I would first change those fields that use numbers (like SubSold and SubLeft) to NUMERICS. Then I would try that update query (and call it only ONCE of course) and see what you get. (Make sure your syntax is correct for using numeric in your query.) (And PLEASE, always put a MsgBox or a Debug.Print to see what your query is telling the Database BEFORE you try to run it....I have told you this over and over....IF YOU DO THAT, you will solve 99.999999% of all your queries yourself.)
    Sammi

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Re: I Cant figure this Out Update Sold Remain Via Sql

    The 2nd code you posted, where is it called from? from an event/sub.. where?
    ןit is from the same form

    i changed it sami still no luck


    This is the same as the hair dresser program you were/are working on. I am sure you have already been given the answer in one of those threads.
    it is similer execpt the update

  6. #6

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Re: I Cant figure this Out Update Sold Remain Via Sql

    now i get syntax error in insert
    Code:
    strSQL = "INSERT INTO Subscribers "
    strSQL = strSQL & "(ID, SubEmpName, SubDate, SubCust, SubTreatment, SubQuantity, SubSold, SubRemain, SubPaymentMethod, SubPrice, SubTashlomim, SubTotal) "
    strSQL = strSQL & "VALUES("
    strSQL = strSQL & NewID & ","
    strSQL = strSQL & "'" & RplS(CmbName.text) & "',"
    strSQL = strSQL & "#" & MyDate(DTstart.Value) & "#,"
    strSQL = strSQL & "'" & RplS(TxtDesc.text) & "',"
    strSQL = strSQL & "'" & RplS(CmbTreat.text) & "',"
    strSQL = strSQL & CCur(TxtQuantity.text) & ","
    strSQL = strSQL & "0," 'sets the remain to 0
    strSQL = strSQL & CCur(TxtQuantity.text) & "," ' holds the quanttity
    strSQL = strSQL & "'" & RplS(CmbPayMethod.text) & "',"
    strSQL = strSQL & CCur(TxtPrice.text) & ","
    strSQL = strSQL & CCur(CmbTashlomim.text) & ","
    strSQL = strSQL & CCur(TxtPayMent.text) & ","
    strSQL = strSQL & ")"
    CN.Execute strSQL

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,952

    Re: I Cant figure this Out Update Sold Remain Via Sql

    Code:
        With FrmSubscribe.LsVw.SelectedItem
             strSQL = "UPDATE Subscribers SET "
    strSQL = strSQL & "SubDate = #" & MyDate(DTstart.Value) & "#"
    strSQL = strSQL & " WHERE ID = " & .Tag
    CN.Execute strSQL
    strSQL = "Update Subscribers set SubSold = SubSold - " & RplS(Txt(1).text & ",")
    strSQL = strSQL & " SubLeft = SubLeft - " & RplS(TxtWorked.text)
    strSQL = strSQL & " where SubCust = '" & RplS(TxtDesc.text) & "'"
    CN.Execute strSQL
    Based on what you said in the Op I would guess that the issue is that second SQL statement, specifically the where clause.

    If you have more than one record with the same customer name and you only want one of them to be updated then do not use that field as the key in your where clause as that tells it to update ALL records that match
    Instead you should only have 1 update statement and the where clause should be using the ID as your first one is doing.

    In other words it looks like you need to merge those to update statements to create one statement that updates all three fields where Id=

  8. #8

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Re: I Cant figure this Out Update Sold Remain Via Sql

    hey DM but if this customer make 2 subscriptions then what?
    it is 2 new ID's but the same name
    so if he buys a pc magazine and buys a phone magazine
    and then he comes to take 1 of the magazines then it will update the 2 ID's beacuse it has the same name

  9. #9

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Re: I Cant figure this Out Update Sold Remain Via Sql

    ok i thing i got it
    Code:
             strSQL = "UPDATE Subscribers SET "
    strSQL = strSQL & "SubDate = #" & MyDate(DTstart.Value) & "#"
    CN.Execute strSQL
    strSQL = "Update Subscribers set SubSold = SubSold + " & RplS(TxtQuantity.text & ",")
    strSQL = strSQL & " SubRemain = SubRemain - " & RplS(TxtQuantity.text)
    strSQL = strSQL & " where ID = " & .Tag
    CN.Execute strSQL
    Last edited by salsa31; Dec 9th, 2013 at 01:31 AM.

  10. #10

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,048

    Re: I Cant figure this Out Update Sold Remain Via Sql

    tnk you sir DM
    and tnx all for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width