Adding Records to a Table

[spectrolab]

Hi All,

I have the following code which populates a table using the start and values entered in a form. I was wondering if there was any way to define the number increment that it uses? In some cases, it only needs to enter every second number, is there any way to tell it to do this in vba? What would be easiest is if I put another command on the form and say increment by this number, like 2 or 1. I have been trying on this for days and am no closer to a solution. Any help is greatly appreciated.

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
        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.02 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

 

[chergh]

Yeah you can do it like this:

For intCounter = Me.TxtStartValue To Me.txtEndValue Step 2

that would increase intcounter by 2 each step. Obviously you can change '2' to what ever value you desire including negative values.