General Question

[gheyman]

Can you take a calculated field, on a form, and store it as a Value in a Table (preferably as a Primary Key)?

 

[Joe4]

What exactly is this calculation?
The issue is that Primary Keys cannot be Null. So if you are trying to do this on an existing record, you cannot have a Null Primary Key field waiting to be populated. So this would have to be done as you are adding a record.

Can you explain the process and what exactly you are trying to do in a bit more detail?

 

[gheyman]

My Primary Key, if its located in the Table or Elsewhere, is going to be the Proposal Number. (The database will track various information on Proposals. Therefor my Proposal Number will be a Key.)
The Proposal Number is something that this Access program will build by the user making some selections.
The Proposal Number is made up of four parts: A sequenced number, A Program ID, A Job Type ID and the current Year and Month. (All Proposal Numbers Start with a “Z”)
I have Three tables

Sequence / Proposal Number
1 Z0001-FA16-RP-1611
2

Program / Program ID
Blackhawk Helicopter UH60

Job Type / Type ID
New Fabrication NF


Example: Z0002-UH60-NF-1612

So I am pulling the DMax sequence Number from my “PID_SEQUENCE” Table. Then Using the Users Selected Program ID, the users Job Type ID and the Date to create the Proposal Number.

I am taking these parts/pieces and Concatenating them (Hence the Calculation). This calculated Field is what I want to take and Store it as a Value someplace.

Hope that’s clear.

 

[gheyman]

This changed formatting on me. The Tables: There is the Labels, two columns one is Program The second is Program ID

The Program is Blackhawk and the Program ID is UH60

 

[Joe4]

So how are new records entered into the data base?
Is it all Form-driven?

It is going to be a bit tricky, as Primary Key fields cannot be duplicated and cannot be blank, but it looks like you are using other fields in building it.
You might have to do something like:
- Have an Unbound Form, where you ask for the fields needed to build the ID Number (the ones that cannot be calculated)
- Press a button that builds the new record based on the input information and allows them to add the other fields related to this new record

 

[gheyman]

Yes, form Driven.

I mastered the Building of the final Proposal Number through the Users Choices which I concatenate the pieces in a calculated Field. My base questions was can I now take that field and store it in a separate table as a value (not a concatenated formula). A store value procedure.

 

[Joe4]

Access is not like Excel. You cannot store formulas like that. If you add the value to a table, it will store the calculated value.
So it really shouldn't be an issue.

How exactly are you trying to add the value to your table? If there is code, can you post it?
Is the form where you are building this an Unbound Form?

 

[gheyman]

I am two days into learning Access and trying to use it to build Proposal Numbers. So when you ask how I am doing something, how I am doing it is really open for change. Most likely I am not doing it the most efficiently way at this point.

 

[Joe4]

Well, I am trying to figure out how much of it you have completed, and how much of it is just theoretical, so I know exactly what you are looking for and I don't bother posting stuff that you already have.

So:
- Have you worked out this calculation yet? If so, please post the code.
- Have you built the Form where the user makes their selections (to be used in building the value you need)?
- Have you worked out how to incorporate the user selections on the Form to build the value you need? If so, please post the code.

 

[gheyman]

- Have you worked out this calculation yet? If so, please post the code. No I have not

- Have you built the Form where the user makes their selections (to be used in building the value you need)? Yes, The Form is Built

- Have you worked out how to incorporate the user selections on the Form to build the value you need? If so, please post the code. Not Yet

On the Form I have a Combo Box that uses a Table Called "Program_List". It has Three Columns: "PROGRAM NAME" "PROGRAM ID" & "DESCRIPTION"
I have the Bound Column set to 1 (PROGRAM NAME).

Next to the Combo box I have a Textbox (Text22) where I am trying to do a Dlookup to pull the associated "PROGRAM ID" based on the selection of in the Combo.

That's were I currently am.
Here is the formula that's not working: =DLookUp("[PROGRAM ID]","[PROGRAM_LIST]","[PROGRAM NAME]=" & [Combo18])