Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12

    How to increment a text box containing the value of a field by 1 with a button press

    I am embarrassed to ask this, it has to be simple, but I cannot get this to work.
    I have tried FieldName.Value=FieldName.Value + 1 in the button's property sheet event on click. Nothing but errors.
    There is nothing in the code builder that will do this, but I can close the form with the button, ha ha.
    I was an ace with DB2 and got saddled this project because of my big mouth. Access 2010 is a little different, hmmm.
    I am just starting on VBA and I think Alt-F11 opens up a code window, that is where I am at now.


    So can someone kindly post the code, and where exactly I am supposed to put it? I am sure next month I can do this in VBA but I would really like to show a little progress now, <g> TIA

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Sounds as though you need to use the DMax() function, and then add 1 to that. Read about it in the help files. Post back if you then need more help.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12

    Object not found

    Quote Originally Posted by Bob Fitz View Post
    Sounds as though you need to use the DMax() function, and then add 1 to that. Read about it in the help files. Post back if you then need more help.
    So running with your hint, I used, in the on cleck even of the button:

    [DMax ("Payments",'Assets"="Payments","Assets"+1)


    [DMax ("Payments",'Assets")="Payments","Assets"+1


    [DMax ("Payments",'Assets")=DMax("Payments","Assets") +1

    None of them work. I have a table, 'assests' , which has a field, 'payments'. The field is filled by the form from the table, I want to press the button and have the field increment by one. Having the user do it by hand will, I know,have errors. Thank you for your timely reply.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You want something more like:

    Me!fieldname = DMax("fieldname","tablename") + 1

    Why do you want this? Why not use Autoexec field type? Do you want to depend on user not to forget to click button when required or what if they accidentally click twice? Now you need code to assure the record is not saved without this value and also so user can't accidentally increment more than once.

    Is this a multi-user database? There is a risk multiple users will get the same value if record is not committed to table quickly enough.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12
    Thanks for code, I could actually understand Joe it works. I assume I type it in on click box? I don't know another way, thas' why. Per your question, single user, when payment is received, user increments payments made by a person. Then i can calculate outstanding . Can't find or think of another way - get payment, increment customer record, print totals at end of day. And can't think of way to error trap that is workable, you know s way? Thanks for code, you saved me from a lot of bad road... <g>

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Quote Originally Posted by sandwicheguy View Post
    Thanks for code, I could actually understand Joe it works. I assume I type it in on click box? I don't know another way, thas' why. Per your question, single user, when payment is received, user increments payments made by a person. Then i can calculate outstanding . Can't find or think of another way - get payment, increment customer record, print totals at end of day. And can't think of way to error trap that is workable, you know s way? Thanks for code, you saved me from a lot of bad road... <g>
    Perhaps I am misunderstanding your intentions but it sounds to me as though you are trying to keep the total number of payments made by each customer in the field called "Payments". This should not be done. Better to use the DCount() function with criteria.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If you are trying to do a count for each customer then as Bob suggests for DCount the Dmax would also require criteria.

    Another approach is to do an aggregate query with grouping by customer that counts records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12
    'Yo Bob, Thanks for input. I understand I should not use field [Payments] and I should use the DCount() fuction with criteria. Hmmm. If I how to do it that way, I would. I played around with DCount() and DMax() and adding them in different places and I get nowhere. This OOP stuff is really hard to learn and I guess at some point I am going to have to learn iVBA. Right now I am just doing a loan database for a friend and I am stuck on how to decrement the amount owed when the client makes a payment. I should maybe pay someone do do it as I he is getting tired of my 'I'm working on it' responces. So much fun. ACESS is really sophisticated now, you can do anything, but I can't make a stupid button do anything but clear the page, and that from the code builder.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    In properly structured db, amount owed and remaining paymenst should be calculated and not saved. Saving aggregate data is usually a bad idea.

    Have a table with loan terms. Another table of loan payments. Have an aggregate (Totals) query sum and count the payments grouped by loan number. Join that query to LoanTerms. Calculate difference between loan amount and loan payments total.

    Google: Access loan database

    http://599cd.com/templates/access/lo...ube_Loan_Amort
    The sample databases offered are both executables so can't see code. Can see the table structure. The 2010 version crashes Access but the 2003 one does seem to work.

    This one is an mdb:
    http://www.access-programmers.co.uk/...d.php?t=152420
    It only calculates payment schedule. Not set up for payment transactions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12

    Oh! Two tables...

    [QUOTE=June7;167816]In properly structured db, amount owed and remaining paymenst should be calculated and not saved. Saving aggregate data is usually a bad idea.

    Thank you so much. Once you back off you can see the forest instead of the trees! That makes perfect sense and along with what others have said, it is starting to come together.

    I still have the original problem, backing off a bit, of updating a clients records when they make a payment. I was attempting to use a button on a form to increment, in the record filed [Payments], the amount paid by one payment and do queries and calculations from that point. I don't want the collection person to type in in the table field but all my attempts to do some form of 'payment=payment + 1' ends in failure. Can you offer a suggestion about how to tackle that? I understand this can get complicated by multi-users and error checking, but this is just a start, once I have it running I can make it better with these suggestions,

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Trying to say that saving this incremented value is likely not necessary. I don't really understand what purpose it serves. I expect each loan would need its own number sequence for payments received. As stated earlier, this means the DMax would need criteria. The criteria would be the LoanID. This sequence number can be calculated by using textbox RunningSum property that is available on reports. Review: http://allenbrowne.com/ranking.html

    Enter payment transaction (date, amount, loan number) then calculate balances when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    sandwicheguy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    12
    This is the missing part of the picture <g>. I think I can do it,will let you know. Seems I was on the wrong road entirely.

  14. #14
    Parsonshere is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2012
    Location
    Rusk
    Posts
    25

    SampleCode

    You can write a function and put it in a module. Then call the function from the click event in the form's module like this.
    I am just guessing as to table structure.

    sub cmdButton_click_event
    dim x
    x = nextlineitem
    me.txtBox = x ' be sure the textbox default name is changed so that it is not the same as the field Name, otherwise Access is confused.
    end sub


    Function NextLineItem() as long
    NextLineItem = DMax("fldID", "tblInfo", "fldOrdNo = " & y) + 1
    end function
    Last edited by Parsonshere; 04-14-2013 at 10:18 PM. Reason: Additional note

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Parsonshere, issues with this approach presented in post 4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. On button press should update the year
    By jamril in forum Access
    Replies: 3
    Last Post: 02-26-2013, 11:58 PM
  2. Replies: 3
    Last Post: 02-09-2013, 09:23 PM
  3. Replies: 15
    Last Post: 05-17-2012, 01:12 PM
  4. Press a button to combine two fields to open a PDF
    By Douglas Post in forum Programming
    Replies: 4
    Last Post: 02-11-2012, 02:02 PM
  5. Replies: 0
    Last Post: 07-05-2011, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums