Auto Fill Last Name and First Name

[2Shoes]

I'm working on a project for a Veteran Relief Fund Program, and it has been a decade or so since I last really did any database creation or management. We had something that half worked but required manual entry of first and last name, after selecting the client name in one form, and recording a voucher in another form, saving to a table.

The process goes as this, select the client's last name from the drop down just below the Veteran ID at the top, then the form frm_vouchersubform is in the grey box, and I would like to have the Last Name and First Name auto fill, so when we fill in the Voucher Date, Number, Amount, and Type, it saves that data into the 2026 Voucher Log. I have the Date, Number and below all working correctly, but I cannot remember or figure out on my own, how to auto fill the last name and first, after selecting the name at the top? All client data is in the Veteran Data Table. I have it set up to reset and clear the form once the save button is pressed, and then it displays the previous Vouchers in the spreadsheet on the right side of the screen.

I am certain this is simple for most, I just haven't done much in years and I am getting old and rusty.

 

[tvanstiphout]

Before we get to your question, we need to understand your database design. Can you please post a screenshot of the Relationships diagram, with the tables laid out neatly for our review and feedback?

BTW, the expression to pick up LN and FN in the *subform* needs to refer to the parent form. But we will get to that a bit later.

 

[theDBguy]

Hi. Welcome to AWF!

 

[plog]

Before we get to your question, we need to understand your database design....

Tables:
2024 Voucher Log
2025 Voucher Log
2026 Voucher Log

Just with the table names I can tell you haven't set up your tables properly. You will need to work on your table structure before you move onto forms. Post the screenshot tvanstiphout requested so we can help you with that.

 

[Ken Sheridan]

I would like to have the Last Name and First Name auto fill, so when we fill in the Voucher Date, Number, Amount, and Type, it saves that data into the 2026 Voucher Log.

You should not repeat the names in the Voucher Log table, as this introduces redundancy and the risk of update anomalies. All you need in the Voucher Log table is the VeteranID foreign key column. In the subform you can then bind a combo box to that column, setting it up as follows:

ControlSource: VeteranID

RowSource: SELECT VeteranID, FirstName & " " & LastName FROM Veterans ORDER BY LastName, FirstName;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches. The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource: SELECT VeteranID, LastName & ", " & FirstName FROM Veterans ORDER BY LastName, FirstName;

Another issue with your database is that by having separate Voucher Log tables for each year you are encoding data as table names. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. You should have a single VoucherLog table. You have a VoucherDate column in your tables already, so the VoucherYear can probably be derived from this with the expression YEAR([VoucherDate]) in a computed column in a query, or a computed control in a form or report. If the VoucherDate does not invariably provide the correct year, you should include a separate VoucherYear column in the table. You can then return the rows for specific years or groups of years if required by means of a query. The query can join the Veterans and VoucherLog tables if you need to return data from both tables, e.g. in a report.

 

[2Shoes]

Tables:
2024 Voucher Log
2025 Voucher Log
2026 Voucher Log

Just with the table names I can tell you haven't set up your tables properly. You will need to work on your table structure before you move onto forms. Post the screenshot tvanstiphout requested so we can help you with that.

Part of the issue is that I inherited this database, already used since 2024, as a record of this program, and so I am trying to improve it and remember the two years of computer science courses I took... 20+ years ago.

I apologize for not spending many hours ready the forum, I genuinely just want to improve this existing database or maybe even create a new one to replace it, one that's simple for someone with no access experience to input the Voucher info when our clients come into the office. This is not something I put together from scratch.

 

[tvanstiphout]

> I genuinely just want to improve this existing database or maybe even create a new one to replace it

We are here to help you with that.
In your case I would create a new database with correct database design.

Correct db design is super important for the longevity of the app. What if you win the lottery, and today is 1/1/2027? Who is going to create a new 2027 Voucher Log and fix up the queries and forms? Let's make it so that none of that needs to happen.

In the new database we will use a combination of correct relational database design and best practices.
Veteran Data Table becomes tblVeterans. VeteranID is the first field, and it is the primary key, autonumber.
Re-create the other fields, but omit <space>, <questionmark> and other funny characters. Remove or rename Field1.

The several Voucher Log tables become one table tblVouchers. VoucherID primary key autonumber is the first field, VeteranID long integer default null required is the second field. No veteran name fields whatsoever: later they can be looked up in tblVeterans. Make sure that all fields are required unless clearly not so (e.g. Notes may not be, but Voucher* are all required). Create the enforced relationship between the two tables.

Link the tables from the old db to the new db and run Append queries to copy the data over.


Now you have a solid database design that you can build your app on top of. If you want to run year-specific queries or reports, you can use a parameter (select * from sometable where Year(datefield) = [What is the year?]), or you can have a form for the user to select the year to work with.

 

[Mark_]

Vouchers should be a single table, not one per year. You would store the date in your table.
I would only save the first and last name in this table if you have a regulatory reason to. If not, pull from the Veteran Data Table. If you DO have a regulatory reason, make sure you've got the reference documented.

Are the vouchers based on appropriated funds? If so, please check what requirements the appropriation has for disbursement. You may need to save extra information in case of audits.

If you need to keep digital copies of receipts and the such, I would suggest keeping the images in a separate folder and have a text field that stores the path to the files. This may sound a bit like overkill, but I've had to spend a lot of time resolving issues with appropriated funds that were not properly documented. Really unfun.

 

[tvanstiphout]

> I would only save the first and last name in this table if you have a regulatory reason to.
Mark has a good point. I wrote from a technical database design point of view, but you are the SME = Subject Matter Expert so you need to make sure that important business rules are documented and make it into the database design.
One of those may be: "people can legally change their name, and for each voucher we need to record the name as it was on that date".
That would translate to FirstName and LastName fields in tblVouchers, and their values get copied from tblVeterans upon creation of a new voucher.

 

[2Shoes]

Vouchers should be a single table, not one per year. You would store the date in your table.
I would only save the first and last name in this table if you have a regulatory reason to. If not, pull from the Veteran Data Table. If you DO have a regulatory reason, make sure you've got the reference documented.

Are the vouchers based on appropriated funds? If so, please check what requirements the appropriation has for disbursement. You may need to save extra information in case of audits.

If you need to keep digital copies of receipts and the such, I would suggest keeping the images in a separate folder and have a text field that stores the path to the files. This may sound a bit like overkill, but I've had to spend a lot of time resolving issues with appropriated funds that were not properly documented. Really unfun.

Luckily the Vouchers are just tracked by us to ensure we know how often a veteran comes in, and making sure we don't go over the total allotted amount per calendar year, there is tracking of the receipts and vouchers the Accountant handles so that's not my responsibility. I wouldn't mind creating something where that is feasible to do, as we do get the receipts back.

> I genuinely just want to improve this existing database or maybe even create a new one to replace it

We are here to help you with that.
In your case I would create a new database with correct database design.

Correct db design is super important for the longevity of the app. What if you win the lottery, and today is 1/1/2027? Who is going to create a new 2027 Voucher Log and fix up the queries and forms? Let's make it so that none of that needs to happen.

In the new database we will use a combination of correct relational database design and best practices.
Veteran Data Table becomes tblVeterans. VeteranID is the first field, and it is the primary key, autonumber.
Re-create the other fields, but omit <space>, <questionmark> and other funny characters. Remove or rename Field1.

The several Voucher Log tables become one table tblVouchers. VoucherID primary key autonumber is the first field, VeteranID long integer default null required is the second field. No veteran name fields whatsoever: later they can be looked up in tblVeterans. Make sure that all fields are required unless clearly not so (e.g. Notes may not be, but Voucher* are all required). Create the enforced relationship between the two tables.

Link the tables from the old db to the new db and run Append queries to copy the data over.


Now you have a solid database design that you can build your app on top of. If you want to run year-specific queries or reports, you can use a parameter (select * from sometable where Year(datefield) = [What is the year?]), or you can have a form for the user to select the year to work with.

You are absolutely correct, what I want to accomplish here is to relearn how to create a new database that will outlast me if I am not here. I guess I should start off with a new database, and built it out correctly while incorporating some of the necessary queries that management may want to see once in a long while.