how to update a field in a table with a value in the same record based on a form?

[Ryno Bower]

Is this even possible?

I'm working on a project and its based on months!
Everytime I click a a button cmdRunMonthEnd I would like it to do the following. I will just explain from the part where I don't get it right!

I have a table TrnControl with the following fields:
TrnYear, TrnMonth, CurrentBalance, 1,2 through to 12 (months).

I also have this form called RunMonthEnd. With the following unbound textboxes:
Txt.Year, txtMonth, txtNewMonth, txtCurrentBalan ce and a command button cmdRunMonthEnd.

As I said, everything works as I want up to the point where click the button. When I click the button I want the following to happen if it's even possible.

When it is clicked I want it to update the table called TrnControl.
it should take the value from the forms text box called txtCurrentBalan ce and update one of the month's (1-12). It should update to the field which is in the txtNewMonth.

So if the txtNewMonth is 3, then it must take txtCurrentValue and update the field called 3 in that same record where TrnYear is the same as txtYear on the form.

please help, it seems impossible to me! Is it?

Thanks
Ryno

[Rabbit]

Certainly possible. You could create a dynamic SQL string depending on what's in txtNewMonth.

But I would caution against keeping your data in such an unnormalized structure.

[NeoPa]

It's possible and relatively straightforward . Whether your wanting to do that as you've explained makes sense or is wise I doubt, but ultimately only you can know why you're wanting it (so I'll leave that side of things to you).

The form already has controls (which should be bound incidentally) for the fields in your table, which include all twelve numbered month fields (Please don't just use the number as a field name. It really isn't too clever and will probably result in many problems for you.), as well as an unbound control [txtNewMonth]. The code behind the button's Click event should determine the relevant control from the value found in [txtNewMonth] and copy the value of [txtCurrentBalan ce] into it. It may also make sense at this stage to save the record.

Well done for including so much helpful detail in your question by the way.

[Ryno Bower]

Thanks for the replies. Could one of you please give me an example of code I should use for this to happen. When I created the table I thought that using just the month number would be easier, as the txtNewMonth will only have the number value in it. Originally I had Mth1,2,3...

But you're the experts.

I would appreciate it if I can have some code example that I could use.

Thanks

[NeoPa]

I'm afraid not.

You are expected to try it for yourself first. We're not here to do things for you, but to help when you get stuck. You've been given the directions, now you must do what you can and proceed from there. We can help if you get stuck of course.

[Ryno Bower]

Haha...that's why I ask the question. I am stuck. I don't even know where to begin. I'm already confused for what to do after you said about I should not only use a number for the months.

Rabbit talks about a dynamic sql...how must I start if I don't know where and how?

I need help desperately.
At least a starting point.
Thanks

[Mihail]

I understand well ?
You have 12 fields, one for each month ?
If YES then remove this fields immediately. As Rabbit say such an unnormalized structure come with a lot of troubles.
Even if you'll accomplish the task now you will fall soon in more and more troubles.

So replace all this field with only one:
MonthNumber where to store the month number for your balance then rethink your database at all.

This link is very useful (the best I can say) for a beginner.

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

Good luck !

[Ryno Bower]

Hi,

I need to have all the months in the record as I need to update the month value on every month end.
The month need to update through the whole year and store the values for the selected month.

If I run month end for the 3rd month, the value must be stored for that month. If its the 4th month, same needs to happen.
I don't understand what you mean to replace all the months and make one month?

Really I need some sort of code to start this off as I really am even more confused.

Please help!!!

I can't post a code and then ask a question for help if I don't even know where to start.

Please help

[Mihail]

Not make one MONTH.
Make one FIELD where to store the month number.

Are you follow the link I give you ?
Until you will understand the idea for http://bytes.com/topic/access/insigh...ble-structures, is a wasted time for every one that wish to help you.
Sorry for that. It is MY opinion. Not necessary a true one.

[Ryno Bower]

Yes if I create a field to store the months. That would then create duplicate values for the trnYear as it is the primary key field.

I kust need a code to update the value of the month field based on the form as described in my first post.

[Mihail]

:) If you think so... good luck !

[NeoPa]

Ryno,

No-one's asking you to be coder of the year here. What you post would only be a start. However, if you cannot think of any code after we've spent time trying to explain these basic concepts to you then it seems we're wasting our time. I've already laid out the basic logic of the code for you in post #. This is a site for offering help to coders, not to code for other people for free. If you need it done for you then you need to think about paying someone for their expertise. Here, we offer help to fellow professionals and amateurs. If you're not prepared even to have a crack at it, after the basics have already been provided for you, then I don't see how you can consider yourself one of those.

As for the month fields, you might consider field names of M1, M2, ... M12 and control names on your form of txtM1, txtM2, ... txtM12. This avoids the silly situation where anything is named simply as an integer value.