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:
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
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