By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Counters

P: n/a
Hi All,

I need some help with my counter, i've a database and the main table
is called PIRs. Initially i was using the code
=DMax("[pir id]","PIRs")+1 to increment the number which started at
about 362 that increamented fine on the form in a single user
environment.

When another user logged on the same number was given to both users
which we can't have as this would cause duplicates.

I was reading up about custom counters and saw a posting for the on
update where the number will update once the user saves the record.
Which is what i want.

This is the code that i have taken

rivate Sub PIR_ID_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PIRs) Then
'Only make a number if it's a new record
me.PIR id=nz(dmax("[PIR ID]"."[PIRs]"))+1
Else
' do nothing: don't overwrite an existing VoucherNumber
End If
End Sub
But this does not work when i try and save the record it tells me that
it can't contain a null value blah blah blah.

Where exactly am i going wrong? I don't want a command box to show me
the number, i was access to automatically assign one once it's saved.

Thanks in advance

Carol

Sep 6 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
ca**********@rbos.com wrote:
Hi All,

I need some help with my counter, i've a database and the main table
is called PIRs. Initially i was using the code
=DMax("[pir id]","PIRs")+1 to increment the number which started at
about 362 that increamented fine on the form in a single user
environment.

When another user logged on the same number was given to both users
which we can't have as this would cause duplicates.

I was reading up about custom counters and saw a posting for the on
update where the number will update once the user saves the record.
Which is what i want.

This is the code that i have taken

rivate Sub PIR_ID_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PIRs) Then
'Only make a number if it's a new record
me.PIR id=nz(dmax("[PIR ID]"."[PIRs]"))+1
Else
' do nothing: don't overwrite an existing VoucherNumber
End If
End Sub
But this does not work when i try and save the record it tells me that
it can't contain a null value blah blah blah.

Where exactly am i going wrong? I don't want a command box to show me
the number, i was access to automatically assign one once it's saved.

Thanks in advance

Carol
Maybe change to
If Me.NewRecord then
me.[PIR id]=nz(dmax("[PIR ID]"."[PIRs]"))+1
'Save it
Me.Dirty = False
Endif
Sep 6 '07 #2

P: n/a
On Sep 6, 1:47 pm, Salad <o...@vinegar.comwrote:
carol.tay...@rbos.com wrote:
Hi All,
I need some help with my counter, i've a database and the main table
is called PIRs. Initially i was using the code
=DMax("[pir id]","PIRs")+1 to increment the number which started at
about 362 that increamented fine on the form in a single user
environment.
When another user logged on the same number was given to both users
which we can't have as this would cause duplicates.
I was reading up about custom counters and saw a posting for the on
update where the number will update once the user saves the record.
Which is what i want.
This is the code that i have taken
rivate Sub PIR_ID_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PIRs) Then
'Only make a number if it's a new record
me.PIR id=nz(dmax("[PIR ID]"."[PIRs]"))+1
Else
' do nothing: don't overwrite an existing VoucherNumber
End If
End Sub
But this does not work when i try and save the record it tells me that
it can't contain a null value blah blah blah.
Where exactly am i going wrong? I don't want a command box to show me
the number, i was access to automatically assign one once it's saved.
Thanks in advance
Carol

Maybe change to
If Me.NewRecord then
me.[PIR id]=nz(dmax("[PIR ID]"."[PIRs]"))+1
'Save it
Me.Dirty = False
Endif- Hide quoted text -

- Show quoted text -
Hi,

Thanks for that i've changed it as suggested but when i try and save
it it gives me a complie error then goes back to highlighting the "."
in the code.
HRM not sure why.

Thanks
Carol

Sep 6 '07 #3

P: n/a
On Sep 6, 1:47 pm, Salad <o...@vinegar.comwrote:
carol.tay...@rbos.com wrote:
Hi All,
I need some help with my counter, i've a database and the main table
is called PIRs. Initially i was using the code
=DMax("[pir id]","PIRs")+1 to increment the number which started at
about 362 that increamented fine on the form in a single user
environment.
When another user logged on the same number was given to both users
which we can't have as this would cause duplicates.
I was reading up about custom counters and saw a posting for the on
update where the number will update once the user saves the record.
Which is what i want.
This is the code that i have taken
rivate Sub PIR_ID_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PIRs) Then
'Only make a number if it's a new record
me.PIR id=nz(dmax("[PIR ID]"."[PIRs]"))+1
Else
' do nothing: don't overwrite an existing VoucherNumber
End If
End Sub
But this does not work when i try and save the record it tells me that
it can't contain a null value blah blah blah.
Where exactly am i going wrong? I don't want a command box to show me
the number, i was access to automatically assign one once it's saved.
Thanks in advance
Carol

Maybe change to
If Me.NewRecord then
me.[PIR id]=nz(dmax("[PIR ID]"."[PIRs]"))+1
'Save it
Me.Dirty = False
Endif- Hide quoted text -

- Show quoted text -
Sorry Ignore the last comment, HRM, well i changed it to the above but
when i click to save the record it still tells me that it can not
contain a null value.......

Sep 6 '07 #4

P: n/a
ca**********@rbos.com wrote:
On Sep 6, 1:47 pm, Salad <o...@vinegar.comwrote:
>>carol.tay...@rbos.com wrote:
>>>Hi All,
>>>I need some help with my counter, i've a database and the main table
is called PIRs. Initially i was using the code
=DMax("[pir id]","PIRs")+1 to increment the number which started at
about 362 that increamented fine on the form in a single user
environment.
>>>When another user logged on the same number was given to both users
which we can't have as this would cause duplicates.
>>>I was reading up about custom counters and saw a posting for the on
update where the number will update once the user saves the record.
Which is what i want.
>>>This is the code that i have taken
>>>rivate Sub PIR_ID_BeforeUpdate(Cancel As Integer)
If IsNull(Me.PIRs) Then
'Only make a number if it's a new record
me.PIR id=nz(dmax("[PIR ID]"."[PIRs]"))+1
Else
' do nothing: don't overwrite an existing VoucherNumber
>> End If
>> End Sub
But this does not work when i try and save the record it tells me that
it can't contain a null value blah blah blah.
>>>Where exactly am i going wrong? I don't want a command box to show me
the number, i was access to automatically assign one once it's saved.
>>>Thanks in advance
>>>Carol

Maybe change to
If Me.NewRecord then
me.[PIR id]=nz(dmax("[PIR ID]"."[PIRs]"))+1
'Save it
Me.Dirty = False
Endif- Hide quoted text -

- Show quoted text -


Sorry Ignore the last comment, HRM, well i changed it to the above but
when i click to save the record it still tells me that it can not
contain a null value.......
Go to the debug window and enter
? nz(dmax("[PIR ID]"."[PIRs]"))+1

Looking at it, it might be that
? nz(dmax("[PIR ID]","PIRs"))+1
should work
Sep 6 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.