DMAX Coding Help

[Brook]

Good Day all,

I need some help getting started using the DMAX function to assign
sequential number to my orders. What I have is this, my orders table with
ordernumber, ordertype, and orderdetails. My ordertype uses a lookup function
for CustomOrder, ProgramOrder, and SampleOrder.

What I need to do is create my form so that when I select my Ordertype from
my combobox, the code adds "1" to the last / largest order number there is.

Can anyone point me in the right direction, give me any pointers?

If you need more information please feel free to ask or email me at :

brook at karmaimports dot net

thanks

Brook

 

[John Vinson]

Good Day all,

I need some help getting started using the DMAX function to assign
sequential number to my orders. What I have is this, my orders table with
ordernumber, ordertype, and orderdetails. My ordertype uses a lookup function
for CustomOrder, ProgramOrder, and SampleOrder.

What I need to do is create my form so that when I select my Ordertype from
my combobox, the code adds "1" to the last / largest order number there is.

You'll need a little VBA code in the OrderType combo's AfterUpdate event:
something like

Private Sub comboboxname_AfterUpdate()
If IsNull(Me!OrderNumber) Then ' don't stomp on existing number
Me!OrderNumber = NZ(DMax("[OrderNumber]", "[tablename]", "<criteria>")) + 1
End If
End Sub

I'm not certain what set of records in the table should determine the order
number, so you'll need to fill in the criteria yourself.

John W. Vinson/MVP
Me!Order

 

[Brook]

JV,

Thank you very much for the info/help!

I"m not exactly sure what you mean by "criteria" but I want each of
my "ordertypes" (ie. custom,program & sample) to have independent
ordernumbers. So I guess my criteria would be my "criteria" would be my
ordertype... correct?

Thanks again..

Brook

Good Day all,

I need some help getting started using the DMAX function to assign
sequential number to my orders. What I have is this, my orders table with
ordernumber, ordertype, and orderdetails. My ordertype uses a lookup function
for CustomOrder, ProgramOrder, and SampleOrder.

What I need to do is create my form so that when I select my Ordertype from
my combobox, the code adds "1" to the last / largest order number there is.

You'll need a little VBA code in the OrderType combo's AfterUpdate event:
something like

Private Sub comboboxname_AfterUpdate()
If IsNull(Me!OrderNumber) Then ' don't stomp on existing number
Me!OrderNumber = NZ(DMax("[OrderNumber]", "[tablename]", "<criteria>")) + 1
End If
End Sub

I'm not certain what set of records in the table should determine the order
number, so you'll need to fill in the criteria yourself.

John W. Vinson/MVP
Me!Order

 

[John Vinson]

JV,

Thank you very much for the info/help!

I"m not exactly sure what you mean by "criteria" but I want each of
my "ordertypes" (ie. custom,program & sample) to have independent
ordernumbers. So I guess my criteria would be my "criteria" would be my
ordertype... correct?

Yes:

"[Ordertype] = '" & [Ordertype] & "'"

assuming that Ordertype is a text field. Leave off the ' before and
the # "'" after if ordertype is numeric.

John W. Vinson[MVP]

 

[Brook]

JV,

Thanks again for the info,

The "ordertype" comes from my ordertype combobox, will that matter on
how I set up the code?

Thanks,

Brook

JV,

Thank you very much for the info/help!

I"m not exactly sure what you mean by "criteria" but I want each of
my "ordertypes" (ie. custom,program & sample) to have independent
ordernumbers. So I guess my criteria would be my "criteria" would be my
ordertype... correct?

Yes:

"[Ordertype] = '" & [Ordertype] & "'"

assuming that Ordertype is a text field. Leave off the ' before and
the # "'" after if ordertype is numeric.

John W. Vinson[MVP]

 

[John Vinson]

JV,

Thanks again for the info,

The "ordertype" comes from my ordertype combobox, will that matter on
how I set up the code?

The value of the ordertype that you need is the actual datatype of the
field stored in your table. I don't know what that is. If the combo
box is a (yuck, PTOOIE!) Lookup Wizard field in your table, the actual
contents and the actual datatype of the field are *concealed from your
view* by the Lookup misfeature. If you did use the lookup wizard it's
probably a Long Integer linked to the autonumber primary key of the
Ordertype lookup table.


John W. Vinson[MVP]

 

[Brook]

I did use the "lookup" wizard for my combo box. Is there a better way to set
up a combobox without using a "lookup" function to another table? If so, I am
game for new ideas!

Brook

 

[John Vinson]

I did use the "lookup" wizard for my combo box. Is there a better way to set
up a combobox without using a "lookup" function to another table? If so, I am
game for new ideas!

Well, a combo box is indeed a tool for looking up data in another
table.

However, it is NOT necessary to use the Lookup Wizard to get that
functionality! A Table is designed to store data; I've never liked the
mixing of levels represented by Lookup Fields and Subdatasheets. These
features encourage the use of table datasheets for interaction with
data.

That's ok for quick-and-dirty, very limited use, for those who
understand the relational structure of the data. However, in practice
they cause confusion for people new to databases, by concealing the
actual structure of the data. Your problem is a case in point: you
don't know (because you can't TELL from what you see) what it is that
you want to search for in your table, a number or text!

It's perfectly straightforward to create Combo Box controls on Forms,
and use them to look up data. It's not necessary to have a combo box
in the table to do so. I agree, it saves maybe two mouseclicks when
you're creating the form - but that minor benefit is (IMO) outweighed
by the confusion and inefficiency that having the combo boxes in the
table itself causes.

John W. Vinson[MVP]

 

[Brook]

So, are you saying that I should only use the "lookup" function within my
form and then store/send the combobox value to my table? From there I would
use an if statement with a DMAX to create my sequential number for each order
type.

Thanks,

Brook

 

[John Vinson]

So, are you saying that I should only use the "lookup" function within my
form and then store/send the combobox value to my table? From there I would
use an if statement with a DMAX to create my sequential number for each order
type.

Sounds like a good plan to me.

John W. Vinson[MVP]