Carrying table and field names into a function

[Stephen English]

I am trying to write a function to update the next number in a table (e.g for
an invoice)
I want to have parameters of table and field name
My question is how do I refer to the field name in a recordset please?
I am sure it is easy and I am just having a mental block!
Thanks for any help you can provide
Stephen

GetNextNo("SysTbl_PO", "ID")

Public Function GetNextNo(strTable As String, strFld As String) As Long
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim lngNext As Long

Set db = CurrentDb
strSQL = "SELECT " & strFld & " FROM " & strTable
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveFirst
With rs
.Edit
Problem is in next two lines!
lngNext = CLng(! & strFld)
' (rs! & strfld) = lngnext +1
.Update
End With
GetNextNo = lngNext
End If
End Function

 

[Ken Snell [MVP]]

rs.Fields(strFld)

 

['69 Camaro]

Hi, Stephen.

I am trying to write a function to update the next number in a table (e.g for
an invoice)

In addition to the syntax you asked for, you should make the following
changes:

1.) Disambiguate the Recordset object to avoid problems later. (You may
use the DAO library object as indicated below.)
2.) Add error handling.
3.) Change:
GetNextNo = lngNext
to:
GetNextNo = lngNext + 1

.... so that it returns the next higher number, not the number passed to it
in the formal parameters.

Beware that you'll have the possibility of multiple records with the same
"NextNo" if you use it in a multiuser environment.


' * * * * Start Code * * * *

Public Function GetNextNo(strTable As String, strFld As String) As Long
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngNext As Long

Set db = CurrentDb
strSQL = "SELECT " & strFld & " FROM " & strTable
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveFirst
With rs
.Edit
lngNext = rs.Fields(strFld).Value
rs.Fields(strFld).Value = lngNext + 1
.Update
End With
GetNextNo = lngNext + 1
End If
End Function

' * * * * End Code * * * *


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[David C. Holley]

Why not use an Autonumber field?

 

[ChrisO]

First post...let's see if I can get this right? :?

No need for the creation of Database and Recordset variables
Therefore, no need to set them to nothing when going out-of-scope
It will not require any references to DAO or ADO in Tools/References
The references may or may not be present, it simply doesn't matter
Works in all 32-bit MDB/MDE versions of Access
Makes for better portability between Access versions

Public Function GetNextNo(ByVal strTable As String,
ByVal strFld As String) As Lon

On Error GoTo ErrorHandle

With CurrentDb.OpenRecordset("SELECT " & strFl
& " FROM " & strTable
' You may not want to add a new record to an empty Recordset
' In which case delete the .Edit, Else, .AddNew and End If
' Add the End If below the .Update line
If Not .EOF The
.Edi
Els
.AddNe
End I
GetNextNo = CLng(.Fields(strFld)
.Fields(strFld) = .Fields(strFld) +
.Updat
.Clos
End Wit

ExitProcedure
Exit Functio

ErrorHandler
MsgBox "Error in GetNextNo(), Module mdlGlobal..
" & vbNewLine &
"Number: " & Err.Number & vbNewLin
&
"Description: " & Err.Descriptio

Resume ExitProcedur

End Functio

"let's see if I can get this right?" Apparently not! :cry:

Hope that helps anyway..

Regards
Chris

 

[Larry Linson]

Why not use an Autonumber field?

Probably because there is an accountant, auditor, or manager who insists
that Invoice Numbers or Order Numbers or <whatever> Numbers be monotonically
increasing, which AutoNumbers are not guaranteed to be -- there are a number
of instances, including cancellation of a new record after it is started but
before it is entered, that will cause the AutoNumber to have gaps.

Gaps in supposedly sequential sequences of numbers are a "red flag" raised
for something being _wrong_ to accountants and auditors (and managers who
have to answer to them who have experienced missing <whatevers> in the
past).

Larry Linson
Microsoft Access MVP

 

[Stephen English]

Hi David
Because I want to write it to two tables. I often use autonumber field.
Thanks
Stephen

 

[Stephen English]

Hi Gunny
Thank you for your help.
You gave me what I asked for; Chris gave me more.
Cheers
Stephen

 

['69 Camaro]

You're very welcome.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Tom Wickerath]

Stephen,

"You gave me what I asked for; Chris gave me more."

Don't you think this reply is just a little bit rude? I do, especially
given the fact that the code that Chris gave does not work. He even seems to
have admitted this with his closing remark: "let's see if I can get this
right?" Apparently not! :cry:"

Perhaps you should take to heart the response you posted to Sam nearly a
year ago (Sep 27 2004, 4:05 am). You wrote, in part:

"An attitude of gratitude is what is needed here - if not you
might have to change your name to get anything answered."

Reference:
http://groups-beta.google.com/group/microsoft.public.access.formscoding/msg/3282691689c19593?hl=en&

I think you should have just stuck with "Thank you for your help", marked
the answer as helpful, and left it at that.


Tom
_________________________________________

:

Hi Gunny
Thank you for your help.
You gave me what I asked for; Chris gave me more.
Cheers
Stephen

 

[ChrisO]

G’day Tom

I think the code I posted does work, at least within the context o
the question

If I’ve misread the question please let me know and I’ll try to fi
it

BTW. What I meant by:
"let's see if I can get this right?" Apparently not
Was that I didn’t get the colour formatting correct
I was looking to get black code with green comments but got all gree
in stead
(I guess you could say I’m a bit green with this sites tags. :wink:


Regards
Chris

 

[Tom Wickerath]

Hi Chris,

First, I would have never guessed in a million years that your closing
statement:

"let's see if I can get this right?" Apparently not! :cry:
Hope that helps anyway...

was in reference to color formatting of any kind.

The error that I experienced was apparently due to a misintrepretation on my
part. I had performed a quick test of your code, using the following steps on
the Northwind sample database:

1.) Copied the Customers table (data and structure) to a new tblCustomers
table.
2.) Deleted the text-based CustomerID primary key.
3.) Added CustomerID back in as an autonumber. Saved the table. The records
were now numbered 1 through 91.
4.) Converted the autonumber to a number (long) and then set this field as a
primary key.

I copied your procedure to a new module. I ran it in the immediate window,
using the following command:

? GetNextNo ("tblCustomers", "CustomerID")

This resulted in Error 3022:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.

I removed the primary key index from the CustomerID field. When I re-ran the
function from the immediate window, I noticed that the first record in the
table had been reassigned an ID from 1 to 2. There were now two records with
CustomerID =2.

Upon further consideration, I realized that your function was intended to
act upon a table that contains a single record. This record holds the number
ready to be assigned to another table that needs a guaranteed increment of
one. This was not made obvious in your post, or even in the original post by
Stephen.

This is called an "Intelligent Key" - and unfortunately that's not a
compliment. The trouble is this setup allows the possibility for repeated
numbers, even when there’s only a single user, but it will happen often
enough in a multiuser environment, too. The reason for this is that the
value of GetNextNo is assigned before the record in the table is actually
updated. If the record fails to update -- or the record is read at the same
time by multiple users in a multiuser environment, then multiple records will
be passed this same number for assignment. The fact that the value sitting in
the table doesn’t match the value being passed to the calling function would
be considered a bug by most programmers.

In summary, my initial statement:

"...especially given the fact that the code that Chris gave does not work.
He even seems to have admitted this with his closing remark..."

was due to a misinterpretation on my part. However, attempting to implement
an intelligent key strategy certainly has problems of its own.


Tom
_________________________________________

:

G’day Tom.

I think the code I posted does work, at least within the context of
the question.

If I’ve misread the question please let me know and I’ll try to fix
it.

BTW. What I meant by: -
"let's see if I can get this right?" Apparently not!
Was that I didn’t get the colour formatting correct.
I was looking to get black code with green comments but got all green
in stead.
(I guess you could say I’m a bit green with this sites tags. :wink:
)

Regards,
Chris.