Automatically generate number while entering another table.

[DeepTrouble]

Thanks in Advance for the solution!
The database in question is for a medical laboratory, which has six different sections.The specimen entry is planned through this database. In the Data entry form, a specimen number is generated automatically. However, each section of the lab wants their own serial number.I have tried this as follows, but ofcourse doesn't work! Please note, I am not a Computer graduate, and so have more ignorance than knowledge)
Private Sub Specimen_Number_LostFocus()
Dim db1 As Database
Dim rshepatitis_c As Recordset
Dim inthep_no As Integer
Dim hep_no As Integer
Set db1 = CurrentDb
Set rshepatitis_c = db1.OpenRecordset("HEPATITIS C")
[hep_no] = 1
While Not rshepatitis_c.EOF
rshepatitis_c.Edit
rshepatitis_c![hep no] = rshepatitis_c.RecordCount + 1
rshepatitis_c.Update
rshepatitis_c.MoveNext
Wend
End Sub

Where: HEPATITIS C IS A TABLE
HEP_no IS IS THE FIELD( WHICH NEDDS TO BE UPDATED) IN TABLE
Can I use the same VB for other sections?
Any solutions? Alternates are welcomed as well!

 

[Fizzio]

Is there any reason why you are not using the autonumber facility?

 

[DeepTrouble]

Fizzio
Thanks for your reply.
The "specimen number" is an autonumber field, which indicates any sample in rcd in the lab. the format is e.g.01-000001. Each section needs additonal serial number representing sample number in that section. Hence sample no. 01-000030 could be H-0001 in Hepatitis lab.
Will wait for your suggetion.

 

[Fizzio]

Does each section have its own table? If so you could autonumber the section's spec id and set up a 1:1 link between the specimin table and each section table, linking the specimin ID therefore having eg 2 tables.

Table 1 - Specimens
SpecimenID Autonumber
etc...

Table 2 - HepLab
HepSpecID Autonumber
SpecimenID Long Integer

Then use relationships to set up 1:1 relationship between tables

I have a feeling though that this is not exactly what you wanted :-?

 

[DeepTrouble]

Fizzio
Thanks
You are right!Each section has it's own table and I have 1:1 relationship between two tables on Specimen number, which is copied through referential integrity and cascade update related records.

 

[Fizzio]

Another thought. If you set up an autonumber field in each sections table as the primary key, you will get 0001, 0002 etc (depending on your format setting). Yo can set the format to "H-"00000 for example in the hep lab or "P-"00000 for example in the path lab. It will then record each autonumber as H-00001, H-00002 etc. How are you allocating each spec to the respective labs?

 

[DeepTrouble]

Fizzio
I have tried that earlier. The problem(s) :
1) the specimen no in Specimen table is a text field and
2) H or P number as you suggested are Autonumbers
hence referential/Cascade can not be established and hence the sectional fields are not automatically updated.
This is how I am selecting the specimens for each section:
In specimen table field "Type" select the test to be performed. Query picks up the samples for each section and the tech enters the results. Right now they see the specimen number from Specimen table, but really would like to see one form section table.
Thanks once again

 

[Fizzio]

Are all the specimens logged initially into a central table, or does each specimen go to each section who then log each specimen into their own table. Is there also a reason for using a txt field for the spec number ie is it already set before you receive it? I'm not sure exactly where the details for each specimen go and what the relationship between the tables are. I'm happy to have a look if you zip it to me.