Creating an Orders Form with multiple order types

[Guest]

Good day,

What I have is this: tblorders (which houses all my orders - Custom (CST),
Stock (STK), and Sample (SPL). within the tblorders I have an "orderstype"
and "ordernumber".

What I am trying to do is this: create either a Combo box or an option
group on my form, so that when I choose the appropriate order type, that type
code is saved in the "ordertype" field in my table, and the "ordernumber" for
that type is increased / incremented by 1.

Any Ideas / suggestions?

thanks,

Brook

 

[JP]

If you really want the order numbers to be separate by order type (i.e., the
same order number can appear across order types but not within order types)
then you cannot use an autonumber field.

In that case, put an order type combo box or option group on your form. In
the Before Update event for the form, put in code that does a DMAX on the
order number field for that order type and then increment the result by 1 to
make it the order number of the order being entered.

You of course have to test to make sure that the user selected the order
type before you can run the DMAX. You also have to have some code to test
for a null result from the DMAX, which is what you'll get when entering the
very first record for each order type. And, you'll have to have some code
to make sure that you only try to set the order number on new records (set a
flag if me.newrecord is true in the On Current Event). The order number
field could not be the primary key because it is not unique (presumably the
combination of the order type and order number could be the primary key).

A lot of work.

Or, you can simply use an autonumber field and make the order number unique
across all order types. I would argue that's a better approach because this
way you can never have any confusion caused by two different orders with the
same order number (even if they are different order types).

 

[Guest]

JP,

Thank you for the information, and I understand that I cannot use the
autonumber function, but I need sequential number for each type of order. I
have the ComboBox for the OrderTypes. I am going to have to do some research
the DMAX function. If you know if any samples that I can look at please let
me know.

Also you mention that I need to make sure there is no "Null" value... what
if I set my initial value to "0" or "1", then there wouldn't /couldn't be a
"null" value. Correct?


Thanks,

Brook

 

[JP]

Also you mention that I need to make sure there is no "Null" value...
what

if I set my initial value to "0" or "1", then there wouldn't /couldn't be a
"null" value. Correct?

No, not correct.

The very first time for each order type, there won't be ANY records out
there at all. This will cause the DMax to return a null.

 

[Guest]

Thanks again for the info,

This will definately get me going, I just need to learn more about the DMAX
function.

One last question, to avoid the "Null" value, would it be possible for me
to manually enter my first record, or import my records from another table or
excel?

Thanks,

Brook

 

[Guest]

Try the Nz function before the DMax function. Something like the following
should work, although I have not tested it and am not exactly sure about the
criteria part of the DMax expression (the part after "Table Name" and before
the next parentheses). cboOrderType is the name of the combo box, and of
course substitute your actual form name for YourForm.

Nz(DMax("FieldName","TableName","[OrderType] =" &
Forms!YourForm!cboOrderType),0)+1