CrossPost: 2 Order Types 1 Table

[Guest]

Good Day,

I am trying to create a table that will hold my master inventory, however I
have two types of orders: Custom and Stock. For my custom orders I would like
to have a custom order number KI-0000 (then incremental with each new order..
KI-0001, KI-0002, etc), and Stock Orders NW-0001 (with incremental numbering
same as above.

I am unsure how to incorporate the two types of orders into one table, does
anyone have any ideas? Tips? tricks that they could share?

 

[Nikos Yannacopoulos]

Brook,

Assuming orders are entered through a form:

I would use an option group on the form to select type of order (stock
or custom), and use its On Change event to auto-populate the order
number with a couple of simple lines of code like:

If Me.Frame0 = 1 Then
otyp = "KI-"
Else
otyp = "NW-"
End If
lst = DMax("[OrderID]","tblOrders","Left([OrderID],3) = '" & otyp & "'"
lstnum = Val(right(lst,4))
newID = otyp & Format(lstnum + 1, "0000")
Me.ctlOrderID = newID

Where I have assumed the following:

The option group is called Frame0 nad returns 1 for Custom, 2 for Stock;
The oders table is named tblOrders, and the order number field in it is
named OrderID;
The control on the form bound to the OrderID field is named ctlOrderID.

Change the names in the code as required, to match the actual names in
your design.

HTH,
Nikos