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

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.