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

[sandwicheguy]

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

[Bob Fitz]

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.

[sandwicheguy]

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.

[June7]

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.

[sandwicheguy]

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>

[Bob Fitz]

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.

[June7]

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.

[sandwicheguy]

'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.

[orange]

Perhaps you should paste some sample code so we can see the actual issue in context.

[June7]

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.

[sandwicheguy]

[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,

[June7]

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.

[sandwicheguy]

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.

[Parsonshere]

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

[June7]

Parsonshere, issues with this approach presented in post 4.