Trouble with Calculated fields showing Primary Key Table ID instead of of data

[williamposey]

Hello!!!First time poster, long time reader...



I am making a CRM / Project management database and I am having trouble with calculated fields showing Primary Key Table ID instead of of data

In one table I have a "HOST name" with Unique ID.
In another table I have a "REMOTE name" with a unique ID
I then have another table with a "Specialty" Field and a a "Project Number" field


Project name field is a calculated field bringing all info together to create a unique Project Name

When I choose from a drop down list from both HOST and REMOTE table and then add a specialty I am supposed to get something like this: UCDavis/UCLA:Urology 7 but instead I get this 2/3:Urology 7 the numbers are the Primary Key for the School names..

i WOULD ATTACH MY FILE BUT CANT GET IT SMALLER THEN 500K . Any suggestions..

Also, shouldn't sub forms within a Main form NOT need to have the Main forms primary key ID listed/ added each time a new recorded is formed? Isn't that the point of the sub form to not need to keep adding the Main forms Primary key ID?

[Micron]

To attach files usually requires that the be compacted then zipped. Forum limit is 2 Mb, I think.
Sounds like you are creating calculated table fields - not advisable. Then again, you mention forms, so can't tell where the calculation is performed. Or is it concatenation?

As for subform relationship, do you have any parent/child fields set? Sometimes the appending of subform records is done as they are created, sometimes when clicking a button, sometimes when closing the main form. Depends on the situation and what you need to do, but in most cases you want to prevent the possibility of anyone attempting to create subform records when there's no primary record yet.

Welcome to the forum, btw.

[pbaldy]

If you do a compact/repair and then zip it should be small enough to attach.

The ID field issue sounds like you've used lookup fields. I don't use them or calculated fields, but you may need to create that field yourself instead of using a calculated field (they can't refer to other tables).

Check the master/child link properties of the subform control. Set properly, you shouldn't have to input the ID field.

[pbaldy]

Darn these slow fingers!

[williamposey]

Sorry really new at this.. The way the data flows I think I need to have the fields calculated...

[williamposey]

Its odd because in a different database i created I was able to make calculated field from a table out of look up fields.. Any \suggestions?

[Micron]

Darn these slow fingers!

Score: You 587; me 1.
I see there are 2 views already on the posted db, so I won't bother as I'm doomed to come in 3rd.

[pbaldy]

I see there are 2 views already on the posted db, so I won't bother as I'm doomed to come in 3rd.

I'm one of them but had to leave my computer, so feel free. You're guaranteed 2nd.

[williamposey]

Micron, I still haven't received a response to a fix.. I would very much enjoy what you have to say..

[Micron]

K. Got company. Will look when I get a chance.

[Micron]

You're probably not going to enjoy it after all.
god (with a little g) says "Thou shouldst refrain from the temptation to store calculations in table fields" and his word is good enough for me.
http://allenbrowne.com/casu-14.html

http://allenbrowne.com/ser-45.html

There's absolutely no upside to what you're doing, but there's plenty of downside. If this approach ever worked for you before, I have to guess that it's because the bound column from the combo(s) was not an autonumber ID field, but rather was some sort of meaningful data. I don't know if you can refer to a combo column in your table level expression and I'm not really inclined to test it anymore than I would be willing to test my tolerance to cyanide. If you can do so, it wouldn't be column 1 as you have designed your form combo row source. Yet to alter that row source to suit a table expression that cannot (?) contain a combo column reference would mean the loss of the ability to store the PK id of the parent table as a fk (foreign key) in any other table. If you do not understand what that means, then unfortunately, this indicates you skipped over table normalization and entity/relationship lessons. In that case, you're only headed for more grief.

Calculated table fields are just one of the many things Access allows in db design that simply shouldn't exist. These things only allow beginners to circumvent having to learn good db design, which helps to sell software I guess, and in many cases you'll find yourself unable to upgrade to more robust database servers. Before you refer to a plethora of sites that expound upon the virtues of such wondrous features, note that replication was once the cat's @$$ all over the web and in books. Now you can't even open a replicated db in Access 2016. Then there's ADO. M$ said it would replace DAO and we should all convert to the new religion. Guess what? They've stopped developing ADO (AFAIK) but guess what does continue to be developed? Not saying ADO doesn't have its place; I'm saying take everything with a grain of salt. I guess that means this rant is all for nothing....
A long story perhaps, but it may be good bedtime fodder. Or perhaps you like scary Access stories before lights out!
If you want some links on design principles and related info, let me know.

[williamposey]

Thanks for getting back to me Micron... Yes I have read that Calculated fields are not typically recommend however I need project names that are a combo of several entries and not based on what the data entry person wants to type. I have made this work many times before.. Here is an example of when it did work.. Any comments on why it works here but not in my master database.. All functions look identical.

[Micron]

Be that as it may, the generally accepted approach is to concatenate and calculate via forms, not store concatenations or calculations. Suppose one of the concatenated portions gets changed. Now you have to deal with changing every record where that is used, except you have to change only a portion of it. As if that isn't reason enough to not do so, you'd have the added benefit of not having special characters in data like you have since the form would inject them.

With no understanding of the business your db's support, nor how to use them, it's too difficult to figure out an exact answer to your question. The use of embedded macros doesn't help either. All I can offer is that if you're getting an autonumber id instead of a field value, you're referencing the wrong field of a recordset, query or table, or the wrong column from a combo or listbox. Don't know if you got an answer to your question in first post, but the answer is no. If you don't repeat the primary key field on every subform record as its foreign key, how else would you relate many order items to the one order id?

[ssanfu]

From Post #6

Its odd because in a different database i created I was able to make calculated field from a table out of look up fields.

I'll try and keep this short - but don't hold your breath.
================================================== =====================================

Looking at dB: VHN_CRM Database Prototype

table: Project

field: ProjectName (calculated)
expression: [HostPartner] & "/" & [RemotePartner] & ":" & [ProjectSpecialtyPractice] & " " & [ProjectID]

field: [HostPartner] (lookup field)
expression: SELECT [HOSTPartnerInstitutionInfo].[HOSTPartnerInstitutionName] FROM HOSTPartnerInstitutionInfo; <<<<-------

field: [RemotePartner] (lookup field)
expression: SELECT [HOSTPartnerInstitutionInfo].[HOSTPartnerInstitutionName] FROM HOSTPartnerInstitutionInfo; <<<<-------

[ProjectSpecialtyPractice] (field)
ProjectSpecialtyPractice

[ProjectID] (field)
autonumber PK
----------------------------------------------------------------------------------------------------------------


Looking at dB: MASTER VHN_CRM Database Working Project name with Numbers only 3_14_19 - Copy

table: Project

field: ProjectName (calculated)
[HOSTPartnerInstitutionName] & "/" & [REMOTEPartnerInstitutionName] & ":" & [ProjectSpecialtyPractice] & " " & [ProjectID]

field: [HOSTPartnerInstitutionName] (lookup field)
expression: SELECT [HOSTPartnerInstitutionInfo].[HOSTPartnerInstitutionID], [HOSTPartnerInstitutionInfo].[HOSTPartnerInstitutionName] FROM HOSTPartnerInstitutionInfo; <<<<-------

field: [REMOTEPartnerInstitutionName] (lookup field)
expression: SELECT [REMOTEPartnerInstitutionInfo].[REMOTEPartnerInstitutionID], [REMOTEPartnerInstitutionInfo].[REMOTEPartnerInstitutionName] FROM REMOTEPartnerInstitutionInfo; <<<<-------

[ProjectSpecialtyPractice] (field)
ProjectSpecialtyPractice

[ProjectID] (field)
autonumber PK
----------------------------------------------------------------------------------------------------------------

So the difference is
the 1st dB (VHN_CRM Database) has a text field as the first (bound) field in the 2 lookup fields and
the 2nd dB (MASTER VHN_CRM Database) has the PK field (in orange) as the first (bound) field in the 2 lookup fields


================================================== =====================================


Reiterating what Micron said, using lookup FIELDs and calculated FIELDs in tables are a bad thing.

If the form record source were a query, the project name could be calculated on-the-fly and need not be stored. Does that mean that you (as the developer) has to do more work? Definitely yes. But it will be a better product.


I do not and have never used lookup FIELDs or calculated FIELDs. But then again, the record sources for my forms are always queries, so I can do calculations in the queries (unlike tables as record sources).


In table Projects, you have the default value of fields HOSTPartnerInstitutionID and REMOTEPartnerInstitutionID set to 0 (zero). This prevents you from setting RI for those fields.
Because the PK fields for tables HOSTPartnerInstitutionID and REMOTEPartnerInstitutionID are autonumber types, they can NEVER be zero. Yet Projects.HOSTPartnerInstitutionID and Projects.REMOTEPartnerInstitutionID will default to zero if nothing is entered into the controls on the form. Not a good design IMHO....


There are a few other things I would fix, but I've carried on too long........


Good luck with your project....