AutoNumber within a Range

[rajgoyal00]

Hi, I started working on this project about a month ago, with no knowledge of access. I know a lot more now than I did. But I am stuck in a Phase of it and I need help from you good people.

I have a Drop_Envelopes Table with 5 fields:

1. [ID] = AutoNumber (Using it just because I should have a primary KEY) Dont really need it
2. [DATEVAL] = Date For that Day
3. [Envelope_No] = NUMBER (Envelope Drops in Safe)
4. [CASH] = Currency (Amount of cash in the Envelope)
5. [Checks] = Currency (Amounty of checks in the Envelops)

Purpose: We drop envelopes in a safe. This records which envelope no. had how much cash or checks.

Problem (FOR NOW): The Envelopes we have Cycle from #1 - # 80



In a day's time we can go through 6 or 7 envelopes, (The numbering is in series).

I want my form to Auto Generate my envelope No. and cycle through 1-80 (could be the same day or the next). The form Openes when cliked on button on a diffrent form and filters with That Form's date.

I tried using Dlast("[Envelope_No]","[Drop_Envelopes]") +1 in the default value of the field in the form and also in "Before Update" Event of the field.

I am using a split form (I need the datasheet vew so I can see what all I have entered for the day)
When I start entering a new record, the datasheet view generates another row with DLAST working. For example: If I am working on envelope NO.1 and press Enter. It auto Populates #2 on the next row, but As soon as I start working on #2 , the next row(third) becomes #2. because the last record it recognizes is #1 and #2 hasnt been registered yet. So Its not going to work for me.
As far as looping, ( I havent gotten to that part)

I can really use some help. Please reply with any idea you have. Any help will be greayly appreciated.
Thanks

[orange]

I think you are going to have to learn some vba and looping.
Currently your DLast(..) is working for each record. You need it to be done only when you go to a new Envelop.

Also, most people will advise you to use DMax rather than DLast.

Start
if all done then exit
else
Get New Envelop Number  DMAX statement
   For each record 
      do the record stuff
           the cash
           the checks
    next record
next envelop
endif

Logic is something like the above in general terms.

Good luck

[rajgoyal00]

Thanks for a reply, I kind if knew that its gonna have to be in VBA. But If some one can give me some clear directions, It will really help. I have been trying to dfind a solution from a while now! Thanks Again

[June7]

Have you been trying to code this? Do you need to learn VBA? Start with this http://office.microsoft.com/en-us/ac...010341717.aspx