Link Master/Child without using Master autonumber

[miche7]

Hi there - -

Just for some background, the database is for a conversion from Microsoft Works where every customer & order is one record even if the customer has previously placed orders. In this Access database, there is one Customer record with all their orders. (Well, actually there are about 26,500 customers with about 72,500 orders.)

When a new Customer is added, I need to assign it a number – called CustKey - so that I can create a link to their Orders records. I am unable to use the Customer autonumber field because it has created conflicts when using the autonumber fields of both the Customer and Orders in other functions of the Access database.

It was easy to convert the existing Customers and assign them CustKey, and include this to their Orders records. I just need now to be able to add new Customers and assign them the next number up.

In one attempt, I created a table - LastCustKey - with one record in it – the last number assigned to the last Customer. But don’t really know how to retrieve it when a new Customer is added.

So, I guess I’m just looking for some guidance on how to approach this.

Thanks for any suggestions.

miche7

 

[KenReay]

Hi

First of all I do not undestand why you say you cannot use Autonumber, it will do the job admirably

Does the database operate in a multi user environment?, if no you can get the next number with simple code, but if yes, you need a more robust mechanism to prevent duplicates, there are numerous posts on this site on this subject, but if you are operating in a mulituser environment beware of all of the one liners using DMax(), DLookup) etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[miche7]

Thanks for your guidance Ken. Your mention of "next number" helped greatly with the searches, rather than my "link master/child" approach. Mostly, I found code that seems more than I need, but will give it a try. At least I had the right idea to create a table with the last number used. Glad I found that out.

Since I’m a Mac user at home, I only work on this Access database for a couple of hours on Wednesday afternoons on site at the client’s single-user stand-alone p.c. - – not even connected to the Internet.

Anyway, I fully realize that under certain circumstances the autonumber field would do the job. BUT, I couldn’t use autonumber because I need to use the OrdersID autonumber field to retrieve that specific Order. In the query, with both the Customer and Orders tables – when CustID autonumber was the link - the query indicated that I cannot use two autonumber fields in one query. Sooooooo, it became necessary to create the CustKey unique number field as a link.

Thanks for your reply,

L. Michele Hyland
Software Consultation & Design

http://www.geocities.com/lmh7

 

[KenReay]

Hi

I see what you are saying, but it is a missunderstanding of the use of autonumber.

In the Customer table you would have:

CustId Autonumber
CustName text
CustAddress Text
..etc

in the Orders table

OrderId Autonumber
ONo String
CustId Long
OrderDate DateTime
...etc

Note that in the Customer table CustId is an autonumber, but in the Orders Table (where CustId is a foreign key) it is a long. Autonumber is simply a special kind of long, which Access automatically increments according to a rule (eg increment or random).

There are various mechanisms to populate the foriegn key column of a related table using sub forms for example, or @@identity in SQL

Hope this helps for the future

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK