Error on Data Entry through Form?????? Should be simple...

[attrofy]

Howdy all,

I want to start by saying this forum is about the most usefull I have come across in quite some time - thanks to all the contributors.

Onto the question....I have three feilds I am trying to use to add data to my table. This should be simple to do, but I keep getting errors. I am trying to take a feild [Begin Number] add it to a user defined number [Number of Tags], and have the program add the numbers to fill in the final feild [End Number].

If I go to the control source for [End Number] and enter [Begin Number]+[Number of Tags], the calculations perform correctly, but it won't enter the data into the table. It leaves that feild blank. If I enter the formula as [tblIssue Tag]![Begin Number] + [tblIssue Tag]![Number of Tags], I either get "Error" or "?Name" in the feild.

If I try to enter an expression in the table default value, I am told I can only do functions in that feild. Also if I enter a default value in the form, I get a similar error message. I have a similar form that I have done the same operation (only I am taking the date and adding 59 days to it (=[Start Date]+59). It works fine). The only difference with this example is that I want to take the users input of [Number of Tags]. In the date example the computer generates [Start Date] with the Date() function. What am I doing wrong???? BTW, I have very limited knowledge of VBA.
Thanks in advance...

Russell

 

[gusbrunston]

Hi, Russell:

Even though it is a simple calculation, adding the user input number of tags to the beginning number still produces a calculated value. You're attempting to store the calculated value in the third field. "The Rules of Normalization" tell us that calculated values should not be stored. Rather, they should be calculated every time they're needed. The beginning number is stored, the number of tags is stored. Thus, whenever you need the sum of those two fields displayed, used, reported, or whatever, they should be recalculated. That's why Access doesn't make it easy to store them...

I once decided that The Rules of Normalization should be renamed, "Professional Database Programmers Retirement Program." But you eventually get the hang of it... Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[GingerR]

first, it's not good practice to store calculated numbers in a table. You can do calculations on the fly in queries, forms and reports. If you change any of the numbers you are using in the calculation within the table, the calculated field will not be updated.

that being said....

If I go to the control source for [End Number] and enter [Begin Number]+[Number of Tags], the calculations perform correctly, but it won't enter the data into the table. It leaves that feild blank.

that is because you are trying to do two different things at the same time. the control source for [End Number] has to be the field End Number from the table. that is how the program knows where to put data that is typed into that field in the form.

since however you want to calculate what goes into that field rather than type it in, i suggest this (secondary only to not doing it at all):

1) set the control source of [End Number] to the field [End Number] from the table

2) in the AfterUpdate event of both the [Begin Number] and [Number Of Tags] controls, put

Me.[End Number] =[Begin Number] + [Number Of Tags]
Me.Refresh

now when you change either of those two numbers, the field End Number will be calculated and saved in the table.

g

 

[attrofy]

Hmmmm....well isn't that a kick in the.....

That is what I arrived at and told everyone that was the way things would have to be, but invarriably the first question everyone asks is "What if I want to look up the tags by the last number????" Maybe I'll print your repones and tape it to my forhead.....but then they would probably ask why I have tape on my head.....

 

[attrofy]

Thanks for the response GingerR, I think I'll go with the first method of just not doing it. By using your suggested code, will that allow the value to be stored in the table or not? If I gather what your both saying, there is really no way of storing it in the same table. I guess the ideal solution would be to create a storage table and pass the calculated value to that table where it can be stored as a "real" value. But that seems too complex for me at this time. I'll just stock up on post-it notes and keep my forehead really clean....