Solved Autonumber Advice

Gasman said:
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??????
Click to expand...
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
 
ChrisMore said:
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
Click to expand...
Following on from changing it, the code isn't working now, so it needs to be the format I had originally...
 
ChrisMore said:
Following on from changing it, the code isn't working now, so it needs to be the format I had originally...
Click to expand...
Well I do not know what to say. ;-(
Even if I was completely wrong about the date format, I cannot see how comparing with two completely different formats is ever going to produce the correct result.? :(
As for the Shipments table, there must be a link, so you can either bring in the date in the source query or use a dLookUp()
 
Gasman said:
Well I do not know what to say. ;-(
Even if I was completely wrong about the date format, I cannot see how comparing with two completely different formats is ever going to produce the correct result.? :(
As for the Shipments table, there must be a link, so you can either bring in the date in the source query or use a dLookUp()
Click to expand...
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 work? Otherwise I'm not sure what you mean.
 
I do not know your system remember?
All I am saying is if you moved the date from one table to another, then there must be a way you populate it. The record it is in must be linked in someway to the order? Use that logic to obtain the date.
 
Hi ChrisMore,

You are welcome.

I am learner and trying to help up to my level of knowledge, but unfortunately, that codes still above of my knowledge level.

Here is many experts they can help on this matter.
 
I changed the code and managed to get it working.
Code: 
        '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 " & _
            "Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Shipments.Production_Complete_Date " & strDateClause & "));"
        End If

What I would like to do now is have the Order_Shipment_Number auto generated using the Order_Number (both fields are highlighted in the attached screenshot) so it takes the order number and adds the letter suffix for each new record added in the 'Shipment Details' subform (the Shipments table).

I tried using the ASCII idea that gasman had but haven't been able to get it to work. Can anyone help me to get this to work?

Thanks,
Chris
 

Attachments

  • Screenshot.png
    Screenshot.png
    121.9 KB · Views: 244
ChrisMore said:
I changed the code and managed to get it working.
Code: 
        '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 " & _
            "Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSQL = strSQL & ");"
        Else
            strSQL = strSQL & " AND (Shipments.Production_Complete_Date " & strDateClause & "));"
        End If

What I would like to do now is have the Order_Shipment_Number auto generated using the Order_Number (both fields are highlighted in the attached screenshot) so it takes the order number and adds the letter suffix for each new record added in the 'Shipment Details' subform (the Shipments table).

I tried using the ASCII idea that gasman had but haven't been able to get it to work. Can anyone help me to get this to work?

Thanks,
Chris
Click to expand...
You would need to explain in detail what you tried and how your tables are set up, if anyone is going to help you?
In essence you store the suffix number with each OrderShipmentNumber and increment it by 1 and use that number to add to ASCII base 64
 
Gasman said:
You would need to explain in detail what you tried and how your tables are set up, if anyone is going to help you?
In essence you store the suffix number with each OrderShipmentNumber and increment it by 1 and use that number to add to ASCII base 64
Click to expand...
This is what I have written so far
Code: 
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") & Chr(64 + iNextSuffix)
End Sub

I have attached an image of my table relationships. I created a field in the Shipments table called "Delivery_Suffix" as I tried to follow your previous guidance.

Just to clarify, I don't need the customer order number to change, just the letter suffix on the order shipment number. So it takes the customer order number and adds the letter suffix. Then for each new record added to the shipments subform it moves through the alphabet.
 

Attachments

  • Database Relationships Screenshot.png
    Database Relationships Screenshot.png
    116.1 KB · Views: 255
Last edited:
If the ordershipment number is numeric, then you do not use single quotes. Plus you should be able to get away with Me.Order_Number
I only use full form references if I need to?
Set a breakpoint and step through the code with F8. I'm not sure of that event either, but then I have only really used a Forms BeforeUpdate event
 
Gasman said:
If the ordershipment number is numeric, then you do not use single quotes. Plus you should be able to get away with Me.Order_Number
I only use full form references if I need to?
Set a breakpoint and step through the code with F8. I'm not sure of that event either, but then I have only really used a Forms BeforeUpdate event
Click to expand...
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.
 
Walk through the code with F8.
Is the code even running.? Inspect the variables as they get populated.
I *ALWAYS* Dim my variables and you should have Option Explicit at the top of every module.?
The code above looks OK to me, other than iNextSuffix not being declared?, so walk though it with F8.
txtDeliveryCode should likely be Me.txtDeliveryCode ?
 
Gasman said:
Walk through the code with F8.
Is the code even running.? Inspect the variables as they get populated.
I *ALWAYS* Dim my variables and you should have Option Explicit at the top of every module.?
The code above looks OK to me, other than iNextSuffix not being declared?, so walk though it with F8.
txtDeliveryCode should likely be Me.txtDeliveryCode ?
Click to expand...
I have been able to get this code to run:
Code: 
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)
End Sub

However, what it is doing is only adding the suffix into the Order_Shipment_Number field, so it's not including the order number from the Order_Number field as the prefix.

It also returned 0 in the delivery suffix number so it returns A in the Order_Shipment_Number field. In my example I already have four records so it should at least return E in the Order_Shipment_Number field when the new record is added.
 
Last edited:
what result do you expect?
Code: 
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 = Me.Order_Number & Format(Me.Order_Shipment_Number, "00000") & Chr(64 + iNextSuffix)
End Sub
 
arnelgp said:
what result do you expect?
Code: 
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 = Me.Order_Number & Format(Me.Order_Shipment_Number, "00000") & Chr(64 + iNextSuffix)
End Sub
Click to expand...
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 is used as the suffix to the Order_Number to become the Order_Shipment_Number.

For example:
Order_Number = 10000
First record added to the Shipments table -> Delivery_Suffix = 1
Order_Shipment_Number = 10000A
Second record added to the Shipments table -> Delivery_Suffix = 2
Order_Shipment_Number = 10000B

I hope that makes sense.
 
Last edited:
can you try this:
Code: 
Private Sub Form_BeforeInsert(Cancel As Integer)
iNextSuffix = DCount("1", "Shipments", "Order_Shipment_Number='" & Me.Order_Number & "'") + 1
Me.Order_Shipment_Number = Format(Me.Order_Number, "00000") & Chr(64 + iNextSuffix)
End Sub
 
arnelgp said:
can you try this:
Code: 
Private Sub Form_BeforeInsert(Cancel As Integer)
iNextSuffix = DCount("1", "Shipments", "Order_Shipment_Number='" & Me.Order_Number & "'") + 1
Me.Order_Shipment_Number = Format(Me.Order_Number, "00000") & Chr(64 + iNextSuffix)
End Sub
Click to expand...
That returned B in the Order_Shipment_Number field and 0 in the Delivery_Suffix field
 
what table is the code for?
are we in a subform?
 
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.?
 
You must log in or register to reply here.

Similar threads

J
Im an absolute beginner. Please help
Replies
5
Views
684
Steve R.
Steve R.
W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,099
Ken Sheridan
Ken Sheridan
W
The records not sorted by the record ID field in ascending order in a continuous form after adding new records
Replies
14
Views
1,178
wendywu
W
asteropi
Solved Report/Query won't print my results
Replies
13
Views
415
Gasman
Gasman
T
2 queries in one form
2
Replies
30
Views
2,896
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom