Not sure what to do...

  • Thread starter Thread starter DavidPuddyFan
  • Start date Start date
D

DavidPuddyFan

Currently using the following code to generate a serial# on a data entr
form. Once the cmdButton is clicked, the code basicall
generates/formats the sn# and puts it into the Serial# field. Righ
now I can only create one record at a time, I would like to be able t
add multiple records to the table based on a qty field I would add o
have the user input. I am not sure how to go about this, and any hel
would be great...here is the code I am currently using to generate th
sn# to the 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

End Su
 
don't want this to get lost in the shuffle...anyone have any ideas
could use..thank
 
don't want this to get lost in the shuffle...anyone have any ideas
could use..thank
 
Not to answer your question :-( , but: adding multiple records to a table
based on a qty field, is often not the way to go. It may suggest an error in
your database design. What is the table in question? What "thing" is
described by a single row in that table? What is the primary key of that
table?

HTH,
TC
 
Back
Top