Send tblName variable to function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anand - 15 Jul 2007 08:10 GMT

Hello all,
I have function that finds the next record in a table. I use this function
to auto-generate the next record number (in a non-Autonumber field) in
several tables - although I have to have a separate function for each table.

The function uses the
dbs.OpenRecordSet ("tblTable1") code.
I would like to re-use this function by sending the Table Name as a variable
to the function - avoiding duplicity of the code. How do I do this?

Thanks in Advance
Anand

Reply to this Message

Allen Browne - 15 Jul 2007 08:45 GMT

This should do it:

Public Function NextNumber(strTable As String, strField As String, _
Optional strCriteria As String) As Long
On Error Resume Next
Dim lngMax As Long

lngMax = Nz(DMax(strField, strTable, strCriteria), 0&)
NextNumber = lngMax + 1&
End Function

Example for field ID in table Table1:
? NextNumber ("Table1", "ID")

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello all,
> I have function that finds the next record in a table. I use this function
[quoted text clipped - 10 lines]
> Thanks in Advance
> Anand

Reply to this Message