Adding a record to a Table from a field list?

[spectrolab]

Hi,

I have a table that has records added to it using the following VBa code:

Const MyTable As String = "tblSampleSubmission"
Const MyField As String = "SampleName"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Dim LastDub As Double
Dim addString As String
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
Randomize
'LastDub = Me.txtStartValue - Was only used to start the random function later in series
addString = ""
    For intCounter = Me.TxtStartValue To Me.txtEndValue
        rs.AddNew
        rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & addString
        rs.Fields("SubmissionNumber") = Me.SubNum
        rs.Fields("CustomerID") = Me.CustomerID
        rs.Fields("SamplePrep") = Me.SamplePrep
        rs.Fields("Fusion") = Me.Fusion
        rs.Fields("XRF") = Me.XRF
        rs.Fields("LOI") = Me.LOI
        rs.Fields("Sizing") = Me.Sizing
        rs.Fields("Moisture") = Me.Moisture
        rs.Update
       addString = ""
      If Rnd < 0.02 Then
           'LastDub = intCounter
         intCounter = intCounter - 1
       addString = " DUP"
    End If
    Next intCounter
    rs.Close
   db.Close
Set rs = Nothing
Set db = Nothing

DoCmd.SetWarnings False
    Dim stDocName As String

    stDocName = "mroLOIAppend"
    DoCmd.RunMacro stDocName

Exit_EnterBlast_Click:
    Exit Sub

Err_EnterBlast_Click:
    MsgBox Err.Description
    Resume Exit_EnterBlast_Click

  


End Sub

What I would hope to be able to do is add a "standard" randomly to each SubmssionNumber (each SubmissionNumber might be 1-100 records). The record I need to add should be chosen at random from a list of 6 or so options and added at the end or middle or start of the job (SubmissionNumber) is this something that is easy to do or should I just give up and add it manually?

Thanks to everyone who has helped me in the past, it is getting me up to speed quickly. Access seems to be quite popular as I have contacted 3 developers to help with my dB but they are all to busy to help me so I am going it alone.

 

[The_Doc_Man]

If this is something like a reference standard such as I used to use in wet chemical analysis, you don't just add it. It should represent something you actually did, in which case it would have a "natural" place in your sequence and would be added just like any other sample. If you didn't take a sample of a standard, it doesn't belong in your data set.

If I inferred something that isn't correct, please forgive me for the misunderstanding.

 

[spectrolab]

Thanks for the reply, Doc Man.

What The purpose of the table is to log samples into the laboratory as they are received and print out a job sheet and labels etc for the job (submissionNumber). The 'Standard' I am referring to in this case is one of a number of certified reference materials that have to be measured as part of our QC. To try and take away the human element, I want access to randomly select one of the CRM's and put it in the job so that when the analysis of the job is carried out, the CRM is analysed as well. The position in the job table doesn't matter, as long as it is analysed along with the unknown samples. It isn't a blind check, but a check of our analytical techniques and equipment calibration.

 

[JoeCruse]

I would look at something like a command button on your sample log form that could be clicked on to automatically make an entry on your sample log form. The button would be set up to select from a list of RM's. I don't know enough about your process to tell you exactly how to go about it though. From your company's website, I see that you do analysis in support of mining projects. Your choice of RM's to randomly pull from will have to take into account the material type of samples being logged in.

I keep all of our RM's (which are also used as calibration standards) in our LIMS, in 2 related tables: Table 1 has ID and other info, Table 2 has analytes and concentrations.