Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
How to create incremeneted Check Number in Access table from a specified starting check number.
Hi, I'm trying to create a module or some type of formula that will prompt the user to enter a beginning check number: i.e. 1118172 and then update subsequent rows in a table with sequential number including that first number, incremented by 1 for each subsequent line.
Here is an example...
Starting Check Number: 1118712
First three rows in the table would have:
1118712
1118713
1118714
I have a column in a table labeled: RFCK#. I would like to use some form of a parameter value to enter the first check number and then cascade down, as I described above. I've tried many things, but it is just getting frustrating.
Any help on this would be greatly appreciated.
-Anthony
Here is an example...
Starting Check Number: 1118712
First three rows in the table would have:
1118712
1118713
1118714
I have a column in a table labeled: RFCK#. I would like to use some form of a parameter value to enter the first check number and then cascade down, as I described above. I've tried many things, but it is just getting frustrating.
Any help on this would be greatly appreciated.
-Anthony
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
have this as the default value of control
=Dmax("RFCK","YourTable")+
and on current event of the form re-assign that value.
me.MyControl.DefaultValue=
=Dmax("RFCK","YourTable")+
and on current event of the form re-assign that value.
me.MyControl.DefaultValue=
Just a simple recordset..a for loop...AddNew..and you add as many records you need...all with perfect control
bfuchs...
I'm putting the starting value in a form field and is the DMax value going into the table itself? Just slightly clouded and what is going where...
I'm putting the starting value in a form field and is the DMax value going into the table itself? Just slightly clouded and what is going where...
How you go about this depends somewhat on where you are in the process. It sounds like you already have the check "records" created somewhere and now you want to assign a check number to them. In that case I would create a query that selects the check records with null check numbers and use an order by clause that sequences the records if you have some guidelines for how the numbers should be assigned.
In VBA, get the starting number from your form field (or preferably from the check control table). Open this query, and use a DAO .Update loop to assign the number to each row.
Then you have to somehow pick out these newest checks from the table and print them. You could use the starting checknumber from the form as a starting point using:
WHERE CheckNum >= Forms!yourform!txtNextChec
In VBA, get the starting number from your form field (or preferably from the check control table). Open this query, and use a DAO .Update loop to assign the number to each row.
Dim rs as DAO.Recordset
Dim db as DAO.Database
Dim qd as DAO.Querydef
Dim iCheckNum as Long
iCheckNum = Me.txtNextCheckNum
Set db = CurrentDB
Set qd = db.Querydefs!qSelectNullCheckNumbers
Set rs = qd.OpenRecordset
Do Until rs.EOF = True
rs.Edit
rs!CheckNum = iCheckNum
rs.Update
iCheckNum = iCheckNum + 1
rs.MoveNext
Loop
Then you have to somehow pick out these newest checks from the table and print them. You could use the starting checknumber from the form as a starting point using:
WHERE CheckNum >= Forms!yourform!txtNextChec
Hey Pat, yes you are correct. I already have the check records. I've never used a DAO, can you give me a little more direction on this or point me in the right direction to read about it more...
Thanks!
Thanks!
I wrote all the code you need although you will probably have to modify the column and control names. You can put it in the click event of a button on the form where the starting number is entered.
DAO is a method that allows you to open recordsets in VBA and manipulate the data (among other things table related). Most of the time we use queries to add, update, and delete rows but in this case, you need to generate the check number so your options are to create a function that returns the next function for the update query to use or the simpler method is to generate the number as you loop through the recordset. I use both methods and generally the update query is preferred because it is almost always faster. For this process, you won't have enough records to update for speed to be a problem and I think this method is logically easier for a novice to follow so I choose the DAO update loop.
Look at the code line by line.
The first four lines define the variables, the first three of which are DAO objects. Objects are more than simple variables. Don't worry about that at this point.
The next four lines set the variable and objects to some value.
The final part is the loop that reads through the recordset (just like scrolling through a query or table in the Access GUI) and does something to each row.
The Do Until constrains the loop so Access knows its scope. In this case we want to read all records in the recordset until there are no more. EOF = end of file
rs.Edit -- says that you are going to edit the record (other options are .addNew and .delete)
rs!CheckNum = variable - puts the current value of the variable in the check number
rs.Update -- says save the record
rs.MoveNext -- says to move to the next record in the recordset.
Loop -- identifies the ending line for the Do Until loop. So everything between Do Until and Loop is executed for each record of the recordset.
Remember I told you to use an Order By clause in the query that you are using to produce the recordset. Get in the habit of being concious of whether or not sequence of records is important to the logic of what you want to do. In a relational database such as Access, SQL Server, Oracle, DB2, etc. tables and queries are unordered sets. That means that row order is unpredictable UNLESS you specifically sort the rows.
DAO is a method that allows you to open recordsets in VBA and manipulate the data (among other things table related). Most of the time we use queries to add, update, and delete rows but in this case, you need to generate the check number so your options are to create a function that returns the next function for the update query to use or the simpler method is to generate the number as you loop through the recordset. I use both methods and generally the update query is preferred because it is almost always faster. For this process, you won't have enough records to update for speed to be a problem and I think this method is logically easier for a novice to follow so I choose the DAO update loop.
Look at the code line by line.
The first four lines define the variables, the first three of which are DAO objects. Objects are more than simple variables. Don't worry about that at this point.
The next four lines set the variable and objects to some value.
The final part is the loop that reads through the recordset (just like scrolling through a query or table in the Access GUI) and does something to each row.
The Do Until constrains the loop so Access knows its scope. In this case we want to read all records in the recordset until there are no more. EOF = end of file
rs.Edit -- says that you are going to edit the record (other options are .addNew and .delete)
rs!CheckNum = variable - puts the current value of the variable in the check number
rs.Update -- says save the record
rs.MoveNext -- says to move to the next record in the recordset.
Loop -- identifies the ending line for the Do Until loop. So everything between Do Until and Loop is executed for each record of the recordset.
Remember I told you to use an Order By clause in the query that you are using to produce the recordset. Get in the habit of being concious of whether or not sequence of records is important to the logic of what you want to do. In a relational database such as Access, SQL Server, Oracle, DB2, etc. tables and queries are unordered sets. That means that row order is unpredictable UNLESS you specifically sort the rows.
Hi Pat,
Thanks for the tutorial surrounding DAO recordsets and how to use the appropriately. I've adjusted the code to reflect my correct value names; however, I'm getting an error on the Set rs = line. It says that there are too few parameters...
Thanks for the tutorial surrounding DAO recordsets and how to use the appropriately. I've adjusted the code to reflect my correct value names; however, I'm getting an error on the Set rs = line. It says that there are too few parameters...
Private Sub cmdAppendNumbers_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim iCheckNum As Long
iCheckNum = Me.txtNextCheckNum
Set db = CurrentDb
Set qd = db.QueryDefs!qCheckNullValues
Set rs = qd.OpenRecordset
Do Until rs.EOF = True
rs.Edit
rs!RFCK = iCheckNum
rs.Update
iCheckNum = iCheckNum + 1
rs.MoveNext
Loop
End Sub
Ok, I was able to get it to work, which is wonderful, thanks for your help! As it relates to the OrderyBy. How do I set the code to order by the field RFLANA. Can I put that somewhere in the cmd that will first reorder it by that field and then append the check number?
This is the revised code I have...
This is the revised code I have...
Private Sub cmdAppendNumbers_Click()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim iCheckNum As Long
iCheckNum = Me.txtNextCheckNum
Set db = CurrentDb
Set qd = db.QueryDefs!qCheckNullValues
Set rs = qd.OpenRecordset
Do Until rs.EOF = True
rs.Edit
rs!RFCK = iCheckNum
rs.Update
iCheckNum = iCheckNum + 1
rs.MoveNext
Loop
End Sub
Oh, I got that too. I'm just including the Order By in the original query that creates the recordset before I have the Check Numbers appended.
Thanks for all your help!
Thanks for all your help!
Good for you for getting through this. DAO loops are very useful and not that tough to code once you understand some concepts. You may also hear people mention ADO which is a similar data access library but more geared toward ODBC. I use DAO all the time for both Jet/ACE and ODBC, mostly because I'm too lazy to learn ADO and I haven't yet run into any situations where DAO (which is native for Jet/ACE) was too slow for ODBC.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial