Solved Autonumber Advice

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:
Gasman said:
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
Click to expand...
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
 
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??????
 
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: 176
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: 183
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
 
You must log in or register to reply here.

Similar threads

B
Update query doesn't like value formatted for a different region.
2
Replies
21
Views
617
Josef P.
J
G
Relationships and Tables conundrums
Replies
9
Views
479
MajP
MajP
B
Advice Request : Simplest method to allow multi-site access to a prototype database
Replies
10
Views
351
Pat Hartman
P
H
Solved Query for the balance stock material
Replies
8
Views
369
GPGeorge
GPGeorge
B
Code a date one week from another date
Replies
6
Views
267
Mark_
M

Users who are viewing this thread

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