Form Data not showing in Table (1 Viewer)

[eonelson]

I have a form that was created for data entry. For the majority of the information it automatically shows up in the table as it should. However, I have several fields that do not show up in the table. A couple of them are calculations and others are simple entry fields.

I have made sure that the fields are coordinated in both the table and the form, i.e. if currency, etc.

The information does show in the database view of the form.

I believe the problem goes back to the importing of information from a previous database -- because everything works on the data from the old database, the problem only shows up after the new records have been input.

I am totally at a loss on how to coordinate the two.

Thanks in advance

 

[RuralGuy]

I believe we will need additional information before we can take a reasonable guess as to the problem. You should be aware that as a general rule, calculations do not belong in fields; just recalculate them when you need the data. Are the controls that do save data bound to fields in the underlying table? In other words, do these controls have a ControlSource?

 

[eonelson]

form fields to table fields

Yes, the control source comes from another field in the same table (form). That does work in other parts of the form however, so it has to be something more than that. And, unfortunately, I don't have enough depth of knowledge to work my way through.

I've tried to attach a zip file of the db but it is too large. Not sure how to attach just the forms and tables.

The two issues I am having - that have the same problem.

1. frmFinancialFirstMeeting (the last 15 fields on the form) - some carry over and some don't
2. tblFinancialfirstmeeting (those fields on the last 8-9 entries only)

The second case:

1. frmRegistration, sbfMemberInfo - here the information for "counties" carries over to tblMemberinfo for the first 325 as names of counties and for the last 20 or so as the auto number from the look up table.


Thanks so much. The more I learn, the more I want to learn, but when I get a big road block I can't wrap around it gets very frustrating.

 

[boblarson]

I've tried to attach a zip file of the db but it is too large.

Make sure that BEFORE you try zipping the file that you use:

Tools > Database Tools > Compact and Repair

 

[eonelson]

zipped dbase

learn something new every day - and that is good.

 

[RuralGuy]

Just for the record: forms have controls and tables/queries have fields. Any control that has a calculation as its ControlSource is not bound to a field and so the results of the calculation will not be stored in a table. Here's some links with which you should probably become familiar:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

Provide the names of the controls that are not working as you would wish. Each control on your form has a unique name.

 

[eonelson]

controls

Controls not doing what they are supposed to do on two different forms:

frmFinancialFirstMeeting:

1. CurrentPremOpportunity Control Source: =[AveCwtMilkShipped]*[PotentialDifference] Tab 18

2. Milkprice/lb Control Source: =[MilkPrice] Tab 22 (price comes from earlier in form)

3. AveCostDiscardedMilk Control Source: =[# days]*[lbs/milk/day]*[MilkPrice] Tab 23

4. LostFromClinicalMastitis Control Source: =[TotalCostPerCase]*[NumberClinicalCasesLstMo]

The stymie to me is why calculations work and show in the earlier part of the form and not in the later part of the form.


frmRegistration - sbfMemberInfo

1. Combo Box: County Control Source: County Row Source Type: Table/Query Row Source: SELECT tblzCounty.County_ID, tblzCounty.County FROM tblzCounty ORDER BY [County];

In this control the information provided to the earliest part of the table is in name form. In information provided to the table after the db switch is in number form.

I understand about calculations not needing to take place until you run a report. However, in this instance we have field reps who pull up the tables in read only form to compare data between different farms. I wrote a query that basically pulls up the table - perhaps there is a better way to go in that. . but that is as far as I have gotten to give them what they want.

Thanks.

eileen

 

[RuralGuy]

Hi Eileen,
Everything I've looked at so far works if there are values in the underlying table. As soon as I key in values, the formulas work. Am I missing something?

 

[eonelson]

values in tables

I might be looking at it differently than you, if I understand what you are saying.

I understand you to say that if you put a value in the field of the table, the formula works. True. The formula works. But, I want to do the calculation while in the Form view and have the results automatically show up in the table view. In those instances I cited that isn't happening.

Maybe I'm missing something?

Thanks

eileen

 

[RuralGuy]

The values will only save to the table when the control is bound to a field (has the field as the ControlSource of the control). It will not save to a table if there is a formula in the ControlSource of the control. You would need to move the formula to the AfterUpdate events of the other controls and probably the Current event of the form in order to bind the control to a field so that puting a value in it will save to the field. Does that make sense?

 

[boblarson]

I think you are missing something. When data is entered into the form, it then calculates the information and when entering data into the form, it will automatically save the bound fields to the table, so if you come back to that record on the form the calculations will show up.

Again, per normalization of relational databases, you do not store calculated fields unless they cannot be recalculated by the base numbers. This is because storing calculated values is redundant data. You already have the base numbers so the calculations, if needed, can be recreated at any time during your process - in queries, forms and reports (you should never let users work directly in the tables).

 

[eonelson]

yes, that makes sense

It makes sense in that I like the way you gave me exactly what needs to be done. It doesn't make sense in the broad scope, but if it works, that is fine. I will do some playing around with it and let you know.

I got one of my other problems to finally work through some trial and error and comparing of fields from different forms. . . so I am more ahead of the game than I was.

Also, I have it set up so no one is working in the tables but me -- they are able to look at them in a "read only" through a querie I built.

Thank you so much for your patience.

I'll keep you posted.

eileen

 

[boblarson]

Also, I have it set up so no one is working in the tables but me -- they are able to look at them in a "read only" through a querie I built.

You can add your own fields in the query that can be calculated from the fields in the table. So, if you are showing them the query you can add the calculated fields into the query, in fact you could even create custom functions that would do the calculations and return them to the query so you wouldn't need to type in the calculation formulas frequently when creating new queries.

 

[eonelson]

queries

Bob,

At least I can finally understand most of what you are saying. That will be a next step once I get all of this other stuff figured out.

I really actually like learning this it is just that I get easily frustrated and my learning style is to ask a question at a roadblock and then figure out again until I come to an impasse. So thanks for the help. I'm sure you will hear from me again.

Eileen

 

[michaeljohannes]

Hi Bob,

I am new to this forum but am having the exact same problem.

If I want to add the data entered in two controls on a form and display it in another control, I understand the code to be as follows

frmControlThree = [frmControlOne] + [frmControlTwo]

(forgive me if the syntax reads strangely)

I understand the idea that a control on a form is not represented identically in a table unless the control source indicates to do so.

But how can I execute this procedure using Visual Basic code (after update) of the control on the form [frmControlTwo] so that it DOES update that "field in the table'

I think you're right, the relational database structure needn't have a calculated field in a table because it's redundant. But for the sake of this exercise, can you offer a possible solution to the above problem? I would be very appreciative of your time!

All the best,
Michael

 

[boblarson]

Michael:

What you can do is have the controlsource of the text box you want to display the two added text boxes:

=[YourTextBox1] + [YourTextBox2]

And then to save it in a bound field you can have a hidden text box that is bound to the field and in the form's BeforeUpdate event you can put

Me.YourHiddenTextBoxNameHere = Me.YourVisibleTextBoxNameHere

 

[michaeljohannes]

Bob,

Cheers! That is so simple and will work brilliantly!

Thank you kindly,
Michael

 

[michaeljohannes]

Hi Bob,

If I want to do the same function in a subform, will it work the same way?

I am having problems (it doesn't work) when I use that code in a subform (in datasheet view if that matters...)

Thanks,
Will

 

[boblarson]

Subforms have different syntax you have to use when referring to them and the controls on them.

Here's a good reference guide:
http://www.mvps.org/access/forms/frm0031.htm

The thing to make sure to realize too, is that when they say subform in the code, that they are referring to the name of the control that is the container on the main form that houses the subform. So, when someone says:

Forms!YourMainFormNameHere.YourSubformNameHere.Form.YourControlNameHere

Then when changing the name of YourSubformNameHere it would be the CONTAINER that houses the subform (although the container and the subform CAN share the same name, but it isn't always the case).

 

[michaeljohannes]

Hi Bob,

supposing I have a form called "WeekOf" and a subform called "WageTableSubform"

and... the calculated field I want to add the values is called "text36" and the control I want it to equal is called "ServiceLaborCost",

would the code look like this?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Forms!weekof.wagetablesubform.Form.Text36 = Forms!weekof.wagetablesubform.Form.ServiceLaborJob

End Sub

I'm close!

Thanks,
Michael