My primary key can't be an auto-number - please help! 1

[Tempest]

The database I'm working on will store information about our customers, the work orders that contain each job's processes, and eventually estimating and invoicing. Since the same customer has multiple jobs, the work order has to have it's own unique identifier. I can't use an auto-number field because the job ticket (work order) table relates to the job processes table which holds the individual processes for each job ticket. I don't know of any way to make it easy for the user to either come up with a unique job number or automatically generate that information. This will be used in a multiple users on four workstations. I'm so close to having a great tool to help the company I work for. I hope someone can help me, and I thank any one in advance who tries! <br>
<br>

 

[Guest_imported]

I am sorry, I am a little slow. You said you can't use an autonumber because the job ticket tables related to the job processes table, which holds (Etc.), but I still don't see how this prevents you from using an autonumber. I can think of numerous ways to come up with a unique identifier without resorting to autnumbering, but I am not sure how these will work in your application, as I am not sure what the problem is with the autnumber method. <p>John Vogel<br><a href=mailto:john@computerwiz.net>john@computerwiz.net</a><br><a href=

http://www.deja.com/~computerwiz>Deja.com

Computerwiz Community</a><br>---==============================---<br>
I am currently working on

http://www.computerwiz.net.<br>

It should be LIVE 1/12/00 or before. I would appreciate suggestions, comments and the like. Please go look and help. Thanks.<br>
---========================

 

[DougP]

I had the same problem once and wrote this routine to get around it.<br>
It keeps track of an Invoice number for you but allows the field to be Non-Primary.<br>
You can Index the field though which will speed up searches later by going to design view and setting the Indexed property to Yes(Duplicates OK) for the Invoice number field.<br>
Essentially you are storing the last Invoice or RA-Number in my case in a separate.<br>
<br>
'open table and get the last RA number used<br>
Dim MyDB As Database, MySet As Recordset<br>
Set MyDB = DBEngine.Workspaces(0).Databases(0)<br>
Set MySet = MyDB.OpenRecordset(&quot;Last-RA-Number&quot<br>
MySet.MoveFirst<br>
MySet.Edit<br>
Temp1 = MySet.Fields(&quot;Last_RA_Number&quot<br>
Temp2 = Temp1 + 1<br>
MySet.Fields(&quot;Last_RA_Number&quot = Temp2<br>
MySet.Update<br>
MySet.Close<br>
MyDB.Close<br>
<br>
<br>
I frequently make an AddNew button in which I do several things like put in today's date, Increment the next Invoice Number etc.<br>
Here's a date example<br>
Me!Date = format(now, &quot;mm/dd/yy&quot<br>
<br>
DougP