Incremental Check Numbers

[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.

 

[Roger Carlson]

Actually, you can create a number sequence directly in the query, even in a
Make Table query.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NumberedQuery.mdb" which illustrates how to do this. Find it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[margaret]

That's a much easier way. But what if I don't want to start with the number
1. But each time I run the make table query I want a new starting number?

 

[Roger Carlson]

To start your squence at any given number, just add one less to the Sequence
field. For instance, if you wanted the query to start at 11, do this:

Sequence: DCount("CustName","Customers","CustName <='" & [CustName] &
"'")+10
or
Sequence: (Select Count(1) FROM Customers A WHERE A.CustName
<=Customers.CustName)+10

(depending on which method you used)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[margaret]

I feel like I'm being difficult ... however, since this field will be check
numbers, the user would have to manually figure out the starting sequence
number each time she runs the query (which will be once a day) and then edit
the query. I would rather that didn't happen. Is there a way to do the
same, simple task, that lets the user give the starting number?

To start your squence at any given number, just add one less to the Sequence
field. For instance, if you wanted the query to start at 11, do this:

Sequence: DCount("CustName","Customers","CustName <='" & [CustName] &
"'")+10
or
Sequence: (Select Count(1) FROM Customers A WHERE A.CustName
<=Customers.CustName)+10

(depending on which method you used)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

That's a much easier way. But what if I don't want to start with the
number
1. But each time I run the make table query I want a new starting number?

 

[Roger Carlson]

I guess maybe we should back up. Why are you doing this? Under what
circumstances? If the user is entering data in a form, it's much easier to
simply create the incremented check number at that point.

(On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.
Here's a link:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395)

By using a Make Table query, you're doing some kind of batch processing that
I'm not understanding.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I feel like I'm being difficult ... however, since this field will be check
numbers, the user would have to manually figure out the starting sequence
number each time she runs the query (which will be once a day) and then
edit
the query. I would rather that didn't happen. Is there a way to do the
same, simple task, that lets the user give the starting number?

To start your squence at any given number, just add one less to the
Sequence
field. For instance, if you wanted the query to start at 11, do this:

Sequence: DCount("CustName","Customers","CustName <='" & [CustName] &
"'")+10
or
Sequence: (Select Count(1) FROM Customers A WHERE A.CustName
<=Customers.CustName)+10

(depending on which method you used)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

That's a much easier way. But what if I don't want to start with the
number
1. But each time I run the make table query I want a new starting
number?

:

Actually, you can create a number sequence directly in the query, even
in
a
Make Table query.

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "NumberedQuery.mdb" which illustrates how to do this.
Find
it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
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.

 

[margaret]

I'm attempting to assign check numbers. We have the starting check number
1234 and we have 20 checks to print so I need a unique check number assigned
to each check

I guess maybe we should back up. Why are you doing this? Under what
circumstances? If the user is entering data in a form, it's much easier to
simply create the incremented check number at that point.

(On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.
Here's a link:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395)

By using a Make Table query, you're doing some kind of batch processing that
I'm not understanding.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I feel like I'm being difficult ... however, since this field will be check
numbers, the user would have to manually figure out the starting sequence
number each time she runs the query (which will be once a day) and then
edit
the query. I would rather that didn't happen. Is there a way to do the
same, simple task, that lets the user give the starting number?

To start your squence at any given number, just add one less to the
Sequence
field. For instance, if you wanted the query to start at 11, do this:

Sequence: DCount("CustName","Customers","CustName <='" & [CustName] &
"'")+10
or
Sequence: (Select Count(1) FROM Customers A WHERE A.CustName
<=Customers.CustName)+10

(depending on which method you used)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

That's a much easier way. But what if I don't want to start with the
number
1. But each time I run the make table query I want a new starting
number?

:

Actually, you can create a number sequence directly in the query, even
in
a
Make Table query.

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "NumberedQuery.mdb" which illustrates how to do this.
Find
it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
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.

 

[Roger Carlson]

Well, okay. I still don't see how you're using a Make Table query to do
this, but you can add another section to your Sequence expression which will
find the last number already assigned in a table: DMax("FieldName",
"TableName"). Something like this:

SELECT DCount("CustID","Customers","CustID <=" &
[CustID])+DMax("CustID","Customers") AS Sequence, Customers.CustName,
Customers.CustPhone, Customers.CustID
FROM Customers
ORDER BY Customers.CustID;

Paste this into a query in the sample I mentioned and you'll see it work.
Of course, this is finding the highest number in the same table you are
sequencing, so you wouldn't use it that way. You'd use one table, but find
the highest number in another.

I would convert the query into an append query:

INSERT INTO Customers ( CustID, CustName, CustPhone, CustID )
SELECT DCount("CustID","Customers","CustID <=" &
[CustID])+DMax("CustID","Customers") AS Sequence, Customers.CustName,
Customers.CustPhone, Customers.CustID
FROM Customers
ORDER BY Customers.CustID;

and append it into the table whose value you are finding the highest value
for.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm attempting to assign check numbers. We have the starting check number
1234 and we have 20 checks to print so I need a unique check number
assigned
to each check

I guess maybe we should back up. Why are you doing this? Under what
circumstances? If the user is entering data in a form, it's much easier
to
simply create the incremented check number at that point.

(On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.
Here's a link:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395)

By using a Make Table query, you're doing some kind of batch processing
that
I'm not understanding.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I feel like I'm being difficult ... however, since this field will be
check
numbers, the user would have to manually figure out the starting
sequence
number each time she runs the query (which will be once a day) and then
edit
the query. I would rather that didn't happen. Is there a way to do
the
same, simple task, that lets the user give the starting number?

:

To start your squence at any given number, just add one less to the
Sequence
field. For instance, if you wanted the query to start at 11, do this:

Sequence: DCount("CustName","Customers","CustName <='" & [CustName] &
"'")+10
or
Sequence: (Select Count(1) FROM Customers A WHERE A.CustName
<=Customers.CustName)+10

(depending on which method you used)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

That's a much easier way. But what if I don't want to start with
the
number
1. But each time I run the make table query I want a new starting
number?

:

Actually, you can create a number sequence directly in the query,
even
in
a
Make Table query.

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "NumberedQuery.mdb" which illustrates how to do this.
Find
it
here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
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.