Create Next ID from Table

[jl2886]

I only want the assID to report the highest number if the case number is in the same month and year, otherwise I want it to assign 0. For some reason, my code isn't working.
The lsi case number should read something like 407-112
Lets say that in the month of April of 07 the highest case number we had was 112, then the assID should report 112, this way, when I assign the LSI Case Number for a new case in the month of april it will read 407-113.

I also have another problem because in the criteria part of the DMAX statement I am looking at the LEFT most 3 variables, it could be 4 if we are in month 10 11 or 12.

So, I want the dmax function to scan my database for the month and year formatted as myy (could be a 2 number long month) and report back the largest right most number (in the example above it would be 112, but if it was a new month it would find no number. The right most part of the LSI Case number is always 3 digits long.
After that I want to assign the newest case to be exactly as it is written in my code.

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
   assID = DMax("Right([LSI Case Number],3)", "Master Log", Left([LSI Case Number], 3) = Format(Now, "m") & Format(Now, "yy"))
   Me.LSI_Case_Number = Format(Now, "m") & Format(Now, "yy") & "-" & Format(assID + 1, "000")
End Sub
 

[MMcCarthy]

Try this ...

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
   assID = DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-")-1) = CStr(Month(Now())) & CStr(Year(Now())))
   Me.LSI_Case_Number = CStr(Month(Now())) & CStr(Year(Now())) & "-" & Format(assID + 1, "000")
End Sub
 

Mary

[NeoPa]

Firstly, I would strongly suggest that you change the format of your [LSI Case Number] field to yyyymm-nnn.
What you have at the moment will certainly complicate matters for you and almost certainly will be harder to work with and understand for anyone trying to use it. It will also appear in a funny and unpredictable order in your tables. A two digit year is probably usable at this stage of the century, but is not a wise choice nevertheless.

Assuming my recommendations are followed, this code should help :

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
  Dim strAssID As String
 
  strAssID = NZ(DMax("Right([LSI Case Number],3)", _
                     "Master Log", _
                     "[LSI Case Number] Like Format(Date(),'yyyymm')"), "0")
  Me.LSI_Case_Number = Format(Date(),"yyyymm\-") & _
                       Format(CLng(strAssID) + 1, "000")
End Sub

[pks00]

What about using a recordset to handle it. Might also be better in multi user environments to handle locking issues. Just a thought

eg


assID = DMax("Right([LSI Case Number],3)", "Master Log", Left([LSI Case Number], 3) = Format(Now, "m") & Format(Now, "yy"))
Me.LSI_Case_Number = Format(Now, "m") & Format(Now, "yy") & "-" & Format(assID + 1, "000")
End Sub

dim rs as dao.recordset
dim sSql as string
 
sSql = "SELECT MAX(  MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = " & Format(Now(),"MYY")
 
set rs=currentdb.openrecordset(sSql)
if rs.eof = true then
    Me.LSI_Case_Number = Format(Now(),"MYY") & "-001"
else
    Me.LSI_Case_Number = Format(Now(),"MYY") & format(rs!MaxCaseNo,"000")+1
end if
 
rs.close
set rs=nothing
 

[jl2886]

What is a record set? AND should I just put that code after doubleclick of LSI CASE NUmber in form.
By the way I am importing the table from Excel, so some already have case numbers...will they become record set's?

Also, It's not working...it says that there is an operator missing. and it says that set is missing

[NeoPa]

A Recordset is a set of records.
Simple really, but it can be a table or a select query. Select queries can be as simple as showing a single table or as complex as showing fields from many linked and/or unlinked tables or subqueries.
The rest of your post I don't understand as it is without reference. Which post are you responding to?

[MMcCarthy]

What is a record set? AND should I just put that code after doubleclick of LSI CASE NUmber in form.
By the way I am importing the table from Excel, so some already have case numbers...will they become record set's?

Also, It's not working...it says that there is an operator missing. and it says that set is missing

Did you try my suggestion in post #2 and if so what was the result?

[pks00]

Sorry there was a missing closing bracket in the code. Ive also corrected incrementing the next number

Now at what point would you assign a new ID? Is it at the point of adding a record?
If so then u need to make use of the form's current event. This is called each time a new record is loaded or added, then to check the newrecord flag
eg

public sub Form_Current()
    Dim rs As DAO.Recordset
    Dim sSql As String
 
    If Me.NewRecord = True Then
        sSql = "SELECT MAX(  MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1)) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = " & Format(Now(), "MYY")
        Debug.Print sSql
 
        Set rs = CurrentDb.OpenRecordset(sSql)
        If rs.EOF = True Or IsNull(rs!MaxCaseNo) Then
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-001"
        Else
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-" & Format(rs!MaxCaseNo + 1, "000")
        End If
 
        rs.Close
        Set rs = Nothing
    End If
 

With regards to a recordset, NeoPa has given u an explanation.
A little further explanation - Im not sure how else to describe it but I'll give it a go

Basically a recordset is a variable that holds all the fields that you selected by specifying the sql (or table) and its values and properties.
It is a way in code to read records and also add, update and delete records.

[jl2886]

Did you try my suggestion in post #2 and if so what was the result?

Hi, it reads invalid use of the null?

[jl2886]

Hi, it reads invalid use of the null?

Mccarthy's method reads invalid use of the null.
The recordset method, doesn't do anything? Do I need to click something, or just click the next arrows until I get to a new record

[MMcCarthy]

What does this do ...

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
   assID = nz(DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-")-1) = CStr(Month(Now())) & CStr(Year(Now()))), "001")
   Me.LSI_Case_Number = CStr(Month(Now())) & CStr(Year(Now())) & "-" & Format(assID + 1, "000")
End Sub
 

Mary

[jl2886]

It reads invalid use of null. Then when I open the VBA Code, it says indentifier under cursor is not recognized

What does this do ...

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
   assID = nz(DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-")-1) = CStr(Month(Now())) & CStr(Year(Now()))), "001")
   Me.LSI_Case_Number = CStr(Month(Now())) & CStr(Year(Now())) & "-" & Format(assID + 1, "000")
End Sub
 

Mary

[MMcCarthy]

I've changed it very slightly, can you run it again and if it still gives an error can you tell me exactly which line of code it stops on.

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
 
   assID = DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-")-1) = CStr(Month(Now())) & CStr(Year(Now())))
   assID = assID+1
   Me.LSI_Case_Number = CStr(Month(Now())) & CStr(Year(Now())) & "-" & Format(assID, "000")
End Sub
 

Mary

[jl2886]

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
 
   assID = DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-") - 1) = CStr(month(Now())) & CStr(Year(Now())))
 
   assID = assID + 1
   Me.LSI_Case_Number = CStr(month(Now())) & CStr(Year(Now())) & "-" & Format(assID, "000")
End Sub
 

The bolded line reads identifier under cursor not recognized. LSI Case Number is Text

I've changed it very slightly, can you run it again and if it still gives an error can you tell me exactly which line of code it stops on.

Private Sub LSI_Case_Number_DblClick(Cancel As Integer)
Dim assID As Long
 
   assID = DMax("CLng(Right([LSI Case Number],3))", "Master Log", Left([LSI Case Number], InStr([LSI Case Number], "-")-1) = CStr(Month(Now())) & CStr(Year(Now())))
   assID = assID+1
   Me.LSI_Case_Number = CStr(Month(Now())) & CStr(Year(Now())) & "-" & Format(assID, "000")
End Sub
 

Mary

[pks00]

Hi, how did u run the code?
I set it in form_current and it only gets run when u go into addmode

here is that same example but with diagnostics

Public Sub Form_Current()
    Dim rs As DAO.Recordset
    Dim sSql As String
 
    MsgBox "In Form Current"
    If Me.NewRecord = True Then
 
        MsgBox "In Add Mode"
 
        sSql = "SELECT MAX(  MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1)) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = " & Format(Now(), "MYY")
 
        MsgBox "SQL Generated - please see immediate window, take sql generated and run in sql query editor"
        Debug.Print sSql
 
        Set rs = CurrentDb.OpenRecordset(sSql)
        If rs.EOF = True Or IsNull(rs!MaxCaseNo) Then
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-001"
            MsgBox "EOF encounterd - LSI set to " & Me.LSI_Case_Number
        Else
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-" & Format(rs!MaxCaseNo + 1, "000")
            MsgBox "Record Found - LSI set to " & Me.LSI_Case_Number
        End If
 
        rs.Close
        Set rs = Nothing
    End If
End Sub
 

[jl2886]

the diagnostic boxes come up when I click the arrow and asterik in the form. Then it reads an error datatype mismatch in criteria expression and then it highlights the part in the code that reads " Set rs = CurrentDb.OpenRecordset(sSql)" and the defninition reads indentifier cursor not recognized

Hi, how did u run the code?
I set it in form_current and it only gets run when u go into addmode

here is that same example but with diagnostics

Public Sub Form_Current()
    Dim rs As DAO.Recordset
    Dim sSql As String
 
    MsgBox "In Form Current"
    If Me.NewRecord = True Then
 
        MsgBox "In Add Mode"
 
        sSql = "SELECT MAX(  MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1)) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = " & Format(Now(), "MYY")
 
        MsgBox "SQL Generated - please see immediate window, take sql generated and run in sql query editor"
        Debug.Print sSql
 
        Set rs = CurrentDb.OpenRecordset(sSql)
        If rs.EOF = True Or IsNull(rs!MaxCaseNo) Then
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-001"
            MsgBox "EOF encounterd - LSI set to " & Me.LSI_Case_Number
        Else
            Me.LSI_Case_Number = Format(Now(), "MYY") & "-" & Format(rs!MaxCaseNo + 1, "000")
            MsgBox "Record Found - LSI set to " & Me.LSI_Case_Number
        End If
 
        rs.Close
        Set rs = Nothing
    End If
End Sub
 

[jl2886]

by the way, I am inputting the code right into the visual basic editor within the form. I am not creating a module.

[pks00]

thats fine, ensure the event is registered
looking at form's properties, go to Events tab and click on On Current
then code is called
next id is calculated when u create a new record

[jl2886]

I don't know what I am doing, but it won't work. Did you test it out?
First. I open the form. Then I go to design view. then i open the vba editor.
Then I copy and paste your code into the editor. Then I close the editor and save. Then I save teh form. Then I click on next record.
When I do that, two boxes come up. then the debugger comes up with datatype mismatched. I click debugged and the line with "Set rs = CurrentDb.OpenRecordset(sSql)"

[pks00]

look in the immediate window (from vba window)
there should be some SQL generated
can u please take that and run it

go to query, new query, cancel if it prompts u for a table, then go to sql view and paste it then run it

also paste the sql generated here

[jl2886]

the Sql generated
SELECT MAX( MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1)) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = 407

but then when i go up to run and continue the set rs is highlighted

look in the immediate window (from vba window)
there should be some SQL generated
can u please take that and run it

go to query, new query, cancel if it prompts u for a table, then go to sql view and paste it then run it

also paste the sql generated here

[jl2886]

when i opened a new query and ran the sqly code it read data mismatch error

the Sql generated
SELECT MAX( MID$([LSI Case Number], Instr(1,[LSI Case Number],'-')+1)) AS MaxCaseNo FROM [Master Log] WHERE Left$([LSI Case Number],Instr(1,[LSI Case Number],'-')-1) = 407

but then when i go up to run and continue the set rs is highlighted

[pks00]

What are the values of LSI Case Number in the database

are they all the same format?
do u have any nulls? any in other formats?

[jl2886]

YES I DO, i just realized. Maybe I should remove them.

What are the values of LSI Case Number in the database

are they all the same format?
do u have any nulls? any in other formats?

[NeoPa]

YES I DO, i just realized. Maybe I should remove them.

Remove them - or handle them in your code.
Whichever is the appropriate course of action in your circumstances.