help to fix code findmax

[smile]

Hi, I need to update this code to check if there are no first records (table empty) it should create it. Now it gives an error that it does not find first record and stops if table has no records.

Function FindMax()

   Dim db As DAO.Database
   Dim mx As Integer
   Dim rs As DAO.Recordset
   Dim rsVal As String

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("ppk", dbOpenDynaset)

   rs.MoveFirst

   rsVal = rs.Fields("[Pinigu priemimo kvitas Nr:]").Value
   ' Set mx equal to the numeric portion of the field.
   mx = Right(rsVal, Len(rsVal) - 4)

   ' Loop to make sure you have the maximum number.
   Do While Not rs.EOF
      rsVal = rs.Fields("[Pinigu priemimo kvitas Nr:]").Value
      If Right(rsVal, Len(rsVal) - 4) > mx Then
         mx = Right(rsVal, Len(rsVal) - 4)
      End If
      rs.MoveNext
   Loop

   ' Increment the maximum value by one and
   ' combine the text with the maximum number.
   FindMax = "IMK-" & (mx + 1)

   rs.Close
   db.Close

   Set rs = Nothing
   Set db = Nothing

End Function

Also the prefix IMK- on line " FindMax = "IMK-" & (mx + 1)" could it be defined in a table because I have to make new module for every table.

I would like to have table named increment with column prefix. I would write then IMK- BO- ORD-

The the module would pick what to write depending on form. If active form IMK then prefix should be taken from table increment IMK-
If active form BO then prefix should be taken from table increment Bo-

any ideas?

 

[RuralGuy]

You can start here:

Function FindMax() As String

   Dim db As DAO.Database
   Dim mx As Integer
   Dim rs As DAO.Recordset
   Dim rsVal As String

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("ppk", dbOpenDynaset)

   If rs.RecordCount <> 0 Then
      rs.MoveFirst

      rsVal = rs.Fields("[Pinigu priemimo kvitas Nr:]").Value
      ' Set mx equal to the numeric portion of the field.
      mx = Right(rsVal, Len(rsVal) - 4)

      ' Loop to make sure you have the maximum number.
      Do While Not rs.EOF
         rsVal = rs.Fields("[Pinigu priemimo kvitas Nr:]").Value
         If Right(rsVal, Len(rsVal) - 4) > mx Then
            mx = Right(rsVal, Len(rsVal) - 4)
         End If
         rs.MoveNext
      Loop
   End If

   ' Increment the maximum value by one and
   ' combine the text with the maximum number.
   '-- The integer mx will be initialized to 0 by Access
   FindMax = "IMK-" & (mx + 1)

   rs.Close
   db.Close

   Set rs = Nothing
   Set db = Nothing

End Function

'Also the prefix IMK- on line " FindMax

 

[pbaldy]

Might something like this work to avoid the loop?

SELECT Max(Mid([FieldName],5)) AS MaxNum
FROM TableName

Looping through the entire table to find the max value is terribly inefficient if you can just grab it with one shot.

 

[smile]

Might something like this work to avoid the loop?

SELECT Max(Mid([FieldName],5)) AS MaxNum
FROM TableName

Looping through the entire table to find the max value is terribly inefficient if you can just grab it with one shot.

You mean it can search 5 records back and select max value?
Could you please update the code above cause I tried to fix it myself and it did not work for me. Thanks

 

[pbaldy]

It should find the highest value exclusive of the left 4 digits. Simply use that SQL statement instead of the table name in your recordset code. If you get stuck, post your effort and we'll fix it.

 

[smile]

It should find the highest value exclusive of the left 4 digits. Simply use that SQL statement instead of the table name in your recordset code. If you get stuck, post your effort and we'll fix it.

So went to my form and clicked on "Recordset source" then pasted your code:

SELECT Max(Mid([Pinigu priemimo kvitas Nr:],5)) AS MaxNum
FROM ppk

But now the form is not working at all When I try to use the form I get Error: You can''t go to specified record.

 

[pbaldy]

I meant in this line, not the form itself:

Set rs = db.OpenRecordset("ppk", dbOpenDynaset)

 

[smile]

Replaced the line you said and got syntax error.

 

[pbaldy]

Exactly what was the line you tried? This?

Set rs = db.OpenRecordset("SELECT Max(Mid([Pinigu priemimo kvitas Nr:],5)) AS MaxNum
FROM ppk", dbOpenDynaset)

 

[smile]

Exactly what was the line you tried? This?

Set rs = db.OpenRecordset("SELECT Max(Mid([Pinigu priemimo kvitas Nr:],5)) AS MaxNum
FROM ppk", dbOpenDynaset)

Yes that is the line it is painted red and shows syntax error:

Set rs = db.OpenRecordset("SELECT Max(Mid([Pinigu priemimo kvitas Nr:],5)) AS MaxNum
FROM ppk", dbOpenDynaset)

If I write it on one line like this:
Set rs = db.OpenRecordset("SELECT Max(Mid([Pinigu priemimo kvitas Nr:],5)) AS MaxNum FROM ppk", dbOpenDynaset)

I get no more syntax error but get error on line:

rsVal = rs.Fields("[Pinigu priemimo kvitas Nr:]").Value

 

[RuralGuy]

Try: rsVal = rs.Fields("MaxNum")
Remember, your new SQL names the field AS MaxNum.