/ Snippet Library - View Code

Snippet details

ID: 307

Viewed: 1319

Added: 2002-08-19

Version:

Snippets in this catagory
Show Printable Version

Code to get the next available value for a numberic field such as a Primary Key field for a given table/field. Uses ADO.

General Details

Snippet uploaded by: snippet

Email : webmaster@snippetlibrary.com

Snippet By: Clint LaFever

Snippet By:	Clint LaFever
Declarations
Highlight Allnone
Code
Highlight All'------------------------------------------------------------ ' Author: Clint LaFever [lafeverc@usa.net] ' Purpose: Used to obtain the next available ID in a given table/field ' Parameters: tNAME=Table; fNAME=Field ' Example: x=GetNextID"tblCUSTOMERS","CUST_ID" '------------------------------------------------------------ Public Function GetNextID(tNAME As String, fNAME As String) As Long On Error GoTo ErrorGetNextID Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "Select Max(" & fNAME & ") As MaxID from " & tNAME, db.cn, adOpenStatic If rs.EOF = False Then If IsNull(rs!MAXID) Then GetNextID = 1 Else If rs!MAXID <> -1 Then GetNextID = rs!MAXID + 1 Else GetNextID = 1 End If End If Else GetNextID = 1 End If Exit Function ErrorGetNextID: MsgBox Err & ":Error in GetNextID(). Error Message:" & Err.Description, vbCritical, "Warning" GetNextID = 0 Exit Function End Function

No Reviews to show