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:

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
Code:
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")
        
    Else
        '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
    
    Lrs.Close
    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.