Hi All,
I have a system i am working on which will hopefully let me automatically generate subsamples of parent samples based on if they are selected, through the sampleaction query, which basically filters only the selected samples for processing.
The sample table contains a whole lot of different fields, but basically the main ones are SampleID (autoID), ClientSampleID(Text) and ParentSampleID. For a normal sample the user would never see the ParentSampleID field, however if we recieve a sample from the user which we need to use to generate other samples, i generate subsamples, which are new samples to the system, but the ParentSampleID of the subsamples references back to the main sample recieved by the client.
Anyways i have a form which shows a datasheet with the checked samples so the user can see which samples will have action undertaken, and on the same form there are a series of checkboxes, which let the user make a subsample of that type for each selected checkbox, and a text box beside that which lets the user enter the number of subsamples of that type of sample to be generated, and a short description which is applied to all samples.
I thought the best way to approach this was multiple loops, so a loop through the recordset of the sampleaction, and then for each record in sampleaction, to do a series of if loops, so if the checkbox for Carbon is checked, then look at how many samples for carbon are required, and then make a loop to insert however many carbon tubes are required inserting the projectID of the parent sample, the parentsample's SampleID in the child samples ParentSampleID field, and a description consisting of along the lines of "Tube Type & Number (of loop, eg Carbon Tube 7, Carbon Tube 6, Carbon Tube 8) and the description.
For some reason it only partly inserts data, for the first sample on the first loop it inserts the parentsampleID, and the projectID but that is it, and on other samples it completly ignores it.
Can anyone take a peak and give me any comments or suggestions?
I have a system i am working on which will hopefully let me automatically generate subsamples of parent samples based on if they are selected, through the sampleaction query, which basically filters only the selected samples for processing.
The sample table contains a whole lot of different fields, but basically the main ones are SampleID (autoID), ClientSampleID(Text) and ParentSampleID. For a normal sample the user would never see the ParentSampleID field, however if we recieve a sample from the user which we need to use to generate other samples, i generate subsamples, which are new samples to the system, but the ParentSampleID of the subsamples references back to the main sample recieved by the client.
Anyways i have a form which shows a datasheet with the checked samples so the user can see which samples will have action undertaken, and on the same form there are a series of checkboxes, which let the user make a subsample of that type for each selected checkbox, and a text box beside that which lets the user enter the number of subsamples of that type of sample to be generated, and a short description which is applied to all samples.
I thought the best way to approach this was multiple loops, so a loop through the recordset of the sampleaction, and then for each record in sampleaction, to do a series of if loops, so if the checkbox for Carbon is checked, then look at how many samples for carbon are required, and then make a loop to insert however many carbon tubes are required inserting the projectID of the parent sample, the parentsample's SampleID in the child samples ParentSampleID field, and a description consisting of along the lines of "Tube Type & Number (of loop, eg Carbon Tube 7, Carbon Tube 6, Carbon Tube 8) and the description.
For some reason it only partly inserts data, for the first sample on the first loop it inserts the parentsampleID, and the projectID but that is it, and on other samples it completly ignores it.
Can anyone take a peak and give me any comments or suggestions?
Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim idsample As Integer
Dim carbon As Integer
Dim xad As Integer
Dim silica As Integer
Dim other As Integer
Dim sql As String
Dim clientsampleid As String
Dim proj As Integer
Dim desctxt As String
sql = "INSERT INTO SampleRegister (ClientSampleID,ParentSampleID,Project) VALUES " & "(" & "'" & clientsampleid & "'" & "," & idsample & "," & proj & ");"
Set db = CurrentDb
Set rs = db.OpenRecordset("SampleAction")
Do While Not rs.EOF
idsample = rs!sampleID
proj = rs!OrderID
If IsNull([carbon_chk]) = False Then
For carbon = 1 To [ct_txt]
clientsampleid = "Carbon Tube " & carbon & ", " & "," & Val(Me.desc_txt)
DoCmd.RunSQL (sql)
Next carbon
Else
End If
If IsNull([xad_chk]) = False Then
For xad = 1 To [xad_txt]
clientsampleid = "XAD-2 Tube " & xad & ", " & "," & Val(Me.desc_txt)
DoCmd.RunSQL (sql)
Next xad
Else
End If
If IsNull([silica_chk]) = False Then
For silica = 1 To [Silica_txt]
clientsampleid = "Silica Tube " & silica & ", " & "," & Val(Me.desc_txt)
DoCmd.RunSQL (sql)
Next silica
Else
End If
If IsNull([other_chk]) = False Then
For other = 1 To [other_txt]
clientsampleid = "'" & [othername_txt] & "'" & " , " & other & ", " & "," & Val(Me.desc_txt)
DoCmd.RunSQL (sql)
Next other
Else
End If
rs.MoveNext
Loop
End Sub
