TKSoft-Online

 
Public Function FnlngGet_NextFreeNr(strField As String, strTable As String, _
                                    Optional strWHERE As String = \"\", _
                                    Optional blnMaximum As Boolean = False, _
                                    Optional lngStartwert As Long = 1) As Long
    Dim lngActNr    As Long
    Dim lngMaxNr    As Long
    Dim strSQL      As String
    Dim rs          As DAO.Recordset
 
    strSQL = \"SELECT \" & strField & \" \" & _
               \"FROM \" & strTable & \" \"
    If strWHERE  \"\" Then strSQL = strSQL & \"WHERE \" & strWHERE & \" \"
    strSQL = strSQL & \"ORDER BY \" & strField
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.BOF And rs.EOF Then
        FnlngGet_NextFreeNr = lngStartwert
      Else
        If blnMaximum Then
            rs.MoveLast
            FnlngGet_NextFreeNr = rs(0) + 1
          Else
            lngMaxNr = 0
            rs.MoveFirst
            lngActNr = 1
            Do Until rs.EOF
                If lngActNr  lngMaxNr Then lngMaxNr = rs(0)
                rs.MoveNext
                lngActNr = lngActNr + 1
            Loop
            FnlngGet_NextFreeNr = lngMaxNr + 1
        End If
    End If
    rs.Close
    Set rs = Nothing
End Function
 

 
Dim nNewNumber As Long
    nNewNumber = FnlngGet_NextFreeNr(\"DeinZählerfeld\", \"Deine Tabelle\"«»)