Automatically number subfor records 1

[doctorswamp]

Have database for a plant nursery where they produce batches of different plants.

They want a main form to show the plant details then a subform for different production batches of each plant. The subform would best be in datasheet view.

Is there a way to number the batches sequentially, ie for each plant create a series of batch numbers, eg plantX-001, plant X-002, etc, then plantY-001, plant2-002, etc.

I wondered about using an autonumber field for the subform records then concatenating it with the plant name, but this would give gaps in each plant's sequence of numbers.

Thanks

 

[Remou]

If you do not have a large number of users, something like this may suit (typed, not tested):

[tt]intNextNo=DMax("Right([PlantNo],3)","tblTable","Left([PlantNo],Instr([PlantNo],"-"))='" & Left(Me.[PlantNo],Instr([PlantNo],"-")) & "'")

txtPlantNo = Left(Me.[PlantNo],Instr([PlantNo],"-")) & Format(intNextNo,"000")[/tt]

 

[doctorswamp]

Thanks Remou, you're always great at responding.

As ever I'm a few paces behind in understanding. When you say users do you mean plants, ie create a table of identifiers for each plant and pick the highest identifier and somehow add 1 each time?

 

[Remou]

By 'users', I mean people using the database at the same time.

I think I can tidy the idea up a little. Is there a plant number on the main form, say, Plant001? Do you wish the batch numbers to be based on this plant number? If so, try this.

First, find the next batch number, based on the batch numbers already entered and the PlantNo on the main form (Parent):
[tt]intNextNo=Nz(DMax("Right([BatchNo],3)","tblBatches","Left([BatchNo],Instr([BatchNo],"-")-1)='" & Me.Parent.[PlantNo] & "'"),0)+1

Next, format this number for use:
[tt]txtBatchNo = Me.Parent.[PlantNo] & "-" & Format(intNextNo,"000")[/tt]

This can be used to assign a batch number to new records in a suitable event of the subform. Is that a bit clearer?

 

[Remou]

Oops, mark-up error:

[tt]intNextNo=Nz(DMax("Right([BatchNo],3)","tblBatches","Left([BatchNo],Instr([BatchNo],"-")-1)='" & Me.Parent.[PlantNo] & "'"),0)+1[/tt]

 

[doctorswamp]

Many thanks Remou, and yes, there is a plant number on the main form, as PlantNo. For clarity, you are then calling up a field [BatchNo] in a new table tblBatches.

Getting there I think but have a type mismatch. As I read it the Dmax function looks for a variant value, whereas I'd assumed your intNextNo would be an integer.

 

[Remou]

Oops, DMax is not returning an integer. This is better, but you will still need error checking etc.

Private Sub Form_Current()
If Me.NewRecord Then
   intNextNo = DMax("Right(BatchNo,3)", "tblBatches", "Left([BatchNo],Instr([BatchNo],'-')-1)='" & Me.Parent.[PlantNo] & "'")
   txtBatchNo = Me.Parent.[PlantNo] & "-" & Format(Val(intNextNo) + 1, "000")
   Me.BatchNo = txtBatchNo 
End If
End Sub