Generate an Item number based on a key field.

[vad77]

The database I am building to going to be used to generate Work Orders. I have 2 main tables, tbl_TTA which contains the the work order information such as requestor, due dates assigned tech and so on. The second table is tbl_MAC which contains the work to be performed. The tbl_TTA key field is the work order number and there is a 1 to many relationship to the tbl_MAC.
What I want to do is generate an item number for the items that are added to the work order. So if I create work order 111222 and add two items I want to number them 1 and 2 or 111222-1, 111222-2. The name of the field I'm using to store that information is "Item".
If you need further explanation or information please let me know.

Thank you.

[ranman256]

If you have parent/child forms:
tbl_TTA as master
and tbl_MAC as child.

tbl_MAC SHOULD have its own autonum for a MacID
tbl_MAC will have MacID(as autonumber, always good to have)
and the tbl_MAC.WO (as long, filled automatically via parent/child form)

when the new tbl_MAC rec is created, it can generate the new ITEM# via code:

count the # records for THAT tbl_TTA.WO
then either start from 1 (if null) or add +1 if this ID exists:

btnAddNewOrder_click()
dim vWO, vRet, vNum, vItem


vWO = forms!ParentForm!WO            'get the parent work order#    
vRet = Dcount("*","tbl_MAC","[WO]=" & vWO)   'count the # of records in tbl_Mac with this WO

DoCmd.GoToRecord , , acNewRec

if vRet=0  then
   vItem = vWO & "-01"
else
   vNum = vRet + 1 
   vItem = vWO & "-" & format(vNum,"00")
endif

txtItemNum = vItem            'fill in new order# in the textbox
end sub

[vad77]

tbl_MAC does have an autonumber field which is currently the key field. If you mean Form/Subform with the term Parent/Child then yes I do have the main form built using tbl_TTA and subform using tbl_MAC.
I will build this code, it will be in two places though. First on the after_update of a combo box and then on a command button. The rason is when adding a work order the first item is always a gimme, the MAC for is already blank. IF there is one item then all they do is fill in and save(refresh). If multiple items then there is a command button to add more items(go to new record).

[ranman256]

oops, the ADD NEW record code should have been in my code. (at the top)
it would add the new record, and generate the Item#.

[vad77]

It works as far as populating the field with the WO# and -## but it is numbering properly. Also in my tbl_MAC the field name for the work order is MWO.



Here is my code.

Private Sub Command175_Click()
Dim vWO, vRet, vNum, vItem


DoCmd.RunCommand acCmdRefresh


vWO = Forms!frm_tta!WO
vRet = DCount("*", "tbl_MAC", "[WO]=" & vWO)


If vRet = 0 Then
vItem = vWO & "-01"
Else
vNum = vRet + 1
vItem = vWO & "-" & Format(vNum, "00")
End If
Item = vItem
End Sub

[vad77]

First, thank you for your reply. Forgot to say thank you in my first reply.
Now that I think about it might cleaner and easier if I just number the items without the work order number, 1, 2, 3. I am going to give it shot myself now that I have the basics.

[vad77]

Finally was able to work on DB again after few days. I came up with a solution using a method I have used before and I get the results I wanted.
Here is what I did.
1. Change my "Item" field from text to number with default value of "0".
2. Created a query named "qry_item" from my table called tbl_MAC with the MWO and ITEM fields. Criteria under MWO as [forms]![frm_TTA]![WO].
3. On the Click property of my combo box I added code that is the value of ITEM is 0 to change it to 1. This is for the first item only.
4. On a command button to add additional items I have the following code.

Private Sub cmd_addline_Click()
If Me.Item.Value = 0 Then
MsgBox "You must complete the first item before adding more items."
Else
DoCmd.RunCommand acCmdRecordsGoToNew
Dim ItemNum As Double
ItemNum = DMax("item", "qry_item") + 1
Me.Item.Value = ItemNum
End If


Me.cmb_Type.SetFocus


End Sub

My line items are now numbered 1, 2, 3 ....