Incrementing a pre-existing field? 1

[sorensok]

I was "handed" a database in which there are about 4000 transactions in a table. The primary key is the TransactionID which is supposed to increment upon each new transaction. Right now it doesn't. On the transaction form, the user has to look to the very last transaction to see the number, then enter the next one by hand. Is there a way to automatically increment this existing field?

Thank you in advance!

Kyle

 

[KenReay]

Hi

see thread702-393134

If Me.NewRecord = True Then


intMax = Nz(DLookup("max(IDNo)", "My Table&quot,0)
intMax = intMax + 1
Me.IDNo = intMax


End If
In the Before Update event of your form should do it Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[sorensok]

Please excuse my infamiliarity with access. I did this:

Private Sub Transaction_ID_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = True Then
intMax = Nz(DLookup("max(IDNo)", "My Table&quot, 0)
intMax = intMax + 1
Me.IDNo = intMax
End If

End Sub

It doesn't seem to work. Am I missing something simple? Probably!!

Any help would be greatly appreciated!

Kyle

 

[KenReay]

Hi

Try it in the BEFORE UPDATE event of the FORM, not the Control Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[sorensok]

Oops,

Thanks for the help Ken. I really appreciate it!

Ok, I put it in the Before update event of the Form. Still nothing. Do I have to change the variable names in your example to reflect my variable names?

Thanks again,

Kyle

 

[MichaelRed]

In addition to what ever it takes to get the process to work, you NEED to be aware that this is -at best- risky in a multiuser environment. In the general (multiuse) case it WILL sooner-or-later NOT return a unique (for the table) value. This -loosely interperted- means it will not be suitable for a primary key (unique index).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[KenReay]

Hi

Yes, substituting your datanames would be a good idea.

As Michael quite correctly points out, in a multi user environment, there is the possibility of duplicate numbers being generated. Suitable Error trapping is therefore required.

Personnaly I would always put such code in a function, which in the event of a duplicate key error, is recalled and a new next number generated.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[MichaelRed]

Of course, most thing are relative - but generally you CANNOT Guarntee uniqueness with any function which does a lookup on the table. Simplistically, unless the table is "Locked" during the read and increment operation, it is always possible for another user to retrieve the same value. Re-Trying may just generate a deadlock situation. Where the number of users doing data entry is 'large', the possability of deadlock increases. With SAMLL numbers of data entry users it is possible to go for quite a while w/o any problems, however I have seen this occur with as few as three or four users doing strictly data entry.

Further, as a recordset grows, the time required for the lookup increases. Again, in 'small group' situatuions, this may not be an -issue at least not initally. But sooner or later, a multiuser app will suffer at least performance degradation from the use of a lookup on the active table.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places