Sequential Order ID on Form (2 Viewers)

[John Big Booty]

This should do it;

    Dim strOrdNum As String
    
    If Me.OrderNum = 0 Or IsNull(Me.OrderNum) Then 'check if this record already holds and order number
        strOrdNum = Nz(DMax("OrderNum", "Table1"), 1)
        Me.OrderNum = "ORD" & Right(strOrdNum, Len(strOrdNum) - 3) + 1
    End If

 

[charya]

This should do it;

    Dim strOrdNum As String
    
    If Me.OrderNum = 0 Or IsNull(Me.OrderNum) Then 'check if this record already holds and order number
        strOrdNum = Nz(DMax("OrderNum", "Table1"), 1)
        Me.OrderNum = "ORD" & Right(strOrdNum, Len(strOrdNum) - 3) + 1
    End If

I changed the instances of "OrderNum" to orderid and "Table1" to "tblOrder".

"Private Sub Form_Current()
Dim strOrdNum As String

If Me.orderid = 0 Or IsNull(Me.orderid) Then 'check if this record already holds and order number
strOrdNum = Nz(DMax("orderid", "tblOrder"), 1)
Me.orderid = "ORD" & Right(strOrdNum, Len(strOrdNum) - 3) + 1
End If
End Sub"

Running the form gave me this: Compile error
Method or data member not found

 

[vbaInet]

I would put this in the Default Value property of the textbox instead:

Nz(DMax("OrderNum", "TBL_Orders"), 0) + 1

I only changed the position of + 1 from John's code so you don't need the IIF() check.

Also, I will reiterate that you must not save the ORD part as you've already been told. Put this in the Format property of the field in your table:

"ORD"#

It will show exactly as you want it ORD123 etc. This way the field remains as a number and it will be easier to increment.

The other way is to simply set the field's datatype as Auto Number and format it as above.

 

[John Big Booty]

You are going to need to change the first line of my code to reflect the fact that instead of dealing with a numeric value, you are dealing with a string, but I'll leave that up to you so you can earn your pass

 

[charya]

I would put this in the Default Value property of the textbox instead:

Nz(DMax("OrderNum", "TBL_Orders"), 0) + 1

I only changed the position of + 1 from John's code so you don't need the IIF() check.

Also, I will reiterate that you must not save the ORD part as you've already been told. Put this in the Format property of the field in your table:

"ORD"#

It will show exactly as you want it ORD123 etc. This way the field remains as a number and it will be easier to increment.

The other way is to simply set the field's datatype as Auto Number and format it as above.

Tried the "ORD"# but it didnt work. See ive got some dummy data im using. I tried putting the field as a Number and set Format to what yo said above..but then when importing it didnt let me. Nor did it let me add a new order with ORDXXX.

You are going to need to change the first line of my code to reflect the fact that instead of dealing with a numeric value, you are dealing with a string, but I'll leave that up to you so you can earn your pass

No! I cant actually do this! I have no idea about code..

P.S: Been doing this for over 4 hours now

 

[vbaInet]

Tried the "ORD"# but it didnt work. See ive got some dummy data im using. I tried putting the field as a Number and set Format to what yo said above..but then when importing it didnt let me. Nor did it let me add a new order with ORDXXX.

Importing from where? In the import file does it have ORD?

It seems you're being forced to save ORD as part of the ID. But we need this confirmed??

In any case, it is still possible to get the number part of ID (if "ORD" was saved) using Mid() within the function already given.

 

[charya]

Importing from where? In the import file does it have ORD?

It seems you're being forced to save ORD as part of the ID. But we need this confirmed??

In any case, it is still possible to get the number part of ID (if "ORD" was saved) using Mid() within the function already given.

Yep the import file does have ORD..and I have to store it as ORD sa well.

 

[John Big Booty]

I changed the instances of "OrderNum" to orderid and "Table1" to "tblOrder".

"Private Sub Form_Current()
Dim strOrdNum As String

If Me.orderid = 0 Or IsNull(Me.orderid) Then 'check if this record already holds and order number
strOrdNum = Nz(DMax("orderid", "tblOrder"), 1)
Me.orderid = "ORD" & Right(strOrdNum, Len(strOrdNum) - 3) + 1
End If
End Sub"

Running the form gave me this: Compile error
Method or data member not found

At what point in the code?

Also when you post code it would make it a whole bunch easier to read if you wrapped it in code tags. You'll get those if you click the cross hatch button second from the right bottom row of the buttons above the compose window.