Search results

That returned B in the Order_Shipment_Number field and 0 in the Delivery_Suffix field

I am trying to auto generate the Order_Shipment_Number. For every new record added to the Shipments table for each Order_Number, the number in the Delivery_Suffix field in the Shipments table should increase in increments of 1. This number is converted to a letter using ASCII and this letter...

I have been able to get this code to run: Private Sub Form_BeforeInsert(Cancel As Integer) iNextSuffix = Nz(DMax("Delivery_Suffix", "Shipments", "Order_Shipment_Number='" & Me.Order_Number & "'"), 0) + 1 Me.Order_Shipment_Number = Format(Me.Order_Shipment_Number, "00000") & Chr(64 + iNextSuffix)...

The code doesn't seem to be correct as it's not doing anything. It doesn't produce an error either. I have formatted the order numbers as short text as they required letters.

This is what I have written so far Private Sub Order_Shipment_Number_BeforeUpdate(Cancel As Integer) iNextSuffix = Nz(DMax("Delivery_Suffix", "Shipments", "Order_Shipment_Number='" & Forms!Customer_Orders!Order_Number& & "'"), 0) + 1 txtDeliveryCode = Format(Me.Order_Shipment_Number, "00000") &...

I changed the code and managed to get it working. 'Get the quantity used since then. strSQL = "SELECT Sum(Customer_Orders_Items.Order_Quantity) AS Order_Quantity " & _ "FROM Shipments INNER JOIN Customer_Orders_Items ON " & _...

Yeah, confusing. Maybe there's something I've got formatted elsewhere that the code is counteracting. But that's just my uneducated opinion. The Order_Shipment_Number field in the Shipments table has a one-to-many relationship with the Customer_Orders_Items table. Is this not a link which will...

Following on from changing it, the code isn't working now, so it needs to be the format I had originally...

Strange... I have amended it anyway so thanks for pointing it out. Any thoughts on amending the code to include the Shipments table as per my previous post today? Thanks, Chris

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...

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...

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...

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...

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...

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...

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...

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

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...

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.

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...