Full Version: Orderids As Purchaseorder Numbers
UtterAccess Forums > Microsoft® Access > Access Forms
MadPiet
I am pretty sure I know the answer to this, but I thought I would ask anyway.

I am working on someone else's database, so it's taken me a bit to figure out what's really going on. (Maybe I'm rusting out between the ears!)

The OrderID is being used as a meaningful number (so it should autoincrement and not skip values etc). On top of that, they want to be able to have revision numbers, so I could have a series of POs like 308, 308A, 308B, 309, 310, 310A etc.
The only way I can think of doing this that makes sense is to use an Autonumber as the identity of the Purchase Order record, and then include the (display) PO# and revision Number. and force the combination of (PO#, Revision#) to be unique.

So I would have to use DMAX etc to get the next "available" PO number, rigtht?

Thanks!
Pieter
dmhzx
Personally On something like that I would write a specific Next Number Function.
And rather than letters as a 'version' I would use a pair of numbers 01,02 etc - starting with 00.

An autonumber is not good for what you want, but can be used as a primary key.
Whichever way you do this you will get gaps if orders are deleted.
I would make the First part (order) numeric, and the version, which would start at "00" as a text field. - If there is any likelihood of more than 99 version, then I'd make it three and start at 000.

The next number routine would have an argument indicating whether to get the next 'real' number or just the next suffix: - Or I may have two different ones: One for number, and another for suffix. With a unique index on the two fields that constitute the 'order number'.
You could use DMAx for either or both parts.
Whichever route you take, you will need to think about how you handle 'deletions'
MadPiet
Thanks, that's what I suspected. The good news is that not many people use the database at the same time, and only one or two have rights to add new records. (How exactly one does that in Access since the security model is getting gutted is another issue... but that's later.)

Thanks!
dmhzx
You ARE going to split this into Front and back ends aren't you?
I just got a bit concerned when you said

QUOTE
not many people use the database at the same time

That you were planning on sharing it all as one database file.
MadPiet
Thanks for reminding me, but no, I have had enough databases corrupt on me to never do that.

Somebody else started this thing, so I'm still trying to figure out what works on this thing and what doesn't. I'll definitely split the DB before handing over the final version. I still have extraneous columns in some of the tables that I need to figure out, so once the tables and relationships are stabilized, I can split.

If I don't want some folks to be able to access some parts, do I have to distribute different front ends for each "type"/group of users?
MadPiet
I think something like this will work... now to figure out how to do it in Access!!! (Would be nice if you could use something more like T-SQL in Access...)

CODE
CREATE TABLE Customer (
    CustomerID    INT IDENTITY(1000,1)
    ,CompanyName VARCHAR(50) NOT NULL
CONSTRAINT pkCustomer PRIMARY KEY (CustomerID),
CONSTRAINT uqCustomerName UNIQUE (CompanyName));
GO

CREATE TABLE OrderHeader (
    internalOrderID INT IDENTITY,
    OrderID INT NOT NULL,
    Alpha CHAR,
    CustomerID INT,
CONSTRAINT uqOrderAlpha UNIQUE (OrderID, Alpha), -- natural PK
PRIMARY KEY (internalOrderID),   -- surrogate PK
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));
GO

CREATE TABLE OrderDetails (
    OrderID INT,
    TestID INT,
CONSTRAINT pkOrderDetails PRIMARY KEY (OrderID, TestID),
CONSTRAINT fkOrderID FOREIGN KEY (OrderID) REFERENCES OrderHeader(internalOrderID));
GO
dmhzx
I never create tables in code.
Where I am, people are allocated laptops, and will always use the same machine.
I use a few half brick techniques - crude but effective under the circumstances.

I use one FE the same one to all, and what they can do, and which screen opens first depends on a particular being in a particular location on that PC.

- actually I have three levels, and two files.
One file gets admin mode
Another gets 'Power user'.
No file is just user.
I monitor logs on and off with a simple check against environ("UserName") and computer name.
Have a simple function that appears to lock the application to new sign ons.

Some folk prefer a custom log in process.
Done that in the past with a simple hierarchy: Which level you are determines what controls are visible (I use the tag property)

One I quite like: In order to prevent accidental changes, the form's on current event locks all the user controls. There is a command button to unlock them, which is only visible to people with change authority.

As for the 'taking over someone else's work; I know what you mean.
I have a couple of those: both of which look like whoever did it came straight out of a training course and wanted to use every technique under the sun, on the basis of "Why use a built in function when an API will do the job" It uses collections all over the place, when a recordset would do the job. It even has a special call to a custom function that turns the hourglass on and off.
nvogel
QUOTE
I think something like this will work... now to figure out how to do it in Access!!! (Would be nice if you could use something more like T-SQL in Access...)

You can do the same in Access! Your code worked for me with one or two very minor changes of syntax.

CODE
CREATE TABLE Customer (
    CustomerID AUTOINCREMENT(1000,1)
    ,CompanyName VARCHAR(50) NOT NULL,
CONSTRAINT pkCustomer PRIMARY KEY (CustomerID),
CONSTRAINT uqCustomerName UNIQUE (CompanyName));

CREATE TABLE OrderHeader (
    internalOrderID AUTOINCREMENT,
    OrderID INT NOT NULL,
    Alpha CHAR NOT NULL,
    CustomerID INT,
CONSTRAINT uqOrderAlpha UNIQUE (OrderID, Alpha),
PRIMARY KEY (internalOrderID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));

CREATE TABLE OrderDetails (
    OrderID INT,
    TestID INT,
CONSTRAINT pkOrderDetails PRIMARY KEY (OrderID, TestID),
CONSTRAINT fkOrderID FOREIGN KEY (OrderID) REFERENCES OrderHeader(internalOrderID));
MadPiet
Thanks for the code amendments. I just find T-SQL much more self-documenting than the Access UI stuff. Sure it's easier to use, but there are some cases when T-SQL definitely comes in handy. that's why I posted it as pseudo Access SQL/T-SQL.

Now to figure out what the end users actually mean in some cases.
ScottGem
There are two Wiki Articles that might help you:

http://www.UtterAccess.com/wiki/index.php/Login_Security


http://www.UtterAccess.com/wiki/index.php/...ntial_Numbering
MadPiet
Oh cool! Super handy!

Thanks, Scott, I'll have a read.

Pieter
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.