Pasquale has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
User generated primary keys
Hello,
I have a database with several tables including contactinfo, cases, risk and others. Each family has several fields entered in each table and they are all have relationships. The primary key generated for the contactinfo table is a combination of the following:
the first three letters of a family name
the first letter and number of the postcode
the last three letters/numbers of the postcode
e.g. Jones living in SW1 ER6 would have a primary key JONSWER6
That would also be the primary key for the risk and cases table.
How can I develop a macro/vba so that the person entering the address and fmaily name information doesn't have to do it manually, and for the primary key to populate other tables like cases, risk and other tables.
Thanks
RE: User generated primary keys
Hi Pasquale, thanks for your query.
Your query raises several issues. First of all, it is bad practice to create a primary key in the manner you're suggesting. Ideally, you should create a composite primary key by indicating the fields on the table which, when combined, create unique identifiers for each record. What you're doing is creating what is technically know as "derivative data" which can reflect upstream errors and therefore affect the functioning of the database.
If you attempted to create a primary key in the manner you suggest, you would need to parse the data (chop up the postcode by looping through it and cutting it when it sees a space), then concatenating it with only the first three letters of the family name (again by looping through each value) and bolting all that together with the remains of the postcode. While this is possible on a datasheet, it would make more sense to collect the information via a VBA user form, combine it and then populate the relevant fields on the series of tables in your database. As you can probably understand, a close description of how to do this is beyond the scope of this forum, so if you would like to pursue this with a trainer please do contact us via the telephone number of the website. We'd be only too happy to help you build the tool you need.
All the best,
Anthony
