Update Table by Form (1 Viewer)

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
I have two tables.

1. CardDetails
Fields: CardNo
Balance
2.CardTopUp
Fields : CardNo
Amount

I created a form using CardTopUp Table. What i Want to do is when i click submit button

1. Insert the record to Cardtopup table
2.Update only Balance field on CardDetail according to CardNo. (I want to add Cardtopup Amount to the remaing Balance on Card Details table.

As a example is there is 100 balance and when i topup 200 i want to update balace field as 300.

Please tell me how to do that

I
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Feb 19, 2013
Messages
16,554
with databases you do not store values that can be calculated - you calculate as and when required. The calculation would be the sum of amounts in your cardtopup table. Although presumably you have another table for expenditure?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:41
Joined
Sep 21, 2011
Messages
14,051
Basically you do not.?

If you created a form using the CardTopUp Table you would have a bound form and it would all work automatically.?
As you have for some reason chosen to use an unbound form, you would need to use an Append query to add the record with data from the form controls. I believe you are just making work for yourself?
You would not add any top ups to the balance field, but run a calculation (DSUM() ) or a query to get the balance.?

The smae would apply to outgoings, except they would be negative and then the result would be the balance remaining.?

If you think about it, your initial balance is really just your first top up.?

HTH
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
with databases you do not store values that can be calculated - you calculate as and when required. The calculation would be the sum of amounts in your cardtopup table. Although presumably you have another table for expenditure?
Yes I have
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
Basically you do not.?

If you created a form using the CardTopUp Table you would have a bound form and it would all work automatically.?
As you have for some reason chosen to use an unbound form, you would need to use an Append query to add the record with data from the form controls. I believe you are just making work for yourself?
You would not add any top ups to the balance field, but run a calculation (DSUM() ) or a query to get the balance.?

The smae would apply to outgoings, except they would be negative and then the result would be the balance remaining.?

If you think about it, your initial balance is really just your first top up.?

HTH
CAn u tell me how to that i want to i am very new student for acesss
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:41
Joined
Sep 21, 2011
Messages
14,051
Just select the relevant table, and then select Create and then your particular form.?

That gives you a head start, then just amend to suit. Handy if you have a good few fields for the form.

Check out YouTube, there are plenty of videos on Access on there.
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
Just select the relevant table, and then select Create and then your particular form.?

That gives you a head start, then just amend to suit. Handy if you have a good few fields for the form.

Check out YouTube, there are plenty of videos on Access on there.
I was already created Form for CardTopUp by using CardTopUp Table. It has These fields.

CardNo
Amount

There is another table CardDetails It has these fields.
CardNo
Balance

In this field there is a amount .

What i want to do it when iTop up my card using CardTopUp form, when click submit button
1.Record that data to the CardTopUp table. (It's ok its working also now)
2.I want to update CardDetails Table Balance Field. Add topup amount to CardDetails balance field.
As a example in balance field abailable 1000 and i am topup 200 i want to see in carddetail table 1200

How to do?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Feb 19, 2013
Messages
16,554
if all you have in the card details table is card number and balance, you don't need the table at all.

I recommend you have one table for transactions with a field to define what type of transaction
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
What i want to do is there is expenditure also. i want to substract expenditure and add the topup amount to the card balance how to do it sir?
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
if all you have in the card details table is card number and balance, you don't need the table at all.

I recommend you have one table for transactions with a field to define what type of transaction
What i want to do is there is expenditure also. i want to substract expenditure and add the topup amount to the card balance how to do it sir?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:41
Joined
Oct 29, 2018
Messages
21,358
Hi All. This previous discussion might help clarify things. I think it's related to this thread.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:41
Joined
Sep 21, 2011
Messages
14,051
Why not have a look at the ready supplied templates.?
There is one called Financial Management that could do what you want.? or at least show you the concepts.
 

Bandara

Member
Local time
Today, 17:11
Joined
Apr 17, 2020
Messages
60
Why not have a look at the ready supplied templates.?
There is one called Financial Management that could do what you want.? or at least show you the concepts.
Can u give me a code to do it
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:41
Joined
Sep 21, 2011
Messages
14,051
No, I am not writing code for you.

1. It takes me time to write code, as I do not code that often.
2. Even if I did, you would learn nothing, and just be back asking for more.

I can see from another thread the way that would go.

Members here will steer you in the right direction, and often amend your code for it to work, but it is too much to expect them to write it for you, even if they can knock it up in a few minutes.?

My suggestion was to use a ready made template, which in effect will do what you are asking. Someone else has already written it for you. The drawback is that you will need to use it the way it was designed, or learn to change it.

You will likely receive a similar response wherever else you crosspost. At the end of the day it is you that needs to do the work.

If you listen to the experts here and get the structure correct first, there is a good chance you will not have to write much code at all.?
 

Users who are viewing this thread

Top Bottom