Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: subtracting values from specific records

    (access 2003)

    My form (FormMine) has a list box that has several possible values (A, B, & C) and a button (no command yet).

    I also have a table (2FldTable) with two fields: "Letter", which is constant, and contains the same values as the list box in the form above -but it is not queried or related to the list box. And the field "Amount", which is a long integer field that at anytime, may have the value of a random positive whole number.

    The list box and the "Letter" field has no recurring values. The "amount" field may have duplicate values.

    How do I set the button to: on click, subtract 1 (rather, add -1) to the amount field of the current value selected in the list box?

    Thanks =D


    ...if anyone is curious. I'm asking about this for a delete record function. I'm working on a form that generates a multiple sequential alphanumeric ID (its kinda working now) that bases the next number on a "last_item_num" field.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could either write some VBA to do it, or an UPDATE query that has some criteria in it that refers to the listbox on your form.

    I'd go for VBA
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    Thanks!

    I just got the update query running.
    ---

    EDIT:

    I found a second flaw in record deletion. I can't delete records with ID codes that are not the latest. If I my latest is A04 and I delete A01, The next ID the computer will generate is still A04, then A05. It won't reuse the already deleted A01 and makes a second A04. Which is kinda bad and also causes a slight bump since primary keys can't have duplicates.

    Seems like this will take a little more research before I can make a fix.
    Last edited by coffeecat; 07-02-08 at 00:45.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How could the computer generate A04 a second time? Certainly not with AutoNumber. Therefore whatever is generating A04 contains the flaw.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like you are counting records and adding one, rather than finding the max number and adding one. The first would get screwed up by deletions, the second wouldn't.
    Paul

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    Oh, sorry, when I meant that the computer would generate a number, I meant to say via a function call. Anyway, I used a code I found while I was reading up on VBA.

    Here's the part that generates the next code in sequence or inputs a new letter code with a value of 1 in the last item number field:

    If lrs.EOF = True then

    string1 = "insert into ID_Code (ID_Key, Last_Item_Num)"
    string1 = string1 & " values "
    string1 = string1 & "('" & pSID & "', 1)"

    db.execute string1, dbFailOnError

    stringA = pSID & "-" & Format (1,"0000")

    else

    string2 = "Update ID_code"
    string2 = string2 & " set Last_Item_Num = " & Lrs ("Last_Item_Num") + 1
    string2 = string2 & " where ID_Key = '" & pSID & "'"

    db.execute string2, dbFailOnError

    end if
    The script works nicely, I could figure as much as knowing which field/table names to replace with my own, but most of the VBA lingo is still beyond me.

    My delete button launches a query via
    Dim stDocName As String

    stDocName = "Qry_IDdel"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    The computer generates A04 a second time because when I use a delete record function that also subtracts 1 from the last number used field (I deleted A01), the value of last_item_num becomes 3.

    For now, I'm considering not adding a delete function for old records (just the latest one), and adding a new field that simply states whether the data is active or inactive/void. I'll have numerical gaps in the ID sequence when querying active records, but that's negligible. =)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I have to say this is VASTLY more complex than it needs to be.

    The code MUST be faulty as it's not dealing with the situation. I don't know how pSID is being calculated, but it's wrong.

    Don't give up on your delete functionality. Instead re-write the code to cope with it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And this would appear to be where the value is coming from, which you haven't shown:

    Lrs ("Last_Item_Num")

    Which appears to be from a recordset? If so, what's the SQL?
    Paul

  9. #9
    Join Date
    Jun 2008
    Posts
    163
    thanks again guys, I appreciate the time you give to read my questions. anyway:

    SID and StID are fields in table "List_Enrollees".
    ID_Key and Last_Item_Num are fields in table "ID_Code".

    these are my declarations:

    Dim db As Database
    Dim LSQL As String
    Dim LUpdate As String
    Dim LInsert As String
    Dim Lrs As DAO.Recordset
    Dim LNewSID As String

    Set db = CurrentDb()

    LSQL = "Select Last_Item_Num from ID_Code"
    LSQL = LSQL & " where ID_Key = '" & pStID & "'"

    Set Lrs = db.OpenRecordset(LSQL)
    ----

    @startrekker:

    Okay, I'll try thinking of other ways to do it. Re-writing the code IS the answer, I just need to read up more so I can create a code on my own.

    @pbaldy
    it's in the code above now

    ..It would have been easier if I just posted the whole code earlier. T_T;

Posting Permissions

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