So i have a form with two fields so far tha populates tblitems for job transfer.
Field 1: tid (ticket ID)
Field 2: jid ( job number ID)

I wanted to create the ticket id based on the job number therefore i have this code:

Private Sub jid_AfterUpdate()
'Assign ItemCode
    If IsNull(tid) = True Then
        'A Commodity Type must be selected
        If IsNull(jid.Value) = False Then
            tid = NewTicketID(jid.Value)
        End If
    End If
Const cQuote = """"  'Thats two quotes
    Me.jid.DefaultValue = cQuote & Me.jid.Value & cQuote
    End Sub
Option Compare Database
Option Explicit

Function NewTicketID(jid) As String

    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim Lrs            As DAO.Recordset
    Dim LNewTicketID   As String
    Set db = CurrentDb()
    'Retrieve ltid for jid
    LSQL = "Select ltid from tblTicketID"
    LSQL = LSQL & " where jnumber = " & jid
    Set Lrs = db.OpenRecordset(LSQL)
    'If no records were found, create a new jid in the test table
    'and set initial value to 1
    If Lrs.EOF = True Then
        LInsert = "Insert into tblTicketID (jnumber, ltid)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "(" & jid & ",1)"
        db.Execute LInsert, dbFailOnError
        'New Item Code is formatted as "AGR-0001", for example
        LNewTicketID = jid & "-" & Format(1, "0000")
        'Determine new ItemCode
        'New ItemCode is formatted as "AGR-0001", for example
        LNewTicketID = jid & "-" & Format(Lrs("ltid") + 1, "0000")
        'Increment counter in Codes table by 1
        LUpdate = "Update tblTicketID"
        LUpdate = LUpdate & " set ltid = " & Lrs("ltid") + 1
        LUpdate = LUpdate & " where jnumber = " & jid
        db.Execute LUpdate, dbFailOnError
    End If
    Set Lrs = Nothing
    Set db = Nothing
    NewTicketID = LNewTicketID
    Exit Function

End Function
in the afterupdate.

For that specific tid i would like to enter material used. Im not quite sure how to go about it. I have in inventory table. I tried using subforms with no luck.