Solved Autonumber Advice (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:12
Joined
May 7, 2009
Messages
19,169
what table is the code for?
are we in a subform?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
What @arnelgp was trying to point out is that you were searching for the next suffix for an order number, but you were NOT using ordernumber to create the order_shipment_Number?

As I stated, you need to walk through this code and inspect the variables.?
Yes, it is slow, but is the only way to spot errors like this, if you cannot do it via looking at the code.?

All these terms are familiar to you and not too us, so for us to spot them is even harder?

So with the last incarnation you do appear to be comparing apples and oranges?
You are comparing deliver number with order number?, but the delivery number will always have a suffix and the order number will not.?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:12
Joined
May 7, 2009
Messages
19,169
yes, Gasman.

where is the OrderNumber? on the Main Form?
 

ChrisMore

Member
Local time
Today, 10:12
Joined
Jan 28, 2020
Messages
174
What @arnelgp was trying to point out is that you were searching for the next suffix for an order number, but you were NOT using ordernumber to create the order_shipment_Number?

As I stated, you need to walk through this code and inspect the variables.?
Yes, it is slow, but is the only way to spot errors like this, if you cannot do it via looking at the code.?

All these terms are familiar to you and not too us, so for us to spot them is even harder?

So with the last incarnation you do appear to be comparing apples and oranges?
You are comparing deliver number with order number?, but the delivery number will always have a suffix and the order number will not.?
I am new to using VBA and have never troubleshooted code before, so I am not familiar with what you mean by 'walk through this code and inspect the variables".

I see what you mean, the Order_Shipment_Number will never be the same as the Order_Number so they can't be compared. It's only the Order_Shipment_Number that has the suffix. Maybe it needs to be the Order_Number from the parent table (Customer_Orders) compared to Order_Number in the child table (Shipments)?

Order_Number is on the main form called Customer_Orders_Form
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
I am new to using VBA and have never troubleshooted code before, so I am not familiar with what you mean by 'walk through this code and inspect the variables".

I see what you mean, the Order_Shipment_Number will never be the same as the Order_Number so they can't be compared. It's only the Order_Shipment_Number that has the suffix. Maybe it needs to be the Order_Number from the parent table (Customer_Orders) compared to Order_Number in the child table (Shipments)?
This link will explain it much better than I can.
https://www.techonthenet.com/access/tutorials/vbadebug2010/debug01.php
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
I am new to using VBA and have never troubleshooted code before, so I am not familiar with what you mean by 'walk through this code and inspect the variables".

I see what you mean, the Order_Shipment_Number will never be the same as the Order_Number so they can't be compared. It's only the Order_Shipment_Number that has the suffix. Maybe it needs to be the Order_Number from the parent table (Customer_Orders) compared to Order_Number in the child table (Shipments)?

Order_Number is on the main form called Customer_Orders_Form
Well it could be compared IF you stripped off the suffix, but it is important to compare like with like.?
Left(DeliveryOrderNumber,Len(DeliveryOrderNumber)-1) or you could use Like ?

Code:
? dlookup("description","testtransactions","[description] like 'Ms T*'")
Ms T.Stone:199633
syntax will be the same.

Personally I think I would have a field on the order number record called DeliverySuffix and look up that field in the order table for an order and that would give me the last delivery suffix numeric number, which you convert to alphabetical character?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:12
Joined
May 7, 2009
Messages
19,169
if Order_Number is in "main form":
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strOrderNumber As String
Dim iNextSuffix As Integer
strOrderNumber = Me.Parent!Order_Number
iNextSuffix = 65
With Me.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        iNextSuffix = iNextSuffix + 1
        .MoveNext
    Loop
End With
Me.Order_Shipment_Number = Format(strOrderNumber, "00000") & Chr(iNextSuffix)
End Sub
 

ChrisMore

Member
Local time
Today, 10:12
Joined
Jan 28, 2020
Messages
174
if Order_Number is in "main form":
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strOrderNumber As String
Dim iNextSuffix As Integer
strOrderNumber = Me.Parent!Order_Number
iNextSuffix = 65
With Me.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        iNextSuffix = iNextSuffix + 1
        .MoveNext
    Loop
End With
Me.Order_Shipment_Number = Format(strOrderNumber, "00000") & Chr(iNextSuffix)
End Sub
Thanks so much arnelgp, that has got it working perfectly. Also thanks very much for your help Gasman, and for being patient with me!

I really want to learn VBA to be able to write code like this. Do you learn that through experience or reference books? What advice can you give me on how to start learning VBA?

Thanks,
Chris
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:12
Joined
Sep 21, 2011
Messages
14,038
Thanks so much arnelgp, that has got it working perfectly. Also thanks very much for your help Gasman, and for being patient with me!

I really want to learn VBA to be able to write code like this. Do you learn that through experience or reference books? What advice can you give me on how to start learning VBA?

Thanks,
Chris
I still Google constantly on how to do something. Fortunately now, being mostly retired, I do not even have to do that much these days. :)

Certainly learn some basic debugging techniques. Especially useful for what I call 'silly' errors, which generally hard to find. :)
Plenty of videos on YouTube as well.
 

ChrisMore

Member
Local time
Today, 10:12
Joined
Jan 28, 2020
Messages
174
Hi @arnelgp,

I have run into a small issue with the code you wrote for me yesterday. When consecutive new records are added to the Shipments table on a new Customer_Order record the suffix on the Order_Shipment_Number doesn't move to the next letter. I have found the only way around it is to exit the main form and go back into it to insert the next record into the Shipments table. Once you go back into the record it then works to insert multiple consecutive new shipment records.

I have attached a screenshot of the issue happening.

Please can I have your assistance in fixing the issue.

Thank you,
Chris
 

Attachments

  • Screenshot 2.png
    Screenshot 2.png
    105.8 KB · Views: 132

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:12
Joined
May 7, 2009
Messages
19,169
what is the table name for the "shipment detail":

Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strOrderNumber As String
strOrderNumber = Me.Parent!Order_Number
Me.Order_Shipment_Number = Format(strOrderNumber, "00000") & Chr$(65 + DCount("1", "DELIVERY_TABLE_NAME_HERE", "[Order_Shipment_Number] Like '" & strOrderNumber & "*'"))
End Sub
 

ChrisMore

Member
Local time
Today, 10:12
Joined
Jan 28, 2020
Messages
174
what is the table name for the "shipment detail":

Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strOrderNumber As String
strOrderNumber = Me.Parent!Order_Number
Me.Order_Shipment_Number = Format(strOrderNumber, "00000") & Chr$(65 + DCount("1", "DELIVERY_TABLE_NAME_HERE", "[Order_Shipment_Number] Like '" & strOrderNumber & "*'"))
End Sub
The table is called "Shipments" so I entered that where you said to in the code and it's solved the problem. Thanks so much once again.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:12
Joined
May 7, 2009
Messages
19,169
you're welcome!
 

deolsunny533

New member
Local time
Today, 15:42
Joined
Nov 10, 2020
Messages
1
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.
 

IbrBadri

Member
Local time
Today, 12:12
Joined
May 24, 2020
Messages
35
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.
Refer to my previous replay https://www.access-programmers.co.uk/forums/threads/autonumber-advice.314336/post-1727941
 

Users who are viewing this thread

Top Bottom