Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unhappy Unanswered: Number is not incrementing

    I have a form that upon double-clicking a control, should increment the number[alphanumeric] by 1... it is not working properly. It will not increment and I cannot figure out why, does not give an error code...

    Here is the form code:

    Function Mainform_NewLot() As Long

    Dim db As Database
    Dim sqlstr As String
    Dim rs As Recordset
    Dim sql As QueryDef
    Dim rval As Integer

    sqlstr = "SELECT TOP 1 * FROM [Lot Numbers]" _
    & "Where ((([Lot Number]) Like ""SP"" & Right(Year(Now()),1) & ""*""))" _
    & "ORDER BY [LOT NUMBER] DESC;"


    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sqlstr)

    If rs.BOF Then
    HighIndex = Year(Now) * 1000
    Else
    If Year(rs![Date Entered]) = Year(Now) Then
    lastdigits = Right(CStr(rs![Lot Number]), 3)
    Lotno = CStr(Year(Now)) + lastdigits
    HighIndex = CLng(Lotno)
    Else
    HighIndex = Year(Now) * 1000
    End If
    End If
    rs.Close
    HighIndex = HighIndex + 1
    Mainform_NewLot = HighIndex

    End Function

    ________________________________


    Private Sub Lotnumber_AfterUpdate()
    Dim db As Database
    Dim strsql As String
    Dim rs As Recordset

    Set db = CurrentDb()
    strsql = "Select [Lot Numbers].[Product Code], [Lot Numbers].[Lot Number] From [Lot Numbers] " _
    & "WHERE [Lot Numbers].[Lot Number] = """ & Me![LotNumber] & """ "

    Set rs = db.OpenRecordset(strsql)

    If Not rs.BOF Then
    If rs![Product Code] <> Me![Product] Then
    MsgBox "The Product Code and Lot Number are an Invalid Combination. Please correct.", vbExclamation, MsgError

    Exit Sub
    End If
    End If


    End Sub
    ___________________________________________

    Private Sub LotNumber_DblClick(Cancel As Integer)
    Dim rval As Long
    Dim Lotno As String

    rval = Mainform_NewLot
    Lotno = "SP" & Right(CStr(Year(Now)), 1) & "-" & Right(CStr(rval), 3)

    Me![LotNumber] = Lotno

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    couple of things to look at to start with:

    two strings
    lastdigits = Right(CStr(rs![Lot Number]), 3)
    Lotno = CStr(Year(Now)) + lastdigits

    so try & in place of +

    Right(CStr(rs![Lot Number]), 3)
    for strings, right$() is faster than right()

    WHERE [Lot Numbers].[Lot Number] = """ & Me![LotNumber] & """ "
    so what is [Lot Number] string or numeric?
    if numeric, change to
    WHERE [Lot Numbers].[Lot Number] = " & Me![LotNumber] & " "

    there might be more, but these ones jumped out at me first.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Well, I tried this:

    Lotno = CStr(Year(Now)) + lastdigits
    Lotno = CStr(Year(Now)) & lastdigits

    and

    WHERE [Lot Numbers].[Lot Number] = """ & Me![LotNumber] & """ "
    Lot Number is a string...so I did not make this modification

    no errors but the results are still the same, no increment...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one thought:
    cstr(Xxxx) + 999
    is strange!

    maybe you are trying for something like (now being the year 2006)
    2006 x 1000 + 123
    = 2006000 + 123
    = 20060123
    in which case, why the cstr(), try cint()

    do you have
    option explicit
    up at the top of the module
    (if no: fix it and...)
    ...then try try compiling
    ?? do you get any compile errors?

    your increment is very simple, it MUST work
    (let's indent it for clarity)
    ...so maybe your programme flow never gets to the increment part
    Code:
    If rs.BOF Then
        HighIndex = Year(Now) * 1000
    Else
        If Year(rs![Date Entered]) = Year(Now) Then
            lastdigits = Right(CStr(rs![Lot Number]), 3)   '<<< query right(cstr( 
            Lotno = CStr(Year(Now)) + lastdigits          '<<< query cstr() + number
            HighIndex = CLng(Lotno)
        Else
            HighIndex = Year(Now) * 1000
        End If
    End If
                                   ' <<<<<< here comes your increment
    rs.Close
    msgbox "before inc " & highindex       '<<<<<<<< add a line
    HighIndex = HighIndex + 1 
    msgbox "after inc " & highindex          '<<<<<<<< add a line
    Mainform_NewLot = HighIndex
    do you get to the msgboxes when it runs??
    ...if yes, does it increment?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    decided to delete it - absolute crap!
    izy
    Last edited by izyrider; 01-04-06 at 14:20. Reason: gone!
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    last desperate thought before i dive into TV: take care with . and !

    object.control

    collection!item

    most times A is flexible and accomodating, but sometimes it protests
    so
    me.TextboxOnMe

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    Izzy--Thank you very much for your responses and suggestions.Let me tell you what I did based on your recommendations.

    There was no Option Explicit at the top, I put it there, compiled the module and got the following error:

    Compile Error:Variable Not Defined

    HighIndex was highlighted

    So I added the following:

    Dim HighIndex As Integer ‘hopefully this is correct
    Dim lastdigits As String ‘hopefully this is correct
    Dim LOTNO As String ‘hopefully this is correct
    since it gave same variable not defined error-------Then I compiled-----NO ERRORS

    Then I went through the form as normal, however I got a RUNTIME error ‘6’ [overflow] when it went to the Lot Number… it highlighted line: HighIndex = Year(Now) * 1000

    Let me stop here for a second and explain the Lot Number …

    What it is supposed to do is create a number like SP6-001 for the first one, SP6-002 for the next and so forth. The “6” in SP6 is for the year 2006…


    Next I proceeded with your other suggestions:

    I put in the msgboxs… that was helpful…

    First box said 2006000
    Next box said 2006001

    So that means SP6-001 okay.. Which is good, but the next one should be SP6-002 being pulled up and it is not doing that….

    it goes back and starts all over again at 2006000 and then increments to 2006001
    So, I guess the increment part really works, I don't know why it is starting all over at 2006000 again....

    Any thoughts as what to try next???

  8. #8
    Join Date
    Nov 2003
    Posts
    300
    Hey Izy,

    I just saw and read your last two posts AFTER I did my last Post! So, maybe my new information will help...

    please read last line as the incrementation IS working,,, I know just don't know why it starts from 000 again, instead of taking the last # 001 and then adding 1 to it for 002...??????

    thanks again for all your help...[what's on the tube? :-) ]

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    My Access is getting rusty because of non-use, but don't you need to rs.movefirst ?
    If I remember correctly, rs.BOF is true after you open the recordset and before moving to a record.

    Also, have you tried to place a breakpoint and step through the code? I wonder what code is executed in your nested ifs. And what data is retrieved by the SQL.
    Last edited by ivon; 01-05-06 at 04:06.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    super that the increment works AND is in the flow!
    two things less to worry about

    2006 * 1000 is way over the 32,xxx max of int
    ...cint() is my error! try with:
    clng()
    dim highindex as long

    sorry for that bad direction.

    by now we should have:
    got rid of the compile error
    got rid of overflow errors
    confirmed inc works
    confirmed inc is in the programme flow



    moving on to stuff allied to ivon's stuff.

    testing .BOF is not enough! if you want to know if you have records in rst
    if rst.bof and rst.eof then
    'your rst is empty
    else
    'you are sure you have at least one record
    endif

    in principle rst defaults to the equivalent of .movefirst if there are any records in rst.

    meanwhile Mike Owen published a much smarter solution than TOP 1 DESC for sequential numbers here on the site before site-trash day.
    it involves a table 'tblExample' with a single record with fields
    idExample (int) = 1 cos there is always only ONE record in this table
    nextSeqNo (long) = the next available sequence number

    with apologies to Mike for including my optimistic concurrency "refinement", the code goes something like:

    private function getNextSeqNo() as long
    'return of -1 is error
    cst maxTries as integer = 5 'daft! cst should be const
    dim strSQL as string
    dim tmpSeqNo as long
    dim tried as integer
    getNextSeqNo = -1 'pessimistic start
    do until tried > maxTries
    tmpSeqNo = dlookup("nextSeqNo", "tblExample", "idExample = 1")
    strSQL = "UPDATE tblExample SET nextSeqNo = " & tmpSeqNo + 1 _
    & " WHERE nextSeqNo = " & tmpSeqNo & " And idExample = 1;"
    currentdb.execute strSQL
    if currentdb.recordsaffected = 1 then
    getNextSeqNo = tmpSeqNo
    exit do
    endif
    tried = tried + 1 'another increment haha
    loop
    exit function

    izy


    LATER
    sorry that should be

    const maxTries as integer = 5
    Last edited by izyrider; 01-05-06 at 13:41.
    currently using SS 2008R2

Posting Permissions

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