Combine two Form textbox fields prior to writing the record in Access 1

[Mikke]

As a user enters a persons frmCust.FirstName and frmCust.LastName I want to combine them together so that when the record is written to the database a third field is created for the record. The third field is called tblCust.CusKey

The result I want should be:

tblCust.CusKey tblCust.LastName tblCust.FirstName
JonesBob Jones Bob

 

[PHV]

Something like this ?
Trim(LastName & FirstName)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mikke]

Yes, that's on the right track, but I need to enter that result into the data field tblCust.CusKey when the record is written (inserted into the database).

 

[RoyVidar]

Why?

1 - storing calculated fields, or fields that can be derived from other fields, break the rules of normalization - you should rather calculate them as PHV demonstrates whenever there's a need to display them/it
2 - if you wan't a primary key to be the combination of two fields, just assign them both as primary key (compound/composite primary key) by selecting both, and hit the primary key button when in table design view.
3 - if you need an index on two fields, use the index button and, create an index name, select the two fields, select relevant properties (unique...)

BTW - first name and last name is not a very good choice of primary key, cause even in rather small databases, there's the possibilities of duplicates...

Roy-Vidar

 

[Mikke]

Thanks RoyVider,

I know the problem my illustration creates, I was trying to simplify my problem so that I could explain it easier.

One approach that I was trying is using the Insert Before in a text box that calculates the field, however, I never see it calculated or stored.
I am simply lost. I know this is got to be easy, but I simply am hitting a mental block.

Any ideas?

 

[PHV]

Provided you have a control bound to the CusKey field, you may put this code in the BeforeUpdate event procedure of the form:
[CusKey control name] = Trim([LastName control name] & [FirstName control name])

But, again, it's often a bad idea to store derived/calculated values in tables...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mikke]

Thanks PHV,

Will this work also on a record that is being inserted for the first time?

 

[PHV]

Try it, and if not put the same code in the BeforeInsert event procedure of the form.
Let us know.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mikke]

Thanks I will try it this afternoon. Thanks for the help.