Solved Autonumber Advice

[Gasman]

Thanks Gasman, I understand what you are saying now and I like the idea. I am still learning vba so unfortunately I wouldn't know where to start with writing the code.

Ok,
You would have a numeric field in the table for last used suffix (but in numeric form) along with a field for the order number. Let's call that suffix field DeliverySuffix
On the the very first delivery there will be no value so DMAX() would return Null, so we would use the NZ() function to catch that.

iNextSuffix = NZ(DMAX("DeliverySuffix","DeliveryTable","OrderNo = " & Me.OrderNo),0) + 1

Now we have the value 1, so we use that as

txtDeliveryCode = Format(Me.OrderNo,"0000") & CHR(64 + iNextSuffix)

which would produce something like 1234A and the table would now have a value of 1 in that record along with the OrderNo

Then the next time, you would do the same, but this time it would find the record an return that 1 value and you would add 1 to it and have 2 as a value and do the same thing.?

I would create a function that returns the new delivery code by passing the required code to that function. Then it would be
txtDeliveryCode = NextDeliverySuffix(Me.OrderNo)

You might not have the actual OrderNo, but the autonumber FK for that order, but the process would be the same.?

HTH

 

[ChrisMore]

Ok,
You would have a numeric field in the table for last used suffix (but in numeric form) along with a field for the order number. Let's call that suffix field DeliverySuffix
On the the very first delivery there will be no value so DMAX() would return Null, so we would use the NZ() function to catch that.

iNextSuffix = NZ(DMAX("DeliverySuffix","DeliveryTable","OrderNo = " & Me.OrderNo),0) + 1

Now we have the value 1, so we use that as

txtDeliveryCode = Format(Me.OrderNo,"0000") & CHR(64 + iNextSuffix)

which would produce something like 1234A and the table would now have a value of 1 in that record along with the OrderNo

Then the next time, you would do the same, but this time it would find the record an return that 1 value and you would add 1 to it and have 2 as a value and do the same thing.?

I would create a function that returns the new delivery code by passing the required code to that function. Then it would be
txtDeliveryCode = NextDeliverySuffix(Me.OrderNo)

You might not have the actual OrderNo, but the autonumber FK for that order, but the process would be the same.?

HTH

Thanks very much for your help with this.

Does this require the order to already have an order number or does this create the order number and the suffix? I don't know if I'm right in doing this but I added the code to the customers orders form in the BeforeInsert() of the Order_Number field. That is what I did before to get the next order number using the code:

Order_Number = Nz(DMax("Order_Number", "Customer_Orders")) + 1

 

[Gasman]

I would expect you to have the order number and suffix number inserted into the table when appropriate.?
The very first time, there would not be any order number and obviously no suffix number in that case.?

Another way would be to just have one record for each order and increment that in the process, in much the same way as an invoice number might be generated, but I myself would go with the way I described for this situation.

 

[ChrisMore]

I would expect you to have the order number and suffix number inserted into the table when appropriate.?
The very first time, there would not be any order number and obviously no suffix number in that case.?

Another way would be to just have one record for each order and increment that in the process, in much the same way as an invoice number might be generated, but I myself would go with the way I described for this situation.

Can your suggestion still work if I use DMax to act as an 'autonumber' so the user doesn't need to manually enter the order number. This is an important part of the process.

Thanks,
Chris

 

[Gasman]

It could, but I would have thought your order numbers would be more structured?
Again, you would have a record that holds last ordernumber used and add one to that (or whatever your process does now).

I would NOT use an autonumber as order number, only for it's intended process, and cannot stress this enough.

 

[ChrisMore]

It could, but I would have thought your order numbers would be more structured?
Again, you would have a record that holds last ordernumber used and add one to that (or whatever your process does now).

I would NOT use an autonumber as order number, only for it's intended process, and cannot stress this enough.

Yeah as everyone on this forum has advised, I am trying to avoid an autonumber so using DMax as an alternative. The Customer_Orders tables holds the order numbers for the code to find the last used number and +1.

I'd be interested to hear how the order numbering process could be more structured. Our current process isn't complex or at all sophisticated but does work for us, we use an Excel spreadsheet with one column containing the order numbers filled down and the order number of the order is decided based on what row the order details are added to. I wanted to improve this by using the Access database instead.

 

[Zedster]

Hi Zedster,

I was initially looking at using a child table for deliveries (screenshots of this are above) but the problem I had with that was getting it to work with my inventory control code. The code uses the product code and quantity fields in the orders child table to reduce the stock so I am unsure of how to change it to use the child table for deliveries instead. Unless the two child tables could work hand in hand maybe I wouldn't need to change it, but I don't know if that would work.

I don't have a problem with splitting a single order into 3 separate orders with letter suffixes as that is currently our company process. I am still learning vba so unsure how to get the incremental number to work that gasman suggested. I do like the sound of the idea though.

Thanks,
Chris

I am thinking on my feet a little, but from an operational perspective, it would make little difference whether parts were "related" many to many with "orders" or many to many with a child table of orders called "dispatches" it could still be used to reduce your stock. Alternatively a "dispatches" table could be transactional. But it sounds like the main reason to stay as you are is because your existing company processes still use a suffix.

Regarding the number generating, the way I generally do it is have a text "number" eg T00100A then use DAO to search all records and identify the largest number currently assigned by extracting the middle 5, having found the largest "number" I would then find the "largest" suffix for that number. If you want to add a new order increment the number and the new one is "T00101A" if you want to split it the new suffixed number becomes "T00100B". There may be more efficient ways.

 

[ChrisMore]

I am thinking on my feet a little, but from an operational perspective, it would make little difference whether parts were "related" many to many with "orders" or many to many with a child table of orders called "dispatches" it could still be used to reduce your stock. Alternatively a "dispatches" table could be transactional. But it sounds like the main reason to stay as you are is because your existing company processes still use a suffix.

Regarding the number generating, the way I generally do it is have a text "number" eg T00100A then use DAO to search all records and identify the largest number currently assigned by extracting the middle 5, having found the largest "number" I would then find the "largest" suffix for that number. If you want to add a new order increment the number and the new one is "T00101A" if you want to split it the new suffixed number becomes "T00100B". There may be more efficient ways.

I like the idea, especially if it has worked for you in the past.

Our number structure is a bit more simple with just 5 numbers and a letter suffix, although the suffix would only exist if the order is split. If your idea will only work by always having the suffix with the 5 digit number, regardless if the order is split or not, I don't think that would be an issue for the company.

How would Access know that the order is split and requires the next letter of the suffix with the same number if the order number is auto generated?

Thanks,
Chris

 

[Gasman]

TBH, I have not had to use that method, but see no reason why it would not work.?
You could use Dcount() instead if you wanted the first delivery not to have any suffix.?,

As to how Access will know, that would be up to you.? Access does not know anything you do not tell it.?

EG. We create an order for 10 items. For whatever reason we can only send 4 now, so the delivery record or whatever you want to use is created.
Then you have to decide how the rest get delivered, and apply the suffix.

Really it should be as you do now manually, but just automated, unless automation can also do it better by changing the process.?

 

[ChrisMore]

TBH, I have not had to use that method, but see no reason why it would not work.?
You could use Dcount() instead if you wanted the first delivery not to have any suffix.?,

As to how Access will know, that would be up to you.? Access does not know anything you do not tell it.?

EG. We create an order for 10 items. For whatever reason we can only send 4 now, so the delivery record or whatever you want to use is created.
Then you have to decide how the rest get delivered, and apply the suffix.

Really it should be as you do now manually, but just automated, unless automation can also do it better by changing the process.?

I realized that sounded like a stupid question when I sent it but didn't try to word it any better!

I have thought of one method which could be an option to get this to work. Currently the customer order form has a command button which inserts a new record. As well as inserting a new record, the button could use DMax to auto generate the next number, its probably best if the number always has a suffix so that will stay with the 5 digit number. I would then add a new button to the form which auto generates a new split dispatch order number to the order by taking the order number and then incrementing the suffix to the next letter using Zedster's idea from earlier.

Has it got potential?

 

[Gasman]

I would say so, but without knowing your process/business the answer is generic.
What happens if an order is cancelled?, are there going to be delivery records already created.

Thinking more about it, I'd have a delivery number without any suffix. That easily identifies it was not a split delivery.?, any suffix indicates it was.?
Is the delvery number always going to be the order number, but perhaps with a suffix.?

 

[ChrisMore]

I would say so, but without knowing your process/business the answer is generic.
What happens if an order is cancelled?, are there going to be delivery records already created.

Thinking more about it, I'd have a delivery number without any suffix. That easily identifies it was not a split delivery.?, any suffix indicates it was.?
Is the delvery number always going to be the order number, but perhaps with a suffix.?

I see what you're saying...so use the deliveries table to store the order numbers with the suffix. I'm a bit confused with how the order number is displayed on the customer order form in that case. If the order isn't split it would display the order number without a suffix (the Order_Number from the Customer_Orders table) but if it is split then it would display the order number with the suffix from the Deliveries table.

If an order is cancelled the status changes to 'Cancelled' and the dates and quantities get zeroed. Unless there is a 'proper' way of doing it?

 

[Gasman]

When do you know when a delivery will be a split delivery. When you do you know how many deliveries it will be?

All these questions need to be answered and then handled by the process.?
It is this information that determines how you handle the process, or should do.?

If you have a split delivery, do you want to know what was in each delivery? I would have thought so?, so you would need to link an item in the order to a particular delivery?

 

[ChrisMore]

When do you know when a delivery will be a split delivery. When you do you know how many deliveries it will be?

All these questions need to be answered and then handled by the process.?
It is this information that determines how you handle the process, or should do.?

If you have a split delivery, do you want to know what was in each delivery? I would have thought so?, so you would need to link an item in the order to a particular delivery?

The order will need to have the functionality to be split at any moment. The customer could say when the order is placed they want the order delivered sporadically, or due to stock shortages or a change of mind the order could be split at a later stage.

I need to a have a further think about it as I've got a feeling we are over complicating things based on the simple processes we have in the company.

Personally, my gut is saying that having a command button on the order form which generates a split delivery order number is the way to go. So a blank order form for each delivery. Also using DMax +1 on the Before Insert property of the form to generate the order number to begin with. I'm not sure whether it's a good idea to have the suffix on all order numbers even if the order is not split though.

 

[Gasman]

As I said, I would only have a suffix if the order is split, so you could have a record with a Null/Blank suffix as there was only one delivery and the order was not split. Otherwise if you have all deliveries with an A suffix, is there a B,C,D etc?

You still have some work to do, but do it in small steps. Then if something does not work, you know where to look, rather than piling a bunch of code together and then trying to work out where it is going wrong.?

Break the process down to simple steps and identify how these steps will affect each other.

Good luck with it.

 

[IbrBadri]

Thanks for the example. I have only started to use Access in the last year so I am struggling to make sense of how I can implement it into my database. However, I can see how the 'CustomSeqNum' in your example could be used in my database to generate the shipment number.

At this time I think it is best to share with you what I have so far. I would share the whole database but it does hold a lot of confidential data. I have attached a screenshot of my customer orders form and another of the database relationships.

I have a table for Customer_Orders, Customer_Orders_Items and Shipments. The relationship between these works so the user can create multiple shipments under one order number if the order is split into multiple parts, or just one shipment if the order is to be delivered in full. The user can then select which shipment each product is on under 'Order Shipment Number' of the 'Bill of Materials' section. At the moment this field is a combo which lists all shipment numbers so it would work better if it only listed the shipment numbers associated with each order.

The problem with this set up is that the user could select the wrong shipment number for a product and it will mess up the inventory calculation. The inventory calculation uses the 'Production Complete Date' field as the identifier to whether to reduce the stock for the product. Therefore, my code will need to be amended so it only includes in the inventory calculation the products in the 'Bill of Materials' section assigned to a shipment number that has a 'Production Complete Date' set. As I have previously said, I took the code from Allen Browne's Inventory Control: Quantity on Hand guide so don't feel confident in updating it to get this system to work.

Let me know if you need anymore information about my database.

Thanks for your assistance,
Chris

Hi ChrisMore
To limit the combo to list only the shipment number associated with each order, you need to but a "criteria" in the query of the combo box, in the form design view, Property Sheet, Row Source of the combo box.
- Remove the "Shipments" table "or whatever it's" from the Row Source.
- Press the " ..." at the write to build a query.
- Add the "Shipments" table from the "Show Table" menu to the query.
- Drag the two fields "Order_Shipment_Number" and then the "Order_Number" in the query fields.
- At the criteria row, under the "Order_Number" column, press the right mouse and select "Build", it is open for you the "Expression Builder" menu.
- Select your form name by press the + sign until you reach the "Order_Number" and double click it, press Ok, close the "Expression Builder".
- Close the query builder and save.

By this way when you open the dropdown list of the combo, it will show only the shipment number associated with each order.

Hop it solve your issue.

 

[ChrisMore]

Hi ChrisMore
To limit the combo to list only the shipment number associated with each order, you need to but a "criteria" in the query of the combo box, in the form design view, Property Sheet, Row Source of the combo box.
- Remove the "Shipments" table "or whatever it's" from the Row Source.
- Press the " ..." at the write to build a query.
- Add the "Shipments" table from the "Show Table" menu to the query.
- Drag the two fields "Order_Shipment_Number" and then the "Order_Number" in the query fields.
- At the criteria row, under the "Order_Number" column, press the right mouse and select "Build", it is open for you the "Expression Builder" menu.
- Select your form name by press the + sign until you reach the "Order_Number" and double click it, press Ok, close the "Expression Builder".
- Close the query builder and save.

By this way when you open the dropdown list of the combo, it will show only the shipment number associated with each order.

Hop it solve your issue.

Hi IbrBadri,

Thanks for your help with that, I've got that combo working now.

The next step to get this method to work is to update the code which calculates the inventory totals. The 'Get the quantity used since then' section of the code will need to be amended to take into account that the Production_Complete_Date is now in the Shipments table (before it was in the Customer_Orders table). It also needs to be changed so the records in the "Bill of Materials" section (this is the Customer_Orders_Items table) will have their Production_Complete_Date based on the Order_Shipment_Number they are assigned with.

The code I currently have is:

Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant) As Long
    'Purpose:   Return the quantity-on-hand for a product.
    'Arguments: vProduct_Code = the product to report on.
    '           vAsOfDate  = the date at which quantity is to be calculated.
    '                           If missing, all transactions are included.
    'Return:    Quantity on hand. Zero on error.
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim lngProduct As Long          'vProduct_Code as a long.
    Dim strAsOf As String           'vAsOfDate as a string.
    Dim strSTDateLast As String     'Last Stock Take Date as a string.
    Dim strDateClause As String     'Date clause to use in SQL statement.
    Dim strSQL As String            'SQL statement.
    Dim lngQtyLast As Long          'Quantity at last stocktake.
    Dim lngQtyAcq As Long           'Quantity acquired since stocktake.
    Dim lngQtyUsed As Long          'Quantity used since stocktake.

    If Not IsNull(vProduct_Code) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProduct_Code
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "dd\/mm\/yyyy") & "#"
        End If

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTake_Date <= " & strAsOf & ")"
        End If
        strSQL = "SELECT TOP 1 Stocktake_Date, Product_Quantity FROM Stocktake " & _
            "WHERE ((Product_Code = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Stocktake_Date DESC;"

        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!Stocktake_Date, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Product_Quantity, 0)
            End If
        End With
        rs.Close

        'Build the Date clause
        If Len(strSTDateLast) > 0 Then
            If Len(strAsOf) > 0 Then
                strDateClause = " Between " & strSTDateLast & " And " & strAsOf
            Else
                strDateClause = " >= " & strSTDateLast
            End If
        Else
            If Len(strAsOf) > 0 Then
                strDateClause = " <= " & strAsOf
            Else
                strDateClause = vbNullString
            End If
        End If

        'Get the quantity acquired since then.
        strSQL = "SELECT Sum(Purchase_Orders_Items.Amount_of_Goods_Received) AS Amount_of_Goods_Received " & _
            "FROM Purchase_Orders INNER JOIN Purchase_Orders_Items ON Purchase_Orders.Purchase_Order_Number = Purchase_Orders_Items.Purchase_Order_Number " & _
            "WHERE ((Purchase_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Purchase_Orders_Items.Date_Goods_Arrived " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!Amount_of_Goods_Received, 0)
        End If
        rs.Close

        'Get the quantity used since then.
        strSQL = "SELECT Sum(Customer_Orders_Items.Order_Quantity) AS Order_Quantity " & _
            "FROM Customer_Orders INNER JOIN Customer_Orders_Items ON " & _
            "Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Customer_Orders.Production_Complete_Date " & strDateClause & "));"
        End If

        Set rs = db.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            lngQtyUsed = Nz(rs!Order_Quantity, 0)
        End If
        rs.Close

        'Assign the return value
        onhand = lngQtyLast + lngQtyAcq - lngQtyUsed
    End If

    Set rs = Nothing
    Set db = Nothing
    Exit Function
End Function

Thanks,
Chris

 

[Gasman]

Chris,
For SQL you should have the date format as mm/dd/yyyy or yyyymmdd

FWIW I have used this in some of my DBs

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Allen Browne also has a function

http://allenbrowne.com/ser-36.html

 

[ChrisMore]

Chris,
For SQL you should have the date format as mm/dd/yyyy or yyyymmdd

FWIW I have used this in some of my DBs

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Allen Browne also has a function

http://allenbrowne.com/ser-36.html

Ok, thanks Gasman.

Are you referring to this part of the code?

 If Not IsNull(vProduct_Code) Then
        'Initialize: Validate and convert parameters.
        Set db = CurrentDb()
        lngProduct = vProduct_Code
        If IsDate(vAsOfDate) Then
        strAsOf = "#" & Format$(vAsOfDate, "dd\/mm\/yyyy") & "#"
        End If

so it should read?:

strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"

I haven't had any issues with the code since I've been running it. What difference does changing that make?

Thanks,
Chris

 

[Gasman]

Yes, that is the part I spotted, yet you have other date formats in the correct order?

What difference does it make? well 01/12/2020 can either be 1st December or 12th January.? Which is it.?
That format ONLY works when it cannot be anything else like 29/10/2020 as there are not 29 months.?

I'm surprised you have not had any issues TBH as you are comparing strSTDateLast which is mm/dd/yyyy format with strAsOf which is in dd/mm/yyyy format??????