I have a two tables called “tblProject” and “tblSample” related by “Project_No” field (one to many) as shown below. Each Project has Many Samples.
tblProject
Project_No Project_Name
P060801 A
P060802 B
tblSample
Sample_ID Project_No Sample_No Sample_Code
1 P060801 01 P060801-01
2 P060801 02 P060801-02
3 P060802 01 P060802-01
4 P060802 02 P060802-02
5 P060802 03 P060802-03
I also have “frmProject” which is the main form and “frmsample” which is the subform.
Each time I add a new sample relating to a particular project and click on the “Save” button on the form “Sample”, I want to generate a sequence of numbers for that particular project. The sample number should restart for a new project selected.
I need help with writing the code for generating sequence of sample numbers for the “Sample_No” field based on the project I select.
As shown in the table above I can concatenate to display that way I want it to later for the “Sample_Code” field. I have about 3 weeks experience with access and know very little coding let alone using conditions. (I think I need to use “Dmax” and maybe some if statements but have no clue as to how)
I would really appreciate it if I could get help with the coding or at least how to go about it.
Thank you very much for you time!