Its for the Shipments table.what table is the code for?
are we in a subform?
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".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.?
This link will explain it much better than I can.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)?
Well it could be compared IF you stripped off the suffix, but it is important to compare like with like.?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
? dlookup("description","testtransactions","[description] like 'Ms T*'")
Ms T.Stone:199633
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!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
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.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
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.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
Refer to my previous replay https://www.access-programmers.co.uk/forums/threads/autonumber-advice.314336/post-1727941I 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.