Only new records with calculated fields are completly calculating. Existing records do not.

[mzimmer577]

I am using a form to input data into my database. Some fields require a manual entry. On a new record form, entering this manual data causes the calculated fields to perform perfectly. The base table, queries, and reports update as expected. Records that were created from an import from excel only partially calculate. I have deleted imported data and replaced it manually and the record behavior remains the same. I have checked that the calculations are the same in the table and in the form. Please help me in figuring this out. I have over 1300 records that will not completely fill out. This is my first experience setting up a database. Windows 10, Access 2019. Thank you.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

If any controls normally calculate because of other controls being updated, then these calcs won't compute just because some of them have data. Nor will a calculated control update if the update is event driven (e.g. AfterUpdate) when the control is updated via vba or macro. You could try the Recalc method for the form (Me.Recalc) for a few situations, but given that you have 1300 records with this issue, you may have to resort to an update query using calculated query fields.

[mzimmer577]

Thank you for your help. The few test records were existing and exhibit the issue I am having. If you add a new record, all fields/controls work as intended.

[June7]

On form, I select value in combobox for existing record and Category textbox calculates. Also shows in table. Neither happens for you?

Field name is [CATEGORY LOOK UP] but combobox named [CATAGORY LOOK UP]

Why are you saving Category and descriptive value as a single string? Set combobox:

ColumnCount: 2
ColumnWidths: 0";1"

RowSource: B;BOOKS;CL;CLOTHES;FF;FURNITURE & FIXTURES;G;GLASSWARE;J;JEWELRY;T;TOYS;H;HOLIDAY;BJ ;BETH JACOB;F;FABRIC;S;SILVER;C;CHINA & CERAMIC;CA;COSMETIC ACCESSORIES;D;DOLLS & FIGURINES;L;LINENS;M;MILITARY;MIS;MISCELLANEOUS;SE W;SEWING;SH;SHEET MUSIC;TL;TOOLS;G; GROOMING;W;WATCHES;LG;LIGHTERS

Only the Category code will save and calculated field not needed. Better, build a table of lookup values and use table as RowSource for combobox.

Advise not to build lookup field in table, just build combobox on form

[mzimmer577]

Sorry, I should have added more detail on what wasn't working. The [CATEGORY] does calculate correctly. Thank you for finding my typo.
I only set [CATEGORY LOOK UP] as a single string because I didn’tknow any better. Now I see how to do itbetter and more efficient.
I tried not do look up fields or calculations in thetable, but I was getting an error. Ithink it was “#field=?”. It cleared uponce I added the field to the table and added it to the form through “addexisting fields”.

It seems my issues start at [CONSIGNOR %]. I have the default value set at ‘=0’, but thefield shows blank. I tried ‘=Null’ andgot the same result. When I enteranything in the field, just the [CONSIGNOR NET] calculates for an existingrecord. If it is a new record, then[CONSIGNOR NET], [PENNEY NET],and [SHERYL NET] all calculate correctly.
Micron suggested using an update query using calculatedquery fields. I guess I am too new atthis to fully grasp what he was trying to tell me.
I haven’t done anything with the check boxes yet since Iwanted to get the form to work correctly first.
Thank you again.

[June7]

Okay, I entered data in all input fields and then all textboxes calculate. Don't get issue.

If fields in import dataset do not have value, they will set field in table to null or empty string and DefaultValue will not trigger.

Don't need = sign in DefaultValue property, although doesn't seem to hurt.

Advise not to use spaces nor punctuation/special characters nor ALL CAPS in naming convention. Better would be ConsignorPct, CategoryLookup, LastActionDate, etc.

Suggest you fix TabOrder property of controls.

Image control has invalid ControlSource. Saving objects in db uses up Access 2GB size limit and can slow performance in large datasets. If this is a concern, then leave images external and save path in text field.

[mzimmer577]

Thank you for the additional improvements. When you entered data, was it in a new formwhere the [ID] is showing (New). This instance works as intended. If you try and enter sold data in records1-13 (They are considered “Existing” since these were imported fromExcel). For those records, thecalculated fields stop at [NET] or [CONSIGNOR NET] if there is a value enteredin the [CONSIGNOR %] field.
Any record that is created after the excel import, continuesto fully work as intended. I can changeany input field and the calculated fields behave as desired.
I noticed that my info under my user name shows Access2013. I am using Access 2019. Hopefully there isn’t a difference betweenAccess 2019 and Access 2010.

[June7]

I entered data into existing records 1-13 and calculations work. Enter data in Commission, Penny Commission, and Sheryl Commission as well as Consignor% even if just 0. Arithmetic with Null returns Null. Number fields are Null if there is no data therefore calculations dependent on those fields return Null.

There are some differences but calculations should not be.

[mzimmer577]

THAT WAS IT!!! I was expecting a new record to behave like an existing record. I guess because of my use of "null", that caused the different behaviors. I'm WAY OK to require all fields be filled in. Now all I need to do is add your enhancements to my primary version and then I will be cookin with gas. Thank you so much. Take care.