M
margaret
I'm attempting to create a make table query that will automatically give me
check numbers. This is what I have done so far.
My Database is called Games.accdb (yes, Access 2007)
I have field ... tblcontrol.checknumber that I will insert the starting
check number.
I have a make table query ... qrytempcheckfile ... that will pull the check
information(ie addressee, amount, etc). In that query, I have a field ckno:
getnewnum(). It calls the function getnewnum() that is a module
(modGetNewNum()) written as follows:
Public Function GetNextNum() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = GamesDb
Set rst = db.OpenRecordset("tblControlFile", dbOpenDynaset)
With rst
rst.MoveFirst
rst.Edit
rst!CheckNumber = rst!CheckNumber + 1
rst.Update
End With
GetNextNum = rst!CheckNumber
Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Error_Handler:
Resume Exit_Here
End Function
The make table query runs fine but the ckno field is all zeros. Also, I'm
guessing on this, but I can see how it will take the checknumber field and
increase by +1 on the first record, but doesn't it need to increase by +2 on
the second field?
Any help would be much appreciated.
check numbers. This is what I have done so far.
My Database is called Games.accdb (yes, Access 2007)
I have field ... tblcontrol.checknumber that I will insert the starting
check number.
I have a make table query ... qrytempcheckfile ... that will pull the check
information(ie addressee, amount, etc). In that query, I have a field ckno:
getnewnum(). It calls the function getnewnum() that is a module
(modGetNewNum()) written as follows:
Public Function GetNextNum() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = GamesDb
Set rst = db.OpenRecordset("tblControlFile", dbOpenDynaset)
With rst
rst.MoveFirst
rst.Edit
rst!CheckNumber = rst!CheckNumber + 1
rst.Update
End With
GetNextNum = rst!CheckNumber
Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Error_Handler:
Resume Exit_Here
End Function
The make table query runs fine but the ckno field is all zeros. Also, I'm
guessing on this, but I can see how it will take the checknumber field and
increase by +1 on the first record, but doesn't it need to increase by +2 on
the second field?
Any help would be much appreciated.