Need help with my design.

X

xenos132

Registered User.
Local time
Yesterday, 20:55
Joined
Jan 9, 2005
Messages
15
I am trying to automate our work order system and incorporate into my data base. Just having a bit of trouble with the design.

I have 4 different system for work orders depending on their use, each has a distinct beginning. Construction for example is BCx , Maintence is Mx, service is SJx…

I am thinking of using 4 tables to generate my numbers by using “leter”000 in the format field of the auto number. So table one would generate, “BC”001… table 2 would generate “M”001… and so on.

Now the problem. I want to have one table called work orders. I am thinking of having a combo box where the items listed are the 4 table names where I am generating the numbers from. I am also thinking when I select one of these it will give me the next available number and I will use this as my work order number. I just can’t figure out how to put this together.

Anyone have a suggestion.

Thanks
 
All your workorders should be in a single table. You should use two separate fields. One for type and the second for sequence number. I would simply use an autonumber as the pk and use that as the sequence number. When you print workorders, you can suffix them with the type field-
WOType & Format(WOID,"0000"). Make the autonumber the primary key.

When creating new WO's, use a combo to choose the type field. Access will automatically generate the autonumber.

If you absolutely insist on generating your own numbers, then you should still use two fields and make a compound primary key. To do that, select the first field and then while holding the cntl key select the second. Press the key icon on the toolbar.

To generate the sequence number use the DMax() function -

WOSeqNum = Nz(DMax("WOSeqNum", "YourWOTable", "WOType = '" & Me.WOType & "'"), 0) + 1
 
The idea of the 4 tables are just for generating the numbers, these numbers will be stored in one table called TblWorkOrders.

Right now I have it all in the same table like you mention. The problem is the construction manager requires his work orders in sequential order. Right now I use the primary key in my work order table for all the work orders, today if the construction manager asks for a construction WO it will be 1000 then if he asks for one tomorrow it could be 1005. (if some one generated other work orders for different jobs like service). By generating the numbers in separate tables I could issue him BC1000 today then tomorrow BC1001…

The separate tables are not for storing data just generating the next work order number. That’s if it’s possible. I was actually thinking the only data in each table would be the Primary Key (BCx) and a column to store the id number from the work order table that was generated when the new record was made.
 
The expression I included in my previous post generates a separate set of numbers for EACH different value of type so it does EXACTLY what you asked for.
 
Pat, I’m feeling stupid now, I don’t know where to use it.

In my table I added a new column called “WOSeqNum”, on my form I made a text box with the same name. In the box control source I used

“= Nz(DMax("WOSeqNum", " TblPO_numbers ", "Type = '" & Me. Type & "'"), 0) + 1”


And I get and error. Lend another tip :(.

Thanks.
 
Pat, I’m getting close. I have made a command button and in the VB editor I have.

Private Sub Command156_Click()
On Error GoTo Err_Command156_Click

WOSeqNum = Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & "Me. Type" & "'"), 0) + 1


Now I am returning the number 1 to my WOSeqNum column. So my question is am I on the right track and does it matter that my Type field on my table is text?



P.S. it sucks to be a Nube but i'm trying....
 
Pat if i rewrite it to.

WOSeqNum = Me.Type & (Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & Me.Type & "'"), 0) + 1)


I will get B1, M1... however on the next run I get a "type mismatch" error.


Can I not use letters in the WWOSeqNum with Dmax ?
 
Can I not use letters in the WWOSeqNum with Dmax ?
Click to expand...

Yes, you can't.
You'll have to rewrite your code so it retrieves the highest integer part of your WOSeq for a specific type.

RV
 
Cross Posting

Xenos, it is considered even more helpful that if you are cross posting your questions across different sites that you make the people helping you aware that you have posted the question elsewhere.

I am referring to your post on Tek Tips.

By not declaring this you have people on two different sites trying to work out a solution for you oblivious to those helping on the other site. And when the solution is found on one site there are others wasting their time on the other. Please consider this in future.
 
Thank you for pointing that out. I have decided to use Pat’s original code. I have just added a new column called WoNum and use

WoNum = [Type] & "" & [WOSeqNum] to get the number I need.
 
xenos132 said:
WoNum = [Type] & "" & [WOSeqNum]
Click to expand...

There's no need for the "" in that expression as it is redundant.

WoNum = [Type] & [WOSeqNum]

This does the exact same without unnecessary null strings.
 
My god you guys rock. One day, Yes one day I will see.
 
I take it you've figured out where to put the code to generate the sequence number. I take it you've also decided you don't need the sequence number to be fixed length with leading zeros.
 
You must log in or register to reply here.

Similar threads

E
Emmanuel Katto : Best Way to Format Ascending Lot Numbers Based on Ingredient and Date
Replies
6
Views
290
Mark_
M
A
Order entry and tracking system
2 3
Replies
44
Views
1,342
Pat Hartman
P
asteropi
Solved How to format ascending numbers for different inputs?
2
Replies
39
Views
903
asteropi
asteropi
T
Question about sorting
Replies
10
Views
425
Pat Hartman
P
E
Need Help with Combo Box
Replies
4
Views
328
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom