Can I use a Do...Loop

[Puddy]

I am using the following code to generate a # on a form.
Private Sub Command11_Click()
Dim MyDate, MyYear, MyWeek, MyDay, MyNumber As Integer
Dim Ret As String
Dim Test As Variant


MyDate = Date
MyYear = Format(Date, "yy")
MyWeek = Format(DatePart("ww", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "00")
MyDay = Format(DatePart("w", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "0")

Ret = "V" & MyYear & MyWeek & " " & MyDay

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")


If IsNull(Test) Then
Ret = Ret & " 001"
Else

MyNumber = CInt(Right(Test, 3)) + 1

Select Case MyNumber
Case Is < 10
Ret = Ret & " 00" & CStr(MyNumber)
Case Is < 100
Ret = Ret & " 0" & CStr(MyNumber)
Case Else
Ret = Ret & " " & CStr(MyNumber)
End Select

End If

Forms!Form1!Serial = Ret


My question is whether or not I could use some sort of do..loop to create multiple entries to the table based on a qty field I add to the form.

Thanks

 

[doulostheou]

I'm missing what your code is actually doing, probably because I'm not seeing the form. But if I am understanding the question properly, it is simply whether or not you can use a do loop to add entries to a table x times. The answer to that is yes.

Dim rstTableName as Recordset
Dim intNewEntries as Integer
Dim i as Integer

intNewEntries = 10 'You can replace this variable with the field on your form

Set rstTableName = CurrentDB.OpenRecordset("TableName",,dbOpenForwardOnly)

With rstTableName
   Do
      .AddNew  'Create a new record in TableName
      .Fields!FieldName = YourValue  'Set FieldName from TableName equal to a value you specify
      .Update 'Add the new row

      i = i + 1 'Increment the counter
   Loop Until i > intNewEntries  'Stop looping when the counter exceeds the desired number of entries
End With

Set rstTableName = Nothing

 

[Puddy]

right now all the form is doing is generating one serial# and upon clicking the close button,posts it to the table...I need to somehow use that code I have, as it formats and generates the sn# a certain way, but use a qty driven do loop to create the needed number of sn#'s entered into the qty field on the form.

Thanks

 

[doulostheou]

What I posted above is an example of a quantity driven do loop that adds entries to a table. You would just integrate it with your code that generates the serial#.

Change your original code to a function that returns the serial number (without changing the code itself, the following would work):

Function CreateSerialNum() as String
Dim MyDate, MyYear, MyWeek, MyDay, MyNumber As Integer
Dim Ret as String
Dim Test As Variant


MyDate = Date
MyYear = Format(Date, "yy")
MyWeek = Format(DatePart("ww", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "00")
MyDay = Format(DatePart("w", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "0")

Ret = "V" & MyYear & MyWeek & " " & MyDay

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")


If IsNull(Test) Then
Ret = Ret & " 001"
Else

MyNumber = CInt(Right(Test, 3)) + 1

Select Case MyNumber
Case Is < 10
Ret = Ret & " 00" & CStr(MyNumber)
Case Is < 100
Ret = Ret & " 0" & CStr(MyNumber)
Case Else
Ret = Ret & " " & CStr(MyNumber)
End Select

End If

CreateSerialNum = Ret

End Function

Then use that function to populate the correct field value in the code I gave you above:

Fields!FieldName = CreateSerialNum()

 

[Puddy]

thanks, I'll give that a try, though now I have to figure out why the code isn't working...it was just the other day, but now for some reason, it will not go to 002, just keeps giving me 001...when I try to debug, the Test= part of the code comes back null, which is why it's going to 001, but I can't figure out why it is doing that when there is data in there.

 

[Puddy]

Well, i just about got this working..I am now able to generate the serial number, based on a qty needed by using the following code:

Dim rstTableName As Recordset
Dim intNewEntries As Integer
Dim i As Integer

intNewEntries = Forms!Form1!Qty 'You can replace this variable with the field on your form

Set rstTableName = CurrentDb.OpenRecordset("Table1", , dbForwardOnly)

With rstTableName
Do
.AddNew 'Create a new record in TableName
Forms!Form1!Serial = CreateSerialNum()
.Fields!Serial = Forms!Form1!Serial 'Set FieldName from TableName equal to a value you specify
.Fields!Customer = Forms!Form1!Combo13
.Fields!Model = Forms!Form1!Model
.Fields!PO = Forms!Form1!PO
.Fields!Initials = Forms!Form1!Combo15
.Fields!Qty = Forms!Form1!Qty
.Update 'Add the new row

i = i + 1 'Increment the counter
Loop Until i > intNewEntries 'Stop looping when the counter exceeds the desired number of entries

End With

Set rstTableName = Nothing

The last thing I need to clear up is that for some reason, I am getting an extra record everytime this is run, meaning if I put in a qty of 5, I actually get 6 records, why would this be happening?

Thanks

 

[doulostheou]

It should say

Loop Until i >= intNewEntries

I mislead you on my initial post, sorry. You could also switch it and say

Do Until i > intNewEntries

If you put the code in the Do section, it will exit the loop before the code executes. If you put it in the Loop section, it will exit the loop after the code executes. That is why looping until i was greater than intNewEntries gave you an extra entry.