Auto-fill one table using records from another

[camerontaylor]

I am extremely new to Access (started 2 days ago) so please excuse my ignorance for seemingly obvious solutions. I have a table with 3 fields, which I can then run a query on to combine the fields into one text field in another table (custom project numbers).

1. Is there a way to have Access automatically run the query when a new record is created in the first table?
2. Is there a way to have Access automatically take the produced text field, and autofill a read-only textbox on a form?

I am essentially trying to create a form which can create, edit, and delete records from one Project List table, and I want to display the project number at the top of the form for when someone is creating a new project, editing the project, or deleting the project, without allowing them to edit the number.

Once again, sorry if there is an obvious solution, I'm super new to all this.

 

[theDBguy]

Hi. Welcome to AWF!

One of the "rules" in creating a relational database is to not store duplicate or redundant information in multiple tables. As long as you can link the tables, you can just use a query to display all the data you want in one form or report. That way, there's no need to update any table based on related data.

 

[camerontaylor]

Hi. Welcome to AWF!

One of the "rules" in creating a relational database is to not store duplicate or redundant information in multiple tables. As long as you can link the tables, you can just use a query to display all the data you want in one form or report. That way, there's no need to update any table based on related data.

Thanks for getting back to me so quickly. I'm wondering if there is a simple way to auto-increment the number at the end of my project numbers (i.e. I have "PO" & "yymm-" & "00" currently), but have the last two digits reset at the beginning of each month? I have done lots of research, but I am unsure of how to implement the code that I have found before.

 

[theDBguy]

Thanks for getting back to me so quickly. I'm wondering if there is a simple way to auto-increment the number at the end of my project numbers (i.e. I have "PO" & "yymm-" & "00" currently), but have the last two digits reset at the beginning of each month? I have done lots of research, but I am unsure of how to implement the code that I have found before.

Yes, that's very possible. Have you tried searching for "custom autonumber?"

 

[camerontaylor]

Yes, that's very possible. Have you tried searching for "custom autonumber?"

I have tried that, but I am still very confused. Any extra advice, however simple, would be appreciated

 

[Pat Hartman]

Here is a sample database with two examples. One is a compound field with an incrementing part which sounds like what you want), the other is a simple sequence number that can be renumbered. At your level, they are probably way over your head but give it a go. Open the form in design view and search for the control name where the generated number gets populated.