Selecting Records from a table

spectrolab

Registered User.
Local time
Today, 07:01
Joined
Feb 9, 2005
Messages
119
Hi All,

This is my code used to populate a table:

Code:
Private Sub LogSam_Click()

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 minID, maxID, stdID As Long 'Used for randomly selected ID from table of standards
Dim stdName As String
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)

'Find maximum and minimum ID number in tblStandards
maxID = DMax("StandardID", "tblStandards")
minID = DMin("StandardID", "tblStandards")

Randomize
    For intCounter = Me.TxtStartValue To Me.txtEndValue Step Me.cboIncrement
        rs.AddNew
        rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf
        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.03 Then
        'Add duplicate record
        addString = " DUP"
        rs.AddNew
        rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & " DUP"
        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
        'add standard
        stdID = Int((maxID - minID + 1) * Rnd + minID)
        stdName = DFirst("standard", "tblStandards", "[standardid]= " & stdID)
        rs.AddNew
        rs.Fields(MyField) = "STD1:" & SubNum & " " & stdName
        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") = True
        rs.Fields("Sizing") = False
        rs.Fields("Moisture") = False
        rs.Update
         
         
      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

As you can see (hopefully) every time the random function is triggered, it adds the record with a DUP added and selects a random record from the table tblStandards and adds it to the table. Unfortunately, the field SampleName has to be unique otherwise the database falls apart, and if the record selected from tblStandards has already been chosen, the code throws an error and stops.

What I am hoping someone will able to help me with is to allow me to have more than one instance of the record by adding a (1) or (2) at the end of the SampleName field only for the record added from the external table. That is, to run a sub-routine (possibly?) that checks for the occurence of that particular record in the table, if it sees none adds (1) to the SampleName field, if it sees one, adds (2), if it sees 2, adds (3) etc.

Thanks very much for any help you can give.

Nigel
 
Nigel,

This is very similar to writing your own number sequences.

When you assign the "MyField" value, modify this line:

rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & " DUP"

adding something like this to the end:

Code:
 & Nz(DMax("[SampleName]", "tblSampleSubmission", "[SampleName] = '" & Me.SamPre & intCounter & Me.SamSuf & "'"), 0) + 1

That should at least get you started I hope,
Wayne
 
Nigel,

Oops, assuming that you can live with "Sample Name DUP001", etc.

It's a bit much for one line of code though. Might break it into steps.

Code:
 & Format(Nz(DMax("Right([SampleName], 3)", "tblSampleSubmission", "[SampleName] Like '" & Me.SamPre & intCounter & Me.SamSuf & "DUP*'"), 0) + 1, "000")

Wayne
 
Thanks very much Wayne,

I don't think that I explained myself quite right in the first place. When the random number is triggered, it does the DUP code first, which will always be unique as it just appends DUP to the previous records SampleName.

Next it looks up a randomly selected record from the table tblStandards, gets the name of the record, then writes a new record to tblSampleSubmission with the format = "STD1:" & SubNum & " " & stdName. If it has already selected the stdName from the table tblStandards, it throws a error. There are 6 records in tblStandards that it can select from.

An Example: it selects the record with stdName = AS7 in the first instance, then the next time it is triggered, it selects the same record and tries to write the record name as

STD1:SubNum AS7, which already exists in tblSampleSubmission and hence can't write it again.

What I need it to do is to give me something like

1st Record - STD1:SubNum AS7 (1)
2nd Record - STD1:SubNum AS7 (2)

Hope that makes sense and is something that can be done.

Regards,

Nigel
 
Why not just run a select query with your criteria. If the record count is > 0 then you have a duplicate and then you can do something like

strSAmpleName = StrSamplename & "(1)"

If in you select statement you use "WHERE tblname.Samplename Like samplename & "*"

you could then have

strSamplename = strSamplename & "(" & rs.count -1 & ")"
 
Thanks for the help everyone, but I am still falling in a hole. What the biggest issue I am having is, the code automatically adds records to the table from the first number entered to the last number entered with SampleName being the unique identifier. This is indexed and no duplicates allowed. That is SampleName has to be unique otherwise it can't be added to the table. If the SampleName is not unique, the code halts and you have to end, find out where it stopped and then start the sequence again, a bit of a pain.

What may be easier for me is to select the records from tblStandards that appears first, then the next time the DUP subsection is triggered, select the next record in the table. I know that should be fairly easy, but I must be stupid, any idea on how I might do that?

As you can see from my code (well, it isn't really my code, it was made with quite a lot of help, obviously!) when the random function is triggered (around every 30 or so records) it adds DUP to the previous SampleName and then continues on. It also selects a record from the table tblStandards at random. What I need it to do now is just grab the first record form the table the first time it is triggered by the DUP routine, the second record the next and so on.

I hope I am making some sense!

Cheers,

Nigel
 
Spectro, the reason why you're having trouble getting an answer is because you are very unclear about what you want and your explanations go on tangents.

I've cleaned up your code (below), but am unsure how you're retrieving anything from tblStandards, other than maxID/minID (unless your form is bound to tblStandards).

Code:
Private Sub LogSam_Click()
On Error GoTo Err_EnterBlast_Click

    Const MyTable       As String = "tblSampleSubmission"
    Const MyField       As String = "SampleName"
    
    Dim stDocName       As String
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim intCounter      As Double
    Dim LastDub         As Double
    Dim minID           As Long                     'Used for randomly selected ID from table of standards
    Dim maxID           As Long                     'Used for randomly selected ID from table of standards
    Dim stdID           As Long                     'Used for randomly selected ID from table of standards
    Dim lRcdCount       As Long
    Dim stdName         As String
    Dim strGenID        As String                   'Generated ID
    Dim strAddString    As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(MyTable)

  'Find maximum and minimum ID number in tblStandards
    maxID = DMax("StandardID", "tblStandards")
    minID = DMin("StandardID", "tblStandards")

    Randomize
    For intCounter = Me.TxtStartValue To Me.txtEndValue Step Me.cboIncrement
        With rs
            .AddNew
                .Fields(MyField) = Me.sampre & intCounter & Me.samsuf
                .Fields("SubmissionNumber") = Me.SubNum
                .Fields("CustomerID") = Me.CustomerID
                .Fields("SamplePrep") = Me.SamplePrep
                .Fields("Fusion") = Me.Fusion
                .Fields("XRF") = Me.XRF
                .Fields("LOI") = Me.LOI
                .Fields("Sizing") = Me.Sizing
                .Fields("Moisture") = Me.Moisture
            .Update
        End With
        addstring = ""
        
        If Rnd < 0.03 Then
          'Add duplicate record
            strAddString = " DUP"
            strGenID = Me.sampre & intCounter & Me.samsuf
            lRcdCount = DCount("[" & MyField & "]", _
                               MyTable, _
                               "Left$([" & MyField & "], Instr([" & MyField & "],"" ("" -1 )='" & strGenID & "'")
            With rs
                .AddNew
                    .Fields(MyField) = strGenID & strAddString & " (" & (lRcdCount + 1) & ")"
                    .Fields("SubmissionNumber") = Me.SubNum
                    .Fields("CustomerID") = Me.CustomerID
                    .Fields("SamplePrep") = Me.SamplePrep
                    .Fields("Fusion") = Me.Fusion
                    .Fields("XRF") = Me.XRF
                    .Fields("LOI") = Me.LOI
                    .Fields("Sizing") = Me.Sizing
                    .Fields("Moisture") = Me.Moisture
                .Update
            End With
        
          'add standard
            stdID = Int((maxID - minID + 1) * Rnd + minID)
            stdName = DFirst("standard", "tblStandards", "[standardid]= " & stdID)
            With rs
                .AddNew
                    .Fields(MyField) = "STD1:" & SubNum & " " & stdName & " (" & (lRcdCount + 1) & ")"
                    .Fields("SubmissionNumber") = Me.SubNum
                    .Fields("CustomerID") = Me.CustomerID
                    .Fields("SamplePrep") = Me.SamplePrep
                    .Fields("Fusion") = Me.Fusion
                    .Fields("XRF") = Me.XRF
                    .Fields("LOI") = True
                    .Fields("Sizing") = False
                    .Fields("Moisture") = False
                .Update
            End With
        End If
    Next intCounter
    
    
    DoCmd.SetWarnings False
    stDocName = "mroLOIAppend"
    DoCmd.RunMacro stDocName
    DoCmd.SetWarnings True


Exit_EnterBlast_Click:
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Exit Sub

Err_EnterBlast_Click:
    MsgBox Err.Description
    Resume Exit_EnterBlast_Click

End Sub

I've attempted to solve what you're looking for in the code, but please restate one more time what you are trying to do.

Ex.
- You have two tables (tblStandards and tblSampleSubmissions)
- Every 30 secs you create a record in tblSampleSubmissions built off the form
- Before creating a record you check if the ID already exists and add 1 to the previous if it does [ID format: STD1:SubNum AS7 (1)]


Am I wrong in assuming that? I don't understand why you're adding a "standard" and a "duplicate" - they are both going to be the same record with different primary keys
 
Last edited:
Okay, I can understand why it is confusing as I am not explaining it very clearly.

My Database is a LIMS (laboratory information management system) which stores all the data we generate in our lab. It is made up of quite a few tables, but the main one is

tblSampleSubmission which has the following fields:

ID - the primary key an autonumber
SubmissionNumber - This applies to a batch of samples and is used to tie all the data to a search parameter, i.e. we report the results we obtain using this field.
SampleName - a unique identifier for each sample we receive
CustomerID - a customer #, identifies the client
and the fields Fusion SamplePrep, LOI etc which are the analysis types we carry out.

The records are added to this table using a form called frmSampleSubmission.

That is where the code posted previously comes in, most of the samples we receive are in numerical order, so instead of adding each sample individually, on the form you enter the first and last number in the sequence:

txtStartValue and txtEndValue

The code then adds the SampleName as a record to the table and continues doing that until it reaches the end value. The other fields in the table are constant and are added also (i.e. SubmissionNumber is the same for all the records added for each batch of samples)

The DUP is needed as it is good laboratory practice to monitor all stages of your work by doing a Duplicate (hence the DUP) sample, that is splitting a sample in half and then analysing both halves following normal methods and comparing the results at the end to ensure that the results are comparable. It is done on a random basis so as to not introduce any bias to the results which might occur if you selected every 20 sample or similar. So it will add a record for the sample to be duplicated, say 60015, then add another record 60015 DUP to the table. Hope that makes sense. From this table we then print out worksheets and labels to allow us to do our thing, so to speak.

Now, another good laboratory practice is to analyse a sample of known composition with each batch to ensure all your machines are working correctly. This is known as a Certified Reference Material (CRM) or Standard.

We use 6 Standards routinely in our lab and the names of these are stored in the table tblStandards which has the following fields

StandardID - A number 1-6
Standard - The name of the actual standard (AS7, SARM11, SARM12 etc)

and the fields with the certified values for each element we measure, which isn't relevant here.

What the code was originally designed to do was every time the DUP value was called for, select a random number between minID and maxID (in this case 1-6, but others may be added later), grab the standard name from the table tblStandards associated with this StandardID, then add it to the table in the form:

STD1:SubmissionNumber StandardName

Now the problem I have been having and haven't got across very well is if it selects the StandardID, say 3, more than once in the batch of samples it will give a record in the table with the same SampleName more than once, which can not occur as SampleName has to be unique. The reason it has to be unique is so that all the other data we generate for that particular sample has to be tied back to it and reported to the customer.

Now, what I was originally asking for was if it randomly selected StandardID say 3, more than once, when it adds the record to the table, it recognises that the record already exists in the table and appends a number after it, so it will have something like this:

STD1:SubmissionNumber AS7
in the first instance

STD1:SubmissionNumber AS7 (2)
the next time it is triggered, etc

STD1: Doesn't change, it is what our customers want to identify a standard.

I hope that makes a bit more sense and is something that is easy to do, if not, we can take the random selection out of the equation and select StandardID 1, 2, 3 etc in order, but I'm not too sure how to get it to remember where it is in the sequence as after it adds the DUP and STD1: it goes back into the normal numbering sequence.

I know that was quite long winded and I am hoping someone can help, my database person has left me in the lurch and I am having to sort it out myself.

Any help is much appreciated.
 
Before I read all that - did you try the code I posted?
 
Thanks Modest,

It probably gives me what I need, I just need to play with the code a little to get it to do what I want. Thanks for the help.
 
Thnaks for the help everyone, I couldn't really get it to work, but I have solved it using some very basic code to select the records in order in the table.
 
Thnaks for the help everyone, I couldn't really get it to work, but I have solved it using some very basic code to select the records in order in the table.

If you would like to take another stab at this, please attach your database.
 

Users who are viewing this thread

Back
Top Bottom