Database

[odin1177]

I am almost done with this database, but I need help with a macro that will help me auto-populate 5 fields after a certain field is populated. I have I have part number data in a separate table tblPartNumbers and I have a form frmRipForm. There are 6 fields alike between these two unbound objects, as the rip form is bound to tblRipForm and the Part Numbers table is bound to nothing, and needs to stay that way. I want to push data based on a record when the the Part number field is populated, I know it will be an after update macro, but I am lost after that. The fields are description, tool number, cavities, cycle time, and customer. I have this data all stored on tblPartNumbers, and want to push these to the form, which has 6 identical fields, plus many more for data entry. Any help would be appreciated, as I am new to Access, and this is my first database.

Thank you for your time, and have a great day!

 

[odin1177]

Well, first thing that I would suggest is to not actually save this field at all. The reason being that it is a calculcated field and you *usually* do not want to store this information, as you can always calculate it like you mentioned.

Does anything depend upon this field being saved, or could it just be recalculated everytime?

It needs to be saved, as it is an item that is reported, and id the efficiency of the machine for that part, and that shift etc....

I believe that because it gets part of its data from the auto-pop fields it kind of behaves like a ghost, and I do not know what to do to fix it.
Unfortunately I need the calculated fields to save for reporting purposes. It is a really important piece of data that I need.

 

[Montez659]

Unfortunately I need the calculated fields to save for reporting purposes. It is a really important piece of data that I need.

I will say this, no you don't. If the data is properly stored (i.e., normalized) then the report can calculate all of this for you and you should NOT have to store a calculated field (at least in this instance).

That said, is this field bound to the table? Can you see the calculcated field value in the bare bones table view? It may be that the information IS stored, but your formulas are overwriting what you are seeing, and then resaving, and if the formula is being calculated at any time the record is popualted with no information, it will wipe out what was already there.

Now you see why it is a bad idea to store a calculated field.

 

[odin1177]

I will say this, no you don't. If the data is properly stored (i.e., normalized) then the report can calculate all of this for you and you should NOT have to store a calculated field (at least in this instance).

That said, is this field bound to the table? Can you see the calculcated field value in the bare bones table view? It may be that the information IS stored, but your formulas are overwriting what you are seeing, and then resaving, and if the formula is being calculated at any time the record is popualted with no information, it will wipe out what was already there.

Now you see why it is a bad idea to store a calculated field.

The data is not stored, but is bound to the table. all of my calculated fields but that one save the right way, and my report structure is done from a query first, and the report simply pulls the information from the query. The calculated fields show nothing unless the data they need to calculate is entered.

 

[Montez659]

I am having a hard time understanding how it can be a calculated field, bound to the table, and yet some of them work! I have no suggestions on this only to say to go back through and see if there is something that is different with this one field as compared to the ones that work.

I would almost think the way you have it structured that the one field not working is the norm, not the exception!

 

[xenou]

It is the Efficiency field. its formula is [Parts Per Shift]/[Part Capacity] Part capacity is one of the fields that has a formula, and auto fills when Cavities, and Cycle Time is populated, and those 2 are one of the ones that auto fill when part number is inputted.

Maybe a calculated field can't be based on another calculated field? Not sure as I've not used such a calculated field before...but that seems likely to me.

 

[odin1177]

I am having a hard time understanding how it can be a calculated field, bound to the table, and yet some of them work! I have no suggestions on this only to say to go back through and see if there is something that is different with this one field as compared to the ones that work.

I would almost think the way you have it structured that the one field not working is the norm, not the exception!

I set up tblRipForm first, and entered in all of the formulas before creating the frmRipForm, and brought them over using the add existing fields button.