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
LVL 1
Anthony6890Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bfuchsCommented:
have this as the default value of control
=Dmax("RFCK","YourTable")+1
and on current event of the form re-assign that value.
me.MyControl.DefaultValue=me.MyControl.DefaultValue.
0
John TsioumprisSoftware & Systems EngineerCommented:
Just a simple recordset..a for loop...AddNew..and you add as many records you need...all with perfect control
0
Anthony6890Author Commented:
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...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
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.

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

Open in new window


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!txtNextCheckNum
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony6890Author Commented:
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!
0
PatHartmanCommented:
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.
0
Anthony6890Author Commented:
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...

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

Open in new window

0
Anthony6890Author Commented:
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...

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

Open in new window

0
Anthony6890Author Commented:
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!
0
Anthony6890Author Commented:
Thank you all, you helped me learn more about DAO.
0
PatHartmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.