orders form... using DMAX to assign new order number

[Guest]

Good Day,

I have have a form (frmneworders) that is based on my tblorders. The fields
are ordernumber, ordertype(based on cboordertype) for Custom, Program, or
Sample, and orderdetails.

What I want to set up is this: When the user selects a new order, they will
choose the ordertype form the cboordertype, and after that ordertype is
selected I need to know how to create the code to lookup the DMAX for the
ordernumber and add 1 too it, to create a sequential ordering system for each
type of order.

If anyone has any ideas... tips please pass them along...

Thanks in advance..

Brook

 

[Dan Artuso]

Hi,
Something like this maybe?

dim lngOrderNumber as Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" & Me.cboordertype & "'") + 1

 

[Guest]

Thank you very much Dan,

I am going to try this and see how it goes... I will post a response one
way or another.

Brook

Hi,
Something like this maybe?

dim lngOrderNumber as Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" & Me.cboordertype & "'") + 1

--
HTH
-------
Dan Artuso, MVP

Good Day,

I have have a form (frmneworders) that is based on my tblorders. The fields
are ordernumber, ordertype(based on cboordertype) for Custom, Program, or
Sample, and orderdetails.

What I want to set up is this: When the user selects a new order, they will
choose the ordertype form the cboordertype, and after that ordertype is
selected I need to know how to create the code to lookup the DMAX for the
ordernumber and add 1 too it, to create a sequential ordering system for each
type of order.

If anyone has any ideas... tips please pass them along...

Thanks in advance..

Brook

 

[Rick Brandt]

Thank you very much Dan,

I am going to try this and see how it goes... I will post a response one
way or another.

If you are going to have multiple users creating records at the same time you
will need to use a strategy that saves the record immediately after assigning a
number. I use the BeforeUpdate event as it can assign the number a split second
before the record is saved making collisions unlikely.

 

[Guest]

hello... I have tried your suggested code, but was unable to get it to work
the way that I would like..

Should my ordernumber in my tblorders be an "Autonumber"?

Do I need to add the code to the afterupdate?

Thanks,

Brook

Thank you very much Dan,

I am going to try this and see how it goes... I will post a response one
way or another.

Brook

Hi,
Something like this maybe?

dim lngOrderNumber as Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" & Me.cboordertype & "'") + 1

--
HTH
-------
Dan Artuso, MVP

Good Day,

I have have a form (frmneworders) that is based on my tblorders. The fields
are ordernumber, ordertype(based on cboordertype) for Custom, Program, or
Sample, and orderdetails.

What I want to set up is this: When the user selects a new order, they will
choose the ordertype form the cboordertype, and after that ordertype is
selected I need to know how to create the code to lookup the DMAX for the
ordernumber and add 1 too it, to create a sequential ordering system for each
type of order.

If anyone has any ideas... tips please pass them along...

Thanks in advance..

Brook

 

[Guest]

thanks for the input...

What type of code would you suggest for the Beforeupdate event?

Thanks,

Brook

 

[Rick Brandt]

thanks for the input...

What type of code would you suggest for the Beforeupdate event?

Assuming the filed in numeri and named [ID]...

If Nz(Me.ID, 0) = 0 Then
Me.ID = Nz(DMax("ID", "TableName"), 0) + 1
End If

The If-Then block is necessary because BeforeUpdate can fire many times on a
single record and you only want to assign the ID if it has not already been
done.

The Nz() is only necessary for the very first record entered.

 

[Dan Artuso]

Hi,
From the info in your post, I would say to put the code in the cboordertype AfterUpdate event.
No, it should definitely not be an autonumber.

I can only guess at how you want it to work, so, post more info.
Did the code fail? If so, what line?

I'm also assuming that each ordertype is treated seperately as far as order numbers go.
Is that correct?

The code I gave you places the new number in a variable, you have to do something with that.

--
HTH
-------
Dan Artuso, MVP

hello... I have tried your suggested code, but was unable to get it to work
the way that I would like..

Should my ordernumber in my tblorders be an "Autonumber"?

Do I need to add the code to the afterupdate?

Thanks,

Brook

Thank you very much Dan,

I am going to try this and see how it goes... I will post a response one
way or another.

Brook

Hi,
Something like this maybe?

dim lngOrderNumber as Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" & Me.cboordertype & "'") + 1

--
HTH
-------
Dan Artuso, MVP


Good Day,

I have have a form (frmneworders) that is based on my tblorders. The fields
are ordernumber, ordertype(based on cboordertype) for Custom, Program, or
Sample, and orderdetails.

What I want to set up is this: When the user selects a new order, they will
choose the ordertype form the cboordertype, and after that ordertype is
selected I need to know how to create the code to lookup the DMAX for the
ordernumber and add 1 too it, to create a sequential ordering system for each
type of order.

If anyone has any ideas... tips please pass them along...

Thanks in advance..

Brook

 

[Guest]

Thank you,

The only thing that I don't see in your code provided, is the fact that my
tblorders will contain 3 types of orders (custom, program and stock), based
on the in information provided in a cbordertype on my form.

Once the user selects the order type from the drop down box, it is saved
to the table under ordertype, then the ordernumber is assigned. Each of the
ordertypes are too have sequential ordernumbers.

Brook

thanks for the input...

What type of code would you suggest for the Beforeupdate event?

Assuming the filed in numeri and named [ID]...

If Nz(Me.ID, 0) = 0 Then
Me.ID = Nz(DMax("ID", "TableName"), 0) + 1
End If

The If-Then block is necessary because BeforeUpdate can fire many times on a
single record and you only want to assign the ID if it has not already been
done.

The Nz() is only necessary for the very first record entered.

 

[Guest]

Good Day Dan,

Ok, I am going to give you exactly what I have in my test database:

tblorders
fields: ordernumber --> Datatype - number, Long Integer, decimal
places 0, default value 0, required - no , indexed -- no.
ordertype --> text, size 50, required - no, allowzero
length no, indexed no, unicode comp. - no,


frmorders: with fields:
ordernumber
ordertype --
cboordertype - Values "Custom", "Program", and "Stock" -- when the
user selects the order type, it is then saved to the field ordertype

cboordertype -- > code:

Code begin:
Private Sub cboordertype_AfterUpdate()

Dim lngOrderNumber As Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" &
Me.cboordertype & "'") + 1


End Sub
Code End:

When I choose my option under my cboordertype, I am getting a runtime error
94, Invalid use of Null.

then when I click debug:
the following code is highlighted:

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" &
Me.cboordertype & "'") + 1

Thanks,

Brook

Hi,
From the info in your post, I would say to put the code in the cboordertype AfterUpdate event.
No, it should definitely not be an autonumber.

I can only guess at how you want it to work, so, post more info.
Did the code fail? If so, what line?

I'm also assuming that each ordertype is treated seperately as far as order numbers go.
Is that correct?

The code I gave you places the new number in a variable, you have to do something with that.

--
HTH
-------
Dan Artuso, MVP

hello... I have tried your suggested code, but was unable to get it to work
the way that I would like..

Should my ordernumber in my tblorders be an "Autonumber"?

Do I need to add the code to the afterupdate?

Thanks,

Brook

Thank you very much Dan,

I am going to try this and see how it goes... I will post a response one
way or another.

Brook

:

Hi,
Something like this maybe?

dim lngOrderNumber as Long

lngOrderNumber = DMax("[ordernumber]", "tblorders", "ordertype = '" & Me.cboordertype & "'") + 1

--
HTH
-------
Dan Artuso, MVP


Good Day,

I have have a form (frmneworders) that is based on my tblorders. The fields
are ordernumber, ordertype(based on cboordertype) for Custom, Program, or
Sample, and orderdetails.

What I want to set up is this: When the user selects a new order, they will
choose the ordertype form the cboordertype, and after that ordertype is
selected I need to know how to create the code to lookup the DMAX for the
ordernumber and add 1 too it, to create a sequential ordering system for each
type of order.

If anyone has any ideas... tips please pass them along...

Thanks in advance..

Brook

 

[Rick Brandt]

Thank you,

The only thing that I don't see in your code provided, is the fact
that my tblorders will contain 3 types of orders (custom, program and
stock), based on the in information provided in a cbordertype on my
form.

Once the user selects the order type from the drop down box, it is
saved to the table under ordertype, then the ordernumber is assigned.
Each of the ordertypes are too have sequential ordernumbers.

The DMax can include a third argument that is a valid SQL WHERE clause without
the word "WHERE". Just include that argument to filter on records with the same
type as was selected on the current record.

If Nz(Me.ID, 0) = 0 Then
Me.ID = Nz(DMax("ID", "TableName", "Type = '" & Me.Type & "'"), 0) + 1
End If

 

[Guest]

Hello Rick,

Thank you very much for the help! that worked perfectly...



Brook

 

[Guest]

Hello Rick...

I have just another question for you... I added your code on an
"Afterupdate" event... you mentioned a beforeupdate event for the code... I
tried that and it didn't work the way the afterupdate code event worked...

would you still suggest the beforeupdate code event?

Thanks,

Brook

 

[Rick Brandt]

Hello Rick...

I have just another question for you... I added your code on an
"Afterupdate" event... you mentioned a beforeupdate event for the
code... I tried that and it didn't work the way the afterupdate code
event worked...

would you still suggest the beforeupdate code event?

The AfterUpdate of your ComboBox might work ok if the same event also saves the
record. Otherwise you will have problems in multi-user situations. If the user
typically makes this selection near the beginning of record creation, then it
might not be possible to issue a save (required fields still not filled in) or
at least not desirable to do so.

Scenario...

UserA makes his selection in the ComboBox and then his phone rings. He spends
an indefinite period of time on the phone before finishing the record and saving
it. Any other user that selects the same type during that interval will get the
same number as UserA and whichever one saves last will get an error.

I was speaking of the BeforeUpdate event of the form. That is the only event
where the record is always saved immediately afterwards (providing it isn't
cancelled).