Autofilling information bewteen two fields 1

[m3rc]

Creating a new database and for the Data Entry I need to have a beginning serial number and and ending serial number. Say the sale was 50 orders (beginning serial)55-12550 through (ending serial)55-12600. How can I have it automatically enter the numbers between the beginning and ending serial number (12551, 12552...12559)? Is this possible or should I take another approach?

 

[MajP]

Sure it is doable, but depending on how you are planning to use it, it may or may not make sense. Here is an example that would make sense. Assume you have two tables, tblSales and tblSaleItems. A "Sale" has an ID, date, and quantity of items sold, and starting serial number value. tblSaleItems has an item serial number, some other fields, and a foriegn key referencing which "Sale" it is related to.

So you have a form to enter a new Sale. On the afterupdate event of the form it runs a method called addSaleItems. This methods reads the quantity sold and the starting serial. It increments the serial up to the quantity sold and adds a record to the tblSaleItems. Each time it adds a record it puts in the serial number, and the related Sale ID.

If this is what you want to do, provide more details. Need table names and related field names. Provide a little more detail on how exactly you want it to work.

 

[m3rc]

Yes, sounds like were on the same page here. Adding the beginning serial number and quanity should work and make a new entry into the tblSerial for each to fill it up. The main table is tblItems with Order# related to Serial# in the tblSerial. If you could help with the VB part that would be great.

 

[MajP]

My main table in this example is tblOrders with an auto ID for an Order. I have a form to add orders and it has order quantity and starting serial. I pass this information from the form's after update event to the subroutine, makeSerial.
There is no error checking in here so there are lots of places where it can bomb.

Assumptions for this.
1) there is one "-" in the serial. Only increment after the "-". i.e. 55-1234 or 123-456 or abc-7890
but not 123-123-45.
2) If I pass 55-xxxx I will never increment beyond
55-9999.

Public Sub makeSerial(strTblName As String, strFieldName As String, intQuantity As Integer, strSerialStart As String, intPosition As Integer, lngOrderNumber As Long)
  '55-12345  The intPosition is 3 where the "-" is
  MsgBox "serial"
  Dim rs As DAO.Recordset
  Dim lngNumberPart As Long
  Dim strStringPart As String
  Dim intCounter As Integer
  Set rs = CurrentDb.OpenRecordset(strTblName, dbOpenDynaset)
  lngNumberPart = CLng(Mid(strSerialStart, intPosition + 1))
  strStringPart = Left(strSerialStart, intPosition - 1)
  For intCounter = 0 To intQuantity - 1
    rs.AddNew
    rs.Fields(strFieldName) = strStringPart & lngNumberPart + intCounter
    rs.Fields("intOrder#") = lngOrderNumber
    rs.Update
  Next intCounter
End Sub

Private Sub Form_AfterUpdate()
  Dim quantity As Integer
  Dim serial As String
  Dim position As Long
  quantity = Me.intQuantityOrdered
  serial = Me.txtStartingSerial
  position = InStr(serial, "-")
  Call mdlMakeSerial.makeSerial("tblItems", "Serial #", quantity, serial, CInt(position), Me.autoOrderID)
End Sub

 

[m3rc]

Got it to the point where it bombs out on a compile error: Method or data member not found. On the Me.autoOrderID highlighted on (.autoOrderID).

 

[MajP]

In my tblOrders the auto ID is "autoOrderID". Therefore
me.autoOrderID refers to the field autoOrderID on the form. If you have different names you will get this error.

 

[m3rc]

Ok got it past that point now on two errors. If I use a letter with numbers in the autoOrderID field I get runtime error 13 type mismatch on

Call mdlMakeSerial.makeSerial("tblOrders", "Serial#", quantity, serial, CInt(position), Me.autoOrderID)

With numbers only I get runtime error 3265 item not found in this collection on

rs.Fields("intOrder#") = lngOrderNumber

 

[MajP]

1. Public Sub makeSerial(strTblName As String, strFieldName As String, intQuantity As Integer, strSerialStart As String, intPosition As Integer, lngOrderNumber As Long)

I assumed that the order ID would be a autonumber so if it is a string you could just change
lngOrderNumber As String (or variant)

2) You need to have a field in your table "intOrder#" with that exact name.

 

[m3rc]

Alright I got it working pretty much the way I just can't get it work with a relationship between the tblOrders & tblSerial relationship being between Order# & Serial#. I need these linked together so you know which serial numbers are which.

 

[MajP]

I just used the field names and table names as examples. Yours might be different. In my example I linked the order table to the tblItems by the order ID.



Example in tblOrders

autoOderID intQuantity strStartingSerial
1234 3 55-0001


tblItems

Serail# intOrderID
55-0001 1234
55-0002 1234
55-0003 1234

The three items link to the order by the intOrderID.

I do not understand a link between Order# and Serial#. A order has many items which are related by the order#. Maybe we just have our names criss crossed.

 

[m3rc]

One more thing is that when it creates the serial numbers it takes out the "-". The format is 5?-????? "56-12345" 5 never changing and 6 being the year.

 

[MajP]

Take out the -1
strStringPart = Left(strSerialStart, intPosition - 1)
to
strStringPart = Left(strSerialStart, intPosition)

 

[m3rc]

Thanks it's working great couldn't have done it without you.

 

[MajP]

However, like I said there is no error checking in this. I can see a lot of places where you could make inadvertant records or throw and error.

 

[m3rc]

How can I make it so whenever you enter 56-00020 it will show that when entered into the table instead of 56-20.

 

[PHV]

Perhaps this ?
rs.Fields(strFieldName) = strStringPart & Format(lngNumberPart + intCounter, "00000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886