VB Code Autonumber

[Ali29J]

Hi All

I recently asked how to implement a autonumber using code, which worked like a dream when creating a new record.

now I tried using the below, same technique as with the abov, in an existing set of records but it does not work, i applied this in the default value property.

=DMax("[SRTracker]","RFQTracker")+1

effectively, upon opening an existing record, i would like the field "SRNumber", if empty, to lookup the last highest record and add 1 to it and enter into the field...

difficult to explain sorry..

Ali

 

[Ali29J]

by the way this is in a nulti user environment

 

[randy700]

but it does not work
What results do you get?


Randy

 

[Zion7]

default values ONLY work, on NEW records, and ONLY NEW records!!!

You'll have to write code, that loops thru the empty records, and add your code.

rec.Open "SELECT * FROM tblTracker WHERE SRNumber Is Null", _
CurrentProject.Conn.....

Do Until rec.EOF
SRNumber = DMax("[SRTracker]","RFQTracker")+1
rec.Update
rec.movenext
loop

 

[TheAceMan1]

How are ya Ali29J . . .

Put your DMax code in the [blue]On Current[/blue] event of the form!

[blue]   Me!RNumber.DefaultValue = DMax("[SRTracker]","RFQTracker")+1[/blue]

See Ya! . . . . . .

 

[Zion7]

AceMan, are you sure?
I was going to offer that, as another option but,
SRNumber will ONLY get populated, if you navigate to that record?
2ndly, simply changing the default value, again, will
only hve relevance on new records, no?
I was going to suggest
If IsNull(SrNumber) Then SrNumber = DMax(....)+1
But again, if particular record, does not receive current focus, will not get populated. thus, any audit forms
that may use SRNumber in criteria, will not get grouped
properly,(predicating, audit forms may be desired).

 

[TheAceMan1]

Zion7 . . .

Yes with slight modification:

[blue]   If Me.NewRecord Then
      Me!RNumber.DefaultValue = DMax("[SRTracker]", "RFQTracker") + 1
   End If[/blue]

But this only ensures added records receive an [blue]SRTracker[/blue] value.

It sounds as if some kind of [blue] cleanup[/blue] needs to be performed, where the [blue]reecordset is parsed seperately and nulls filled proper![/blue]

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

 

[ZmrAbdulla]

Here is some function

http://www.daniweb.com/code/snippet38.html

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[Zion7]

AceMan, I agree, i think your modification will be sifficient,
for all new records, and yes, a recordset loop, filtered
on null SRnumbers, will be required.
Possibly , my post above, or Zameer's will suffice?

 

[TheAceMan1]

Roger That! [blue]Zion7[/blue] . . .

So what happen to [blue]Ali29J![/blue] . . . [purple]the ball is in their park![/purple]

See Ya! . . . . . .

 

[Ali29J]

hi all

Thankyou for all your feedback, apologies but it was a poets day for me on friday (p**s off early today...) sorry UK humour...

ANyway, i have ventured with some of your suggestions, and managed to get it working although it has not been tested thoruoughly. using the function code posted by ZmrAbdulla and slightly adapted code from Zion7 i got the below:

If IsNull(Me.SRNumber.Value) Then
DoCmd.Close acForm, "RFQTracker"
Me.SRNumber.Value = DMax("[SRNumber]", "RFQTracker") + 1
Me.Requery
End If

i had some issue with duplicate changes hance addition of close command, it is a bit scrappy with the rest of my forms but seems to work!

will let you kno wif any problems with testing!

Thanks

Ali

 

[ZmrAbdulla]

Ok.. wrapping the code with NZ() will help you trap the null error. If it is first record then..
[tt]Me.SRNumber.Value = Nz(DMax("[SRNumber]", "RFQTracker") + 1,1)[/tt]

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.