Solved Assigning records based on field values (1 Viewer)

[dawsonrhodes]

Hey All,

This may be very easy, or very difficult, I'm not entirely sure.

Basically, there is a user form that is filled out as a regulatory obligation for every transaction completed. These records require some personal information, and based on this information collected (first, last, date of birth, address), it can later be found in another form, where it gets treated as a "profile" for each person.

Currently, the form is build as a subform in a parent form (the "profile"), which means that in order to make a record, the user first has to search for the "profile" (with information such as name, dob, etc.) to then add the form.

What I'd like to do is automate this, as there are some other reasons I need this that I won't include, as they aren't relevant. The user form would be something like what's listed below

First name	Last Name	DOB
Address	ID Number (D2933-23842-38746	ID Type (Drivers License)
Transaction Amount ($00.00)	Exchange Rate ($00.00)	Method Funds Issued (in person, cheque)

What I would like to do is automate the entries, so that if txtFirstName, txtLastName, txtDateofBirth match, it will automatically record the other fields into a new record for that "person"

I'm not sure if this is clear, please let me know if I need to clarify anywhere.

Thanks in advance!

 

[plog]

it will automatically record the other fields into a new record for that "person"

That's not typically how databases are to work--data shouldn't be copied around from table to table. Instead you use a foreign key:

SQL - Foreign Key

SQL - Foreign Key - In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table, allowing the two tables to be connected together.

www.tutorialspoint.com

You would have your Profile table with all the info you listed (name, address, dob, etc) and it will have a primary key--usually an autonumber, let's call it [ProfileID]. Then, when you need to have that data related to another table you simply store the [ProfileID] value in that table and not every piece of data from Profile. Then when you need data from both tables you created a query where you JOIN those 2 tables by their [ProfileID] fields.

 

[Isaac]

I agree with plog. Stop everything you're building/making until you study/understand how Tables and relational data are supposed to work - structuring that correctly is the foundation upon which everything else works thereafter, or, the basis upon which many things thereafter fail.

 

[jdraw]

I agree with plog and Isaac that your approach is not typical of relational database.
I recommend you review the concept of normalization.