Automatically Assign a Number Reference

[Guest]

I am converting a paper based audit program to an Access database and need
some help. I have a database with a table in it that contains various audit
sections (cash, investments, etc.) Each section has a letter assigned to
identify it, for example, cash=c, investments=I, etc. There are multiple
audit procedures for each section. The referencing system we currently use
requires labels for each procedure in numerical order within each section as
follows: c.1 for the first cash procedure, c.2 for the second cash procedure,
i.1 for the first investment procedure, i.2….., i.3…. etc. where the letter
represents the audit section and the number represents the audit procedure.
I have created a form for the users to utilize when entering new procedures
into the table. The users can’t keep track of all of the letters and numbers
and how many procedures have been entered so far, so I want the form to
automatically assign this sequence to each new record. I was able to get the
reference letter to populate based on a combo box, which shows the letter
along with the section, but I can’t figure out how to get it to assign the
next available number in that section. The autonumber feature wanted to use
sequential numbers across all sections, and I need my numbers to be
sequential within each section. Any thoughts on how this can be done? I am
new to Access so any help is appreciated.
Thanks.

 

[David Gartrell]

Hi there

What I would suggest is to setup a table with 2 numeric fields. One field
would hold the next sequential number for the cash entries and the other
would hold the same for the investment entries.

Then what you need to do is to create a hidden subform within the procedure
entry form, with the subform pointing to the new table you've created. This
sub form should contain 2 text boxes containing the values of the 2 fields
in your table.

Then all you need to do is enter your procedure and set your form so that it
will append the appropriate sequential number depending on what type of
procedure your're entering. Finally you'll need to use a bit of VB code to
increment the relevant sequential number in the subform.