Autonumber Problem

[kruger101]

Hi guys
I searched for my answer but then realised I don't really know what I am looking for.
I have a (what I think is a basic) problem, about autonumbers. I have a table, CallsToCustomer_tbl. In this table I have 2 fields, RefNumber (which links to the main table, and in the main table RefNumber is a sequential Autonumber), and TelephoneRef, which is also a sequential Autonumber field and this is my primary key in the CallstoCustomer_tbl.

So the idea of the program is this: If the RefNumber in the main table changes, the TelephoneRef autonumber should reset and start from 1 again. So if I add another record in my main table, say RefNumber changes from 3 to 4, the TelephoneRef (autonumber) should reset to 1 and start numbering from 1 again.

It's that simple, but I'm unable to solve this problem.

And one more thing... I know there is almost a similiar thread on the main page, but in my opinion that thread is too sophisticated for my problem. I just want to change my autonumber field in my main table from 1 to CMS-001 and 2 to CMS-002. I know I should use the format function but can someone please break it down for me?

Thanks a whole lot. All the help I always get on this site makes me think the world is a good place after all.
Regards
kruger101

 

[WayneRyan]

Kruger,

You're going to have to use the form's BeforeInsert event to execute
a line of code like:

Me.MyNewNumber = Nz(DMax("[AutoNumber]", "MainTable", "[AutoNumber] = " & Me.YourAutoNumber), 0) + 1

Naturally, you'll have to substitute your correct table/column names.

Also, don't try to store the "CMS-" part, you can easily add it for display
purposes. If you had numerous prefixes, you'd have to store it, but in
a different field.

Use the Search Facility here and look for DMax, it's quite a common topic.

hth,
Wayne

 

[kruger101]

Kruger,

You're going to have to use the form's BeforeInsert event to execute
a line of code like:

Me.MyNewNumber = Nz(DMax("[AutoNumber]", "MainTable", "[AutoNumber] = " & Me.YourAutoNumber), 0) + 1

Naturally, you'll have to substitute your correct table/column names.

Also, don't try to store the "CMS-" part, you can easily add it for display
purposes. If you had numerous prefixes, you'd have to store it, but in
a different field.

Use the Search Facility here and look for DMax, it's quite a common topic.

hth,
Wayne

Hi Wayne
Thanks for the help. I can't quite get it to work, can you please use the variables I gave you?, I'm quite the simpleton when it comes to this... I guess this is what you get when you are self taught.

Me.MyNewNumber = Nz(DMax("[AutoNumber]", "MainTable", "[AutoNumber] = " & Me.YourAutoNumber), 0) + 1Wayne

If I understand correctly, MyNewNumber is a new number I should create, Autonumber is the RefNumber (as explained above in 1st post) and YourAutoNumber is the TelephoneRef (as explained). Is this correct?

I don't know the Visual Basic part that good, I normally do everything I can with macros...

Thanks again
Cheers
kruger101