Auto-filling a field based on other fields?

[TerraEarth]

I have a table that I want to generate as follows:

x1 x2 x3
a b ab
a b ab

There is a subform on one of my forms that was generated form a table (x1 | x2 | x3) where x1 is a foreign key that links to the main form. It is in a table format because it is in a one-to-many relationship with the mainform it is hosted on (i.e. a customer mainform and a 'orders' subform in table format).

When the user inputs in data into the mainform, the subform automatically fills in the x1 field. However, I want to set it up so that when the user inputs something into the x2 field, it gets concatenated with x1 into x3, and I would like this to be done for every character entered.

i.e. if I was typing dad into x2, it would go as follows:

x1:a; x2: => x3:a
x1:a; x2:d => x3:ad


x1:a; x2:da => x3:dad
x1:a; x2:dad => x3:dad

Could anyone tell me what the best way to do this would be?

[pbaldy]

I'd look at using a calculated field.

[Gicu]

You could use the OnChange event of x2 to concatenate the values like Me.[x3]=me.[x1] & me.[x2].text or even better do it just once in the AfterUpdate event of the same textbox. The question is why would you want to save it in the table when it is always available to you as a calculated field. You are opening yourself to the possibility of data integrity issues if you ever change x1 or x2 somewhere else.

Cheers,
Vlad

[TerraEarth]

This is a table that keeps track of years associated with a contract.
I wanted a way to make each entry unique, via primary key. I tried an autonumber at first but that didn't stop duplicate entries from being entered.

For example:

1 | contract12 | 2017
2 | contract12 | 2017
3 | contract12 | 2018

However, with a concatenated primary key I was able to avoid that issue:

contract122017 | contract12 | 2017
contract122017 | contract12 | 2017 (ERROR)
contract122017 | contract12 | 2018

I felt that this was necessary because of how messy my import data was, it was very important for me to not have duplicates, but this design choice is causing me some headaches at this point in time.

I tried using a calculated field as suggested in this thread but it turns out access does not allow you to set a calculated field as the primary key, even if all the constituent fields are set to required.
Also, I am not sure how to set a onchange/afterupdate event for a table. The subform looks exactly like a table format, and so when I try to access its fields in design mode I just get the following:



Please advise.

[pbaldy]

You can use the autonumber field and use an index based on both fields, or simply use the 2 fields together as the key.

[TerraEarth]

What would the ramifications of this be when it comes to linking fields across tables? Because the contract number is linked to other tables, but if the contract number is part of a dual primary key then...
My SQL update queries are also based on this concatenated primary key.

[ssanfu]

Then do as Paul suggests and set a unique 2 field index.

See Microsoft Access Tables: Primary Key Tips and Techniques

[TerraEarth]

Thanks, I'm working on it now.

Quick question then, how would I set up SQL queries without use of a predefined composite key? My import data lacks a primary key that is synced with the Main Table's, meaning I would have to update it based on the pre-existing fields already in the data.

For example:

tblAccessTable: (This is what the main table in access looks like)
PK| Composite | ContractNum | Years
1 | contract122017 | contract12 | 2017
2 | contract122017 | contract12 | 2018

tblImportIntoAccess: (I need to import this into the main table)
Composite | ContractNum | Years
contract122017 | contract12 | 2017
contract122018 | contract12 | 2018
contract132014 | contract13 | 2014

My SQL query would look like this:

UPDATE  tblAccessTable As Main
  RIGHT JOIN tblImportIntoAccess As Import ON Main.Composite = Import.Composite
SET
  Main.Composite = Import.Composite,
  Main.ContractNum = Import.ContractNum,
  Main.Years = Import.Years;

Without the Composite field, how would I update the main table with the import table? In the article you linked it suggested that there could be a 'unique' index that one could use to ensure uniqueness, then should i keep the composite field around and use it as a index instead so that in the main table, the primary key is what is used for access records, however the 'unique' index i.e. composite should be used for updating purposes? How do people usually deal with this?

[ssanfu]

I've read and reread this thread and am still confused.


Post #1

i.e. if I was typing dad into x2, it would go as follows:

x1:a; x2: => x3:a
x1:a; x2:d => x3:ad
x1:a; x2:da => x3:dad
x1:a; x2:dad => x3:dad

The progression is wrong. If you want to concatenate X1 and X2, the progression would be

x1:a; x2: => x3:a
x1:a; x2:d => x3:ad
x1:a; x2:da => x3:ada
x1:a; x2:dad => x3:adad



Post #8
You have been asking about concatenating values in previous posts of this thread, but now you bring up a import table.
I think that "Composite" in this record is a typo

2 | contract122017 | contract12 | 2018

I am guessing that the field "Composite" is "X3", the field "ContractNum" is "X1" and the field "Years" is "X2".

In the table "tblImportIntoAccess", data for the record

contract132014 | contract13 | 2014

would not be added to the table "tblAccessTable" (main table) because the query you posted is an UPDATE query, not an APPEND query.
For a record to be UPDATED, the data must first exist in the table.


In Post #6, you said:

Because the contract number is linked to other tables

Do the other tables' records relate to just the contract number or the contract number AND the year?


Again, without knowing the table structures and the relationships, I might split the table "tblAccessTable" into two tables:
tblContractNumbers
-------------------
ContractNumID_PK - Autonumber
ContractNum - Text

tblContractYears
--------------------
ContractYearID_PK - Autonumber
ContractNumID_FK - Number- Long (Link to tblContractNumbers)
ContractYear - Text



Without knowing the table structures and the relationships, it is hard to make good recommendations.

[TerraEarth]

Hi Steve, thanks for the response.

All your conjectures are correct. x3 would be the concatenated field and it would essentially work as [x1] & [x2], I did have some typos there it seems as you have pointed out.

In the table "tblImportIntoAccess", data for the record ... would not be added to the table "tblAccessTable" (main table) because the query you posted is an UPDATE query, not an APPEND query.
For a record to be UPDATED, the data must first exist in the table.

Yes, however the form that I listed up above, i.e.:

tblImportIntoAccess: (I need to import this into the main table)
Composite | ContractNum | Years
contract122017 | contract12 | 2017
contract122018 | contract12 | 2018
contract132014 | contract13 | 2014

Would either be an actual table that I import into the database (from a .csv or .xlsx, for example), or it could be records stored in a table that is linked to a form, i.e. form entries that the user would like to incorporate into the main database tables.

I don't allow entries to flow directly into the main tables from the forms, i have it placed into a secondary table where it is entered/validified => updated into main tables if it's good => deleted/cleared from the secondary table.

To summarize, I seem to have gleaned the understanding the majority of people think I should use an autokey instead of a composite of two fields for the primary key, so I went back and changed all the relevant tables to match this transition, after a bit of tweaking I was able to set it up how I like it.

My next question about the best way to update when I am using an autokey was due to me being a novice in this field. Essentially I have a table in the database with autokeys already assigned, and a table not in the database without autokeys. I was wondering how I could update the database with the import without autokeys when - there are no autokeys! The solution I came up with was to use a composite field as a index (it has to be unique however and generated only from the existing fields) to match against the records in the database, since I can't compare with the autokey. I was just wondering if this is the best way, the most smiled-upon method in order to do this.

For some reason for the longest time I always thought that when people mentioned 'index' they were referring to 'primary key' but that seems to not be the case, i realized this upon reading the article linked by ssanfu.