Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: creating my own sequential number based on txtbox

    So here is the code im trying to adopt. frmJobTicket is used to populate tblItemsForJobTransfer. jobnumbertxtbox AfterUpdate =
    Code:
    Private Sub jobnumber_AfterUpdate()
        'Assign ItemCode
        If IsNull(ticketid) = True Then
            'A Commodity Type must be selected
            If IsNull(jobnumber.Value) = False Then
                ticketid = NewTicketID(jobnumber.Value)
            End If
        End If
        
    End Sub
    Which fires

    Code:
    Function NewTicketID(pjobnumber) As String
    
        Dim db As Database
        Dim LSQL As String
        Dim LUpdate As String
        Dim LInsert As String
        Dim Lrs As DAO.Recordset
        Dim LNewItemCode As String
    
        On Error GoTo Err_Execute
    
        Set db = CurrentDb()
    
        'Retrieve last number assigned for Commodity Type
        LSQL = "Select lastticketid from jobs"
        LSQL = LSQL & " where jid = '" & pjobnumber & "'"
    
        Set Lrs = db.OpenRecordset(LSQL)
        'If no records were found, create a new Commodity Type in the Codes table
        'and set initial value to 1
        If Lrs.EOF = True Then
    
            LInsert = "Insert into Codes (jid, lasticketid)"
            LInsert = LInsert & " values "
            LInsert = LInsert & "('" & pjobnumber & "', 1)"
    
            db.Execute LInsert, dbFailOnError
    
            'New Item Code is formatted as "AGR-0001", for example
            LNewTicketID = pjobnumber & "-" & Format(1, "0000")
    
        Else
            'Determine new ItemCode
            'New ItemCode is formatted as "AGR-0001", for example
            LNewTicketID = pjobnumber & "-" & Format(Lrs("lastticketid") + 1, "0000")
    
            'Increment counter in Codes table by 1
            LUpdate = "Update Codes"
            LUpdate = LUpdate & " set lastticketid = " & Lrs("lastticketid") + 1
            LUpdate = LUpdate & " where jid = '" & pjobnumber & "'"
    
            db.Execute LUpdate, dbFailOnError
    
        End If
    
        Lrs.Close
        Set Lrs = Nothing
        Set db = Nothing
    
        NewTicketID = LNewTicketID
    
        Exit Function
    
    Err_Execute:
        'An error occurred, return blank string
        NewTicketID = ""
        MsgBox "An error occurred while trying to determine the next TicketID to assign."
    
    End Function
    I have already inserted names of tables, fields, etc. where i thought they should go. A good sign is that im getting the OnError pop up message. On the downside its not working as i thought it would. This module fires AfterUpdate of the jobnumbertxtbox. I am not getting any macro error popup. Could anyone take a look at the code and see they see an issue?
    Last edited by bsigmon1103; 03-29-12 at 14:42.

  2. #2
    Join Date
    Mar 2012
    Posts
    68
    FYI. I removed the OnError command and received an error on the line highlighted in red.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is jid numeric or string / text?
    if numeric you dont' need to quote the literal value pjobnumber
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by healdem View Post
    is jid numeric or string / text?
    if numeric you dont' need to quote the literal value pjobnumber
    Yes i seen that after i posted. So went through and removed '. They are numeric fields. do i need to keep the ampersand?

  5. #5
    Join Date
    Mar 2012
    Posts
    68
    new code
    Code:
    Function NewTicketID(pjobnumber) 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 last number assigned for Commodity Type
        LSQL = "Select lastticketid from tblJobTickets"
        LSQL = LSQL & " where jid = me.jobnumber"
    
        Set Lrs = db.OpenRecordset(LSQL)
        'If no records were found, create a new Commodity Type in the Codes table
        'and set initial value to 1
        If Lrs.EOF = True Then
    
            LInsert = "Insert into tblJobTickets (jid, lasticketid)"
            LInsert = LInsert & " values "
            LInsert = LInsert & " & me.jobnumber, 1)"
    
            db.Execute LInsert, dbFailOnError
    
            'New Item Code is formatted as "AGR-0001", for example
            LNewTicketID = pjobnumber & "-" & Format(1, "0000")
    
        Else
            'Determine new ItemCode
            'New ItemCode is formatted as "AGR-0001", for example
            LNewTicketID = pjobnumber & "-" & Format(Lrs("lastticketid") + 1, "0000")
    
            'Increment counter in Codes table by 1
            LUpdate = "Update Codes"
            LUpdate = LUpdate & " set lastticketid = " & Lrs("lastticketid") + 1
            LUpdate = LUpdate & " where jid = me.jobnumber"
    
            db.Execute LUpdate, dbFailOnError
    
        End If
    
        Lrs.Close
        Set Lrs = Nothing
        Set db = Nothing
    
        NewTicketID = LNewTicketID
        
    
        
        
    
        Exit Function
    
    
    
    End Function
    Error on same line. "Too few parameters. Expected1."

  6. #6
    Join Date
    Mar 2012
    Posts
    68
    I have attached a sample using the refereneces in the code. If you someone could take a look and see something im not seeing. Thanks!
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    At first sight, the problem is that:
    Code:
        LSQL = "Select lastticketid from tblJobTickets"
        LSQL = LSQL & " where jid = me.jobnumber"
    Should be:

    Code:
        LSQL = "Select lastticketid from tblJobTickets"
        LSQL = LSQL & " where jid = " & me.jobnumber
    Have a nice day!

  8. #8
    Join Date
    Mar 2012
    Posts
    68
    Sinndho.
    I thought that was the case and tested that. Looks like I had the dquotes placed incorrectly. I'm goin to try again. Thanks!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In your database, there is aloso a problem in the function NewTicketID, even when the quotes are in the right place:
    Code:
    Function NewTicketID(pjobid) 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 last number assigned for Commodity Type
        LSQL = "Select ltid from test"
        LSQL = LSQL & " where jnumber = " & jid
        
        Set Lrs = db.OpenRecordset(LSQL)
    The argument of the function is :
    Code:
    Function NewTicketID(pjobid)
    Then you try to use:
    Code:
        LSQL = LSQL & " where jnumber = " & jid
    while jid is not declared. It is then a Variant that is Empty.

    The LSQL string used to open the Recordset is then:
    "Select ltid from test where jnumber ="
    wich causes an error (Missing operator in expression: 'jnumber =').

    You could easily avoid such errors by forcing the declaration of all variables in the module. Add:
    Code:
    Option Explicit
    in the Declaration section (immediately after "Option Compare Database"). If you now try to compile the project, you'll receive an error (Compile Error: Variable not defined) with jid highlighted.
    Attached Thumbnails Attached Thumbnails Compile error.jpg  
    Have a nice day!

  10. #10
    Join Date
    Mar 2012
    Posts
    68
    Here is where i got the code from. I was trying to adjust it to my needs. seems i'll be spending a little more time trying to get this to work. Thanks for your help.
    Attached Files Attached Files

  11. #11
    Join Date
    Mar 2012
    Posts
    68
    New code:

    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 last number assigned for Commodity Type
        LSQL = "Select ltid from test"
        LSQL = LSQL & " where jnumber = " & jid
        
        Set Lrs = db.OpenRecordset(LSQL)
        
        'If no records were found, create a new Commodity Type in the Codes table
        'and set initial value to 1
        If Lrs.EOF = True Then
        
            LInsert = "Insert into test (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 test"
            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
    I feel like im getting somewhere. Am i wrong in thinking that? Now i have an error on red line above. Again with the too few parameters.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem is the same as the first one:
    Code:
    LInsert = "Insert into test (jnumber, ltid)"
            LInsert = LInsert & " values "
            LInsert = LInsert & "(jid,1)"
            
            db.Execute LInsert, dbFailOnError
    Should be:
    Code:
    LInsert = "Insert into test (jnumber, ltid)"
            LInsert = LInsert & " values "
            LInsert = LInsert & "(" & jid & ",1)"
            
            db.Execute LInsert, dbFailOnError
    Have a nice day!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so one of the lessons from here is
    when writing SQL on the fly and you have problems ALWAYS look at the actual SQL being sent to teh SQL engine. its very easy to glance at your SQL and believe its correct. whether you alwasy assign SQL to a variable and then execute the variabe, or just copy it and put it in a msgbox or simialr is up to you.

    personally I always assign it to a variable (the days of wrrying about runnignout of program memory should be long gone (especially using Access)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •