Generate Sales Order Number

[Duck]

I have a database which is used to track inventory. The main table is
an inventory table into which is appended company purchases as well as
sales. Each transaction has an item number which is an autonumber and
primary key for the table. I can't figure out how to generate a
unique sales order number for my sales which might be comprised of
many individual item numbers in the inventory table.

 

[Guest]

Hi Duck,

If I understand you, you want a field in your Table the value of which is
the combination of several ID numbers in that same table. You can do it like
this:

Add a Combo Box to your Form. Set the Record Source as the Table field
where you want the combination of numbers. I will call it SalesOrderNo.

SELECT [YourPKFieldName] & [Item1ID] & [Item2ID] & [Item3ID] AS
SalesOrderNo, YourTableName.YourPKFieldName
FROM YourTableName
WHERE
(((YourTableName.YourPKFieldName)=[Forms]![YourFormName]![YourPKFieldName]));

The WHERE part of the SQL makes only the right number appear in your Combo
Box List to avoid a data entry error.

Place the SQL Statement in the Row Source of the Form's Properties Sheet.
You can save the SQL As a Query if you want and put the Query Name as the Row
Source.

Click the Format Tab on the Form's Properties Sheet.
Make sure the Column Count is set to 2
and the Column Widths are something like 1";1"
and the List Width is set to 2"
and you can set the Column Heads to Yes


To Add Dashes between the numbers you will have to make sure the Table's
SalesOrderNo Field is set to Text. You can add the dashes like this:

SELECT [YourPKFieldName] & "-" & [Item1ID] & "-" & [Item2ID] & "-" &
[Item3ID] AS SalesOrderNo, YourTableName.YourPKFieldName
FROM YourTableName
WHERE
(((YourTableName.YourPKFieldName)=[Forms]![YourFormName]![YourPKFieldName]));

You can also replace the dashes with periods, but then the field will still
have to still be a Text Field.

Please post back and let me know if this is what you need and if it works.
Hunter57
http://easymsaccess.blogspot.com (Simple Access Tips)
http://www.ChurchManageSoftware.com

 

[John W. Vinson]

I can't figure out how to generate a
unique sales order number for my sales which might be comprised of
many individual item numbers in the inventory table.

Ummm...

That's a bad idea, I'd suggest.

Fields should be atomic, having only one value. A sales order number

321-442-482-510-579-823-906

won't do ANYBODY any good.

The items in the sale *are data*, and should be stored in the OrderDetails
table, one record per item. You can always display them from that table when
needed. The sales order number would be much better as simply a sequential
number; probably NOT an autonumber, as these will always have gaps, but just a
Long Integer assigned in code by the form where you enter items.

John W. Vinson [MVP]