I’m creating a database and a data entry form in MS Access. The custom ID number I have on the form will auto-populate based on certain user entries, as the sample below:
User enters “date of report” (for example, “09/18/2024”)
User enters “issue type” (for example, “Carbon”)
Auto-populated custom ID is set to generate the following customized ID number:
“Carbon-24-001”
I first created the table for the database with all the fields, including a short text field for the custom ID, then made the form, and within the form, I changed the “control source” of this particular field to produce the ID shown above. The formula I used is:
[IssueTypeTable].[Column](1)&"-"&"Right[Date_of_Report],2)&"-"&Format([ID],"000")
When I test out the form, after the “date of report” and “issue type” fields get filled in, the custom ID auto-populates and shows the right thing on the form. When I save, the entry gets saved to the database. The issue is, but when I open the database table, the custom ID field remains empty.
I instead tried making the custom ID field in the table a “calculated” one and tried using the same formula for source but that wouldn’t work. What could I be doing wrong? Really appreciate any help or insight!
1