Solved Autonumber Advice (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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

Code:
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
 
Last edited:

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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

Code:
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?
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?:

Code:
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

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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??????
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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??????
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

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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
Following on from changing it, the code isn't working now, so it needs to be the format I had originally...
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
Following on from changing it, the code isn't working now, so it needs to be the format I had originally...
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()
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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()
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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.
 

IbrBadri

Member
Local time
Today, 20:19
Joined
May 24, 2020
Messages
35
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.
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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: 124

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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
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
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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
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: 133
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:19
Joined
Sep 21, 2011
Messages
14,046
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 ?
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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 ?
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:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:19
Joined
May 7, 2009
Messages
19,169
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
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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
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:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:19
Joined
May 7, 2009
Messages
19,169
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
 

ChrisMore

Member
Local time
Today, 18:19
Joined
Jan 28, 2020
Messages
174
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
That returned B in the Order_Shipment_Number field and 0 in the Delivery_Suffix field
 

Users who are viewing this thread

Top Bottom