Insert recs based on a given number:

[Oyster]

Here's what I'm trying to do:

Form A is used for data entry into SampleTable. On this form I have a some fields including these two: ReplicateA, LabBatchCode.

The database contains another table, IndividualSampleTable. This table contains two fields: ReplicateB, LabBatchCodeReplicateB.

When the user enters information into Form A, the data in fields Sample.ReplicateA and Sample.LabBatchCode are used to populate IndividuallSampleTable. But here's the twist: On IndividualSample Table, IndividualSample.ReplicateB needs to be autogenerated (I guess). Then the field IndividualSample.LabBatchCodeReplicateB is comprised of LabBatchCode entered in Form A, concatenated to the IndividualSample.ReplicateB field.

To make it tricker, the number of records added to IndividualSample table is determined by the value supplied on Form A in the field ReplicateA.

So, Form A could look like this:
ReplicateA: 3
LabBatchCode: CRBR05151998

IndividualSample table should end up with three records like this:
ReplicateB: 1
LabBatchCode: CRBR051519981

ReplicateB: 2
LabBatchCode: CRBR051519982

ReplicateB: 3
LabBatchCode: CRBR051519983

Hope this makes sense! And I appreciate the help!!!!

 

[WayneRyan]

Oyster:
This code will do it:

Dim i As Long
For i = 1 To Me.ReplicateA
   DoCmd.RunSQL("Insert Into IndividualSample (LabBatchCode) " & _
                "Values('" & Me.LabBatchCode & CStr(i) & "');"
   Next i
Me.YourSubform.Requery

BUT, you really shouldn't munch all of those values into one field.

Isn't CRBR051519983
Really CRBR - SomeDate - SomeSequence

If you put them all together, you will always be tearing them apart.

Wayne