Primary key in table (1 Viewer)

Craig6

Registered User.
Local time
Today, 14:11
Joined
Jun 20, 2019
Messages
33
Ok, so this is my first post - hopefully I'm in the right place.
My DB form is for logging support emails. I have a primary key 1,2,3 etc and also a field where I want a reference to the support i.e. 2019-1-R
The 2019 is the date, 1 is the primary key and R is from a combo box on the form. The date is also on the form.
I have read about a few ways to do this and at the moment I have something similar to this in the control source of the text box;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]

This works as it puts what i want in the text box on the form however I can see that it doesn't populate that result in the table.

Am I going about this the right way. I did read about an update query but I could only get this to work when I updated it for all records i.e. it didn't seem to just automatically update after each record was created.

Hope that all makes sense. Thanks in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:11
Joined
Oct 29, 2018
Messages
21,358
Hi. So, your table already has [DateReceived], [SupportID], and [SupportType], right? If so, then you don't need [DateReceived] & "-" & [SupportID] & "-" & [SupportType] in the table too because you can always just use the above expression in your form, report, or query to get the same value and you will never have missing values this way. If you really like to see them in the table, I suppose you could create a Calculated Column instead of a Text column and use the same expression as above. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 28, 2001
Messages
27,003
To add another opinion in the mix, since we very often recommend use of queries rather than direct table usage anyway, and you have those items in a table, leave them separate in the table but together in the query.
 

Craig6

Registered User.
Local time
Today, 14:11
Joined
Jun 20, 2019
Messages
33
TheDBguy, that’s right all those are in the same table. The expression is just on a form which gives me what I want. This leaves the field in the table empty - is that right? I thought the answer to the expression would fill in the field in the same table.
The_Doc_Man, I tried that but couldn’t get the field on my form to update the query.
Maybe I’m overthinking it.


Thanks both
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:11
Joined
Oct 29, 2018
Messages
21,358
TheDBguy, that’s right all those are in the same table. The expression is just on a form which gives me what I want. This leaves the field in the table empty - is that right? I thought the answer to the expression would fill in the field in the same table.
The_Doc_Man, I tried that but couldn’t get the field on my form to update the query.
Maybe I’m overthinking it.

Thanks both
Hi. Typically, it is against Normalization Rules to store calculated values because once the source data changes, your stored calculated value becomes invalid. Hence, calculated values risk your data integrity. The new Calculated Column, I think, was created as a workaround for this rule. It's always been common/best practice to simply do the calculation in a query or wherever/whenever it is needed.

Good luck with your project.
 

Craig6

Registered User.
Local time
Today, 14:11
Joined
Jun 20, 2019
Messages
33
Sorry, I think it's the way I've worded it. I don't want to put a calculation in the table. I just thought that if i put the expression into my form in a new field [ReferenceNo] then the answer would also save into my table. But i can see that it doesn't. I suppose it doesn't matter as long as the result (2019-1-R) appears on my form. I just hope that i can use that data for searches in the future;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:11
Joined
Oct 29, 2018
Messages
21,358
Sorry, I think it's the way I've worded it. I don't want to put a calculation in the table. I just thought that if i put the expression into my form in a new field [ReferenceNo] then the answer would also save into my table. But i can see that it doesn't. I suppose it doesn't matter as long as the result (2019-1-R) appears on my form. I just hope that i can use that data for searches in the future;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]
Yes, you can use it in searches too, if you put the expression in a query instead of the form. Cheers!
 

Craig6

Registered User.
Local time
Today, 14:11
Joined
Jun 20, 2019
Messages
33
Ahh, so I should put that expression into a query instead of the control source of the field on the form? When i did that before i couldn't get it to update the field. Can you point me in the right direction?

The plan is to add a record, choose a support type, pick a date and then my reference will update based on the data previously selected. I only need this as I can't give my colleague an ID no. (primary key) because it won't mean anything to them or me later on down the line.

Sorry if this is doing your heads in. I just want to make sure i start off right.
 

Attachments

  • Annotation 2019-06-21 162435.JPG
    Annotation 2019-06-21 162435.JPG
    16.1 KB · Views: 92

theDBguy

I’m here to help
Staff member
Local time
Today, 07:11
Joined
Oct 29, 2018
Messages
21,358
Ahh, so I should put that expression into a query instead of the control source of the field on the form? When i did that before i couldn't get it to update the field. Can you point me in the right direction?

The plan is to add a record, choose a support type, pick a date and then my reference will update based on the data previously selected. I only need this as I can't give my colleague an ID no. (primary key) because it won't mean anything to them or me later on down the line.

Sorry if this is doing your heads in. I just want to make sure i start off right.
Hi. Whether you put the expression in a query or the form, you shouldn't/wouldn't be able to edit/update "it." So, I am not sure what happened with what you tried before. In any case, this is the correct way to do it. Namely, add the calculated columns in your query, and you can refer to them in your forms and report (and even recordsets). Cheers!
 

plog

Banishment Pending
Local time
Today, 09:11
Joined
May 11, 2011
Messages
11,613
Stop with the coding for a minute and understand the big idea that the posters thus far have been trying to convey:

Your idea of an ID will not be stored in this database

Let that sink in for a minute. You can still accomplish your goal, just not in the manner you have set in your mind.

Your ID is comprised of 3 pieces of data that are stored in your database. You will not create a 4th field in a table to hold the ID you have envisioned, instead you will build a query and that ID field you envision will live there. Your idea of an ID is but a calculation, so when you need it, you will let Access calculate it.

When you need it on a form, you will use the 3 fields it is made of to calculate it and display it to the user. When you do so, that value will not be saved to any table. So, if your form properly displays the ID you envision, you are done, that form does nothing more with your ID than calculate and display it.
 

Craig6

Registered User.
Local time
Today, 14:11
Joined
Jun 20, 2019
Messages
33
Thanks Plog.
I needed it spelling out. I understand now.
Thanks all.
 

Users who are viewing this thread

Top Bottom