using VBA to add and delete a record to a table

[Paul James]

I need to increment the next available autonumber field in a table (named
"tblData") by 1 when a certain event occurs in my database. The table in
which that autonumber is located is not otherwise involved in the event.
But when the event takes place, I need to notch up the autonumber in that
table by exactly one integer. What's the best way to do this?

I realize that one way to do this would be to first add then immediately a
record from tblData. If that's the best way to do it, what would the VBA
code for that be?

(I'm doing this because I'm using the autonumber field in that table to set
ID numbers of certain records in the database, but occasionally the next
available ID number for those records is obtained through another process,
and that's when I need to reset the autonumber in tblData).

Thanks in advance.

Paul

 

[Ken Snell]

My advice is to stop using an autonumber field....and replace it with your
own Long Integer field where you control what the value is (as well as the
next number). In ACCESS 2000 and up, adding a record to increment an
autonumber field, then deleting that record, may *not* permanently use up
that number -- if the database is compacted before another record is added,
the "used" number will be reused.

You're rolling dice here to try to use meaning with the autonumber
field...especially, as you describe it, the value that you want can be "set"
and "used" by a completely different procedure.

If you create a Long Integer field in your table, you can always "get" the
next higher number in the sequence by using an expression similar to this:
NextNumber = Nz(DMax("LongIntegerFieldName", "TableName"), 0) + 1

 

[Ken Snell]

Sorry....one correction to my post.....in A2K2, the autonumber used won't be
reused if you compact the database before you add another record.

 

[Paul James]

Thanks for your reply to my question, Ken. Since we're using Access 2002,
we wouldn't have the problem of not being able to control the autonumber
field. I realize that your suggestion of using the expression you mentioned
to increment an autonumber field is probably a better way to do it, even if
you're in A2K2, but I've already got four tables doing this, with existing
queries, forms and reports based on those fields, and making that change at
this point would require more that a little bit of time.

So my preference would be to run a couple of lines of VBA code that would
add and delete a record to the tables. Can you tell me what that code would
be?

Thanks,

Paul

 

[Ken Snell]

OK - something like this (substitute real names for my generic names)
subroutine could be put in a regular module and then you can call it from
your code:
[When calling this sub, you give it the name of the table, the name of the
autonumber field, the name of any other field in the table, and a value of 1
or 2 depending upon the type of field that the other field is, and it'll
return to you the value of the autonumber that was just used up.]


Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer, lngAutoNumberUsed
As Long)
' VARIABLES:
' ---------
' strTableName is name of table whose AutoNumber field's value is to be
"incremented"
' strAutoNumberFieldName is name of autonumber field in the strTableName
table
' strOtherFieldName is name of any other field in the strTableName table
' intFieldType is variable used to tell subroutine whether the
strOtherFieldName
' field is numeric (intFieldType = 1) or text (intFieldType = 2)
' lngAutoNumberUsed is value of autonumber field that is "used" during the
' incrementing

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
If strTableName = "" Or strAutoNumberFieldName = "" Or _
strOtherFieldName = "" Or (intFieldType <> 1 And _
intFieldType <> 2) Then Exit Sub
strSQL = "SELECT * FROM " & strTableName & " WHERE " & _
strAutoNumberFieldName & " = 0;"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
With rst
.AddNew
Select Case intFieldType
Case 1
.Fields(strOtherFieldName) = 1
Case 2
.Fields(strOtherFieldName) = "A"
End Select
.Update
.Bookmark = .LastModified
lngAutoNumberUsed = .Fields(strAutoNumberFieldName)
.Delete
.Close
End With
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

 

[Paul James]

Thanks so much for this, Ken.

It will be a big help and save me a lot of time.

Paul

 

[Ken Snell]

By the way, just so that you're aware....if you begin to append a record to
a table and then cancel the append action, the autonumber will be used and
won't be available again. So watch out for any parts of your code where an
append action might be canceled (such as in an error handler), as that will
upset your sequential numbers.

 

[Paul James]

Ken - I tested your code and it works great. It adds and deletes a record
from the table, thus incrementing the autonumber field by 1 every time the
procedure runs. This is exactly what I needed.

I had to tweak one minor thing:

Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer,

lngAutoNumberUsed As Long)

I had to pull that last parameter, "lngAutoNumberUsed As Long" out of the
Sub statement and place it in the code as a Dim statement, because the
calling Sub has no way of knowing what the value is. I think this was just
a typo, because you didn't even mention that value as a parameter in your
very helpful comments. So with the modification, the Sub line would read as
follows:

Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer)

with the added Dim statement:

Dim lngAutoNumberUsed As Long

Thanks so much, Ken, for taking the time to write this nifty procedure for
our group.

Paul

OK - something like this (substitute real names for my generic names)
subroutine could be put in a regular module and then you can call it from
your code:
[When calling this sub, you give it the name of the table, the name of the
autonumber field, the name of any other field in the table, and a value of 1
or 2 depending upon the type of field that the other field is, and it'll
return to you the value of the autonumber that was just used up.]


Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer, lngAutoNumberUsed
As Long)
' VARIABLES:
' ---------
' strTableName is name of table whose AutoNumber field's value is to be
"incremented"
' strAutoNumberFieldName is name of autonumber field in the strTableName
table
' strOtherFieldName is name of any other field in the strTableName table
' intFieldType is variable used to tell subroutine whether the
strOtherFieldName
' field is numeric (intFieldType = 1) or text (intFieldType = 2)
' lngAutoNumberUsed is value of autonumber field that is "used" during the
' incrementing

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
If strTableName = "" Or strAutoNumberFieldName = "" Or _
strOtherFieldName = "" Or (intFieldType <> 1 And _
intFieldType <> 2) Then Exit Sub
strSQL = "SELECT * FROM " & strTableName & " WHERE " & _
strAutoNumberFieldName & " = 0;"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
With rst
.AddNew
Select Case intFieldType
Case 1
.Fields(strOtherFieldName) = 1
Case 2
.Fields(strOtherFieldName) = "A"
End Select
.Update
.Bookmark = .LastModified
lngAutoNumberUsed = .Fields(strAutoNumberFieldName)
.Delete
.Close
End With
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub



--
Ken Snell
<MS ACCESS MVP>

Thanks for your reply to my question, Ken. Since we're using Access 2002,
we wouldn't have the problem of not being able to control the autonumber
field. I realize that your suggestion of using the expression you mentioned
to increment an autonumber field is probably a better way to do it, even if
you're in A2K2, but I've already got four tables doing this, with existing
queries, forms and reports based on those fields, and making that change at
this point would require more that a little bit of time.

So my preference would be to run a couple of lines of VBA code that would
add and delete a record to the tables. Can you tell me what that code would
be?

Thanks,

Paul

 

[Ken Snell]

Glad that it works.

The inclusion of "lngAutoNumberUsed As Long" in the argument list for the
subroutine was not a typo. I included it in case you wanted to know the
actual number that was "used" when the subroutine added and deleted the
record. If you don't want it, then you can do as you did (Dim it) or you can
just omit it and the code that contains it.

If you wanted to use it (in other words, get that value back to the calling
code), Dim a variable in the main code and then include it in the argument
list when you call the subroutine:


(main code procedure)

Dim lngANumber As Long
..
..
..
..
..
Call IncrementAutoNumber("TableName", "AutoNumberFieldName", _
"OtherFieldName", 1, lngANumber)


--
Ken Snell
<MS ACCESS MVP>

Ken - I tested your code and it works great. It adds and deletes a record
from the table, thus incrementing the autonumber field by 1 every time the
procedure runs. This is exactly what I needed.

I had to tweak one minor thing:

Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer,

lngAutoNumberUsed As Long)

I had to pull that last parameter, "lngAutoNumberUsed As Long" out of the
Sub statement and place it in the code as a Dim statement, because the
calling Sub has no way of knowing what the value is. I think this was just
a typo, because you didn't even mention that value as a parameter in your
very helpful comments. So with the modification, the Sub line would read as
follows:

Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer)

with the added Dim statement:

Dim lngAutoNumberUsed As Long

Thanks so much, Ken, for taking the time to write this nifty procedure for
our group.

Paul

OK - something like this (substitute real names for my generic names)
subroutine could be put in a regular module and then you can call it from
your code:
[When calling this sub, you give it the name of the table, the name of the
autonumber field, the name of any other field in the table, and a value

of

1
or 2 depending upon the type of field that the other field is, and it'll
return to you the value of the autonumber that was just used up.]


Public Sub IncrementAutoNumber(strTableName As String,
strAutoNumberFieldName As String, _
strOtherFieldName As String, intFieldType As Integer, lngAutoNumberUsed
As Long)
' VARIABLES:
' ---------
' strTableName is name of table whose AutoNumber field's value is to be
"incremented"
' strAutoNumberFieldName is name of autonumber field in the strTableName
table
' strOtherFieldName is name of any other field in the strTableName table
' intFieldType is variable used to tell subroutine whether the
strOtherFieldName
' field is numeric (intFieldType = 1) or text (intFieldType = 2)
' lngAutoNumberUsed is value of autonumber field that is "used" during the
' incrementing

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
If strTableName = "" Or strAutoNumberFieldName = "" Or _
strOtherFieldName = "" Or (intFieldType <> 1 And _
intFieldType <> 2) Then Exit Sub
strSQL = "SELECT * FROM " & strTableName & " WHERE " & _
strAutoNumberFieldName & " = 0;"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
With rst
.AddNew
Select Case intFieldType
Case 1
.Fields(strOtherFieldName) = 1
Case 2
.Fields(strOtherFieldName) = "A"
End Select
.Update
.Bookmark = .LastModified
lngAutoNumberUsed = .Fields(strAutoNumberFieldName)
.Delete
.Close
End With
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub



--
Ken Snell
<MS ACCESS MVP>

Thanks for your reply to my question, Ken. Since we're using Access 2002,
we wouldn't have the problem of not being able to control the autonumber
field. I realize that your suggestion of using the expression you mentioned
to increment an autonumber field is probably a better way to do it,

even
if

you're in A2K2, but I've already got four tables doing this, with existing
queries, forms and reports based on those fields, and making that

change
at

this point would require more that a little bit of time.

So my preference would be to run a couple of lines of VBA code that would
add and delete a record to the tables. Can you tell me what that code would
be?

Thanks,

Paul

 

[Paul James]

Ken, I'm confused about what you're saying in regard to passing a parameter
to another sub when you're trying to find out the value you're passing. I
thought that in order to send a parameter to a function or another sub, you
have to tell that function or sub what the value of the parameter is. If
that's true, how can you send the parameter when you don't know it's value?

Thanks

Paul

 

[Ken Snell]

In this case, you're not sending a parameter to the sub....you're providing
a "location" for the sub to pass a result back to you.

For example, suppose I wanted to write a subroutine to add two numbers
together. I can write this sub:

Public Sub Add2Numbers(varNum1 As Variant, varNum2 As Variant, varNumAnswer
As Variant)
varNumAnswer = varNum1 + varNum2
End Sub


Then I can call this subroutine in my code to have the sub put the value of
the sum into a variable that I call MyAnswer:

Dim MyAnswer As Double
Call Add2Numbers(1, 10, MyAnswer)
MsgBox "The answer is " & MyAnswer

Even though I did not set a value to MyAnswer before calling the sub, the
sub puts its own value into that variable and returns it to me for my use.

I also can do a similar thing using a function:

Public Function Add2Numbers(varNum1 As Variant, varNum2 As Variant) As
Variant
Add2Numbers = varNum1 + varNum2
End Function

Then, I would use this function this way:

Dim MyAnswer As Double
MyAnswer = Add2Numbers(1, 11)
MsgBox "The answer is " & MyAnswer


Hope that is helpful!

 

[Paul James]

My belated thanks for this very helpful code and the clear explanation, Ken.

Until I read your explanation, I didn't understand that lngAutoNumberUsed
was being used to store the autonumber. I've always thought of function
parameters as being something you had to send TO a function. I didn't
realize you could use it to create a variable in which you can store values
generated by the function.