IS THERE AN EASY ALTERNATIVE TO AUTONUMBERING? 2

[BLBurden]

Hello again guys:
Here's my delima. I have a form that I enter records each day. I have the system date set on the form as the day I entered the record. I aslo have an ID field where I need it to show what record I entered. On the next day I need to do the same thing, but I need the ID # to start back for # 1 and count until finish. I usally dont have but 1-10 record to put in daily. See example below

Date: ID #: ( this needs to be autonumber)

2003-06-09 01
02
03 etc.. until finish
Next Day same form

2003-06-10 01
02
03 etc...

If I can get any input in this it will very much appreciated, I have tried and tried again to make it work, if it's not possible will someone out there let me no so I can continue on with other steps. I have tried making a table for everyday of the week and append it to the form each work day, but there has got to be a better way.


Thanks ever more for your help.


BLB
Always Grateful

 

[scriverb]

MichaelRed: I realize that but as you can glean from her posting "her boss enters the records". It seems as though it is a single-user database and they have a specific need for this kind of setup.

I have seen bosses like to keep track of the number of transactions on a single day like this. I realize that a simple query giving them totals for the day is easy enough but also they just like to see that number incrementing one at a time. Who's to know.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BLBurden]

Again, I appreciate all the help here. I promise y'all its is a single user database only. And the only time my boss go near it is when I go on vacation, and that's not often enough.

I am not going to rock the boat. and I am not going to fix it if its not broke. Scriverb gave me a solution I am veryhappy with it, and my boss will be happy also. He doesn't know anything about Access and Databases, and I just know enough to be dangerous.

For now it will be and is a single user database, we are the only department in our company that deals with the sort of permits to be enter into a db. So again I say to all thanks a whole lots for y'all valuable input.

Signing off singing "Happy Trails to you, until we meet again"

BLB - Always Grateful
A good friend will bail you out of jail. A true friend will be sitting at your side saying, "Boy - didn't we fun?"

 

[AccessUser22]

I've used the method that Scriverb posted above and it worked great. Problem is, now I've noticed I'm having certain records locking up in my form. The only thing I can come up with is that it has something to do with this method of autonumbering. I noticed this locking when I went to try to update some fields on a past record. Then I tried to delete the locked record and got the following error:

Error 3188 - Could not update;Currently Locked by another session on this machine.

Anyone have any ideas on how I can get around this? Thanks!

 

[MichaelRed]

go back to the faq referenced long away and far ago in this thread. Actually read the entire thread. The implementation is quite prone to problems unless the app is STRICTLY single user (at least for data entry). The "fool proof" method in the faq is -at least for me- the only pratical process for custom "autonumber".




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[AccessUser22]

I may have to give your method a try since eventually we may have two people doing data entry. At the moment however, the database is completely single user, residing on my own machine since it's in development. I'm not having these locking issues due to other users attempting to create records with the same ID. I'm the only user logged in with only one copy running on my machine. All I'm doing is attempting to go back and edit a non-IDNumber field within a record. I'm gonna play with it some more, but I'd like to be able to see why I'm getting the locks in the first place. Also for additional info I used the first method that Scriverb suggested.

Thanks.

 

[MichaelRed]

the most common 'reason' to get the error in the single user environment is when you have two (or more) processes addressing the same record / recordset (depending on locking) at the time of the edit. Often, this can arise as you instantiate some object and fail to completly close it while continuing with other objects, arrive at the same record (recordset) and attempt the edit. The first instance of addressing the record (recordset) maintains a reference to the record (recordset) and interfeers with hte current operation.


p.s. fix the numbering thing now. it is much easier to do it (or any changes) earlier rather than later.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelCz]

There is a very simple solution - so simple it doesn't need DAO or DOA or VBA.
Goto your table and create an extra field - it must be integer, call it X.
Create a memo form which loads up when Access starts - use setvalue for unbounded date field, another field an unbounded field , format to fixed number, call it Count.
When the form opens or include in autoexec macro, have a setvalue macro set Count to 1. you can of course have a single line table behind this with the two fields.

On Input Form
There must be a field which indicates that the row of data is complete - say after update of amount or date - attach another macro to this. Macro does this: Setvalue of X to Count [X now = 1] , and then the nice touch, the second macro uses setvalue Count + 1 [Count now equals 2]. So after you do your next input,X now = 2, count = 3 and so on.
Using macro "setvalue" and memo forms which load up in the background are very powerful tools.

Or you could even do a looping macro to run n times based on unbound field [call this Z] in form footer which uses expression : count([amount]) to get a value - this represents the number of entries on the page.
Have another unbound field called CountX.

Start a new macro A1 which says:

Run macro B1 and repeat until countX > count (which evaluates to false and stops macro)
setvalue countX = 1


Macro B1
Goto record first
setvalue X = countX
setvalue countX = countX + 1
next record

This will then loop until countX = count and then the macro A1 stops, which means that B1 and the looping also stops.

I know when others see this - they will laugh with derision - there is not even a single Dim anywhere.

Here is the loop:

Actions
Name Condition Action Argument Value
UpdateTTfromReview
SetValue Item: [FormsfrmProp osedTranslations]![CountX]

Expression: 1
Start Loop
RunMacro Macro Name: Mismatch.CreateInTT

Repeat Count:
Repeat Expression: [FormsfrmProp
osedTranslations]![CountX]<[Form
sfrmProposedT
ranslations]![Count]
CreateInTT
OpenForm Form Name: frmPayrollTranslations

translation form

GoToRecord
goto new record Record: New


SetValue Item: [Forms]![frmPayrollTranslations]![P
ayroll No]

Expression: [FormsfrmProp
osedTranslations]![Payroll No]
PAYROLL NO
SetValue Item: [Forms]![frmPayrollTranslations]![Allowance Ded]

Expression: [FormsfrmProposedTranslations]![Allowance Ded]
ALLOW
SetValue Item: [Forms]![frmPayrollTranslations]![
MAPS code]

Expression: [FormsfrmProp
osedTranslations]![MAPS code]

MAPS
Close Object Type:


GoToRecord
Goes to Next Record Record: Next

Changes the CounTX SetValue Item: [FormsfrmProposedTranslations]![CountX]
Number to allow macro
To eventaually stop.

Expression: [CountX]+1

I'm an accountant looking after a revenue budget of $1/4 billion and I use the above looping method to generate a replacement code for every mismatched code which has gone to suspense [ I have an extra field into which an action query picks up the correct code froma translation table.

I could have used SQL to do this - but users like to see what is happening..

We even use the looping method to even generate Excel file outputs for a report which has 100's of recepients.