Store value from form in table

[flavioso]

I created a form which allows a user to select the Year and the Quote Type, and then created an unbound text box(Text5 below) which concatenates in the control source the Quote type, year, and record ID. This field becomes a quotation number. What I would like to know is if there is anyway to store this new "quote number" to a specific table column. If so, this would become the primary key for a subtable which would have quote details, such as parts and price quotes.

Can anyone offer suggestions how to make this value in an unbound table stored into a bound field in a table?

Thanks in advance,

Brad

 

[dkinley]

Create a new field for the table the form is bound to. Set the Text5 control to the control source of the new field.

Not sure how you are calculating Text5 (from a button or on the after change events of the other controls) but let's suppose it's on a button.

Immediately after clicking the button and it does the concantenate sequence, add another line of code: DoCmd.RunCommand acCmdSaveRecord
and this will save it in the field.

-dK

 

[KenHigg]

My suggestion is to not store it but rather have the three fields make up a composite primary key. Then when you need to print the derived code, concatenate it on the fly.

 

[dkinley]

Ah yeah .. I was going to mention that - got sidetracked on a video

I gave you information on storing your number.

I would use the Access generated primary key to provide linking to other tables in the way of foreign keys. Furthermore, for those other tables, I would let them have their own primary key - not use this generated number as the primary key.

I say this because unless you only allow this number to be generated one-time and one-time only, it could be changed at any moment if a user changes one of the fields that makes up this number. The result would be to break the links in the other tables.

-dK

 

[flavioso]

The goal of the database is to track quotes for specific OEM customers. Each year the OEM will have a new quote, and each quote may have multiple products associated with it.

Per my manager, the quote number needs to be "quote type" - "year" & "increment (record id)"

Since modifying the Autonumber is difficult and not recommended, I figured I would create a form which concatenates these fields, and then I could create a form that is tied to the new field.

Does anyone think that I could accomplish this in a different direction?

EXAMPLE:

IND-2008115

Sub table:

Part: 568882
discount: 55%

Part: 568883
discount 65%

subtable linked to QUOTE Number

 

[KenHigg]

Why do you want to store it?

 

[flavioso]

If I use a composite key, how do I create a relationship between the 2 tables?

 

[KenHigg]

So just continue to use the Id field as the primary key but when you need the the 'Quote Number' simply combine the three fields where ever you need to.