[whschimmel]

Dear Sir/Madam,

I'm creating a database for the registration of all our contacts with our customers.

I would like to create a call number for every contact, this call number has to excist from:

customer number + year + month + unique number. This means something like this:

851000-2008-07-00001 (withouth the '-')

Does anybody know how?

Greetz,
Wouter

[OBP]

Wouter, it requires VBA code to do this.
You would have the VBA code in the in the Data Input Form in a Field's "After Update" event procedure.
Does each Customer need the "unique number" to be in sequence or can it just be in sequence accross all Customers.

[whschimmel]

It may be a sequence across customers

[OBP]

You will have to be very careful with this field, as the Number it would produce
85100020080700001
would be very large and may not display properly, it may be necessary to produce it as a Text field where adding the "-" would make it more readable.
It displays as
8.51000200807E+16

[whschimmel]

When adding the - character makes the code (and so the database) more workable, it isn't a problem.

But can't the eventual code (851000-2008-07-00001) be created as a text field? That way the number won't be shown as 8.51000200807E+16. Or is it like an autonumber field always generated as a number?

[OBP]

Yes it should be a text field, and you can have the "-" or not in the text field, that is up to you.
Is it possible to post a copy of the database?
It does not need any data in it, I just need to see the Field names and the data input form.

[whschimmel]

I've added my database. The code has to be generated as the Call-ID field in the tbl_Melding and FORM_Melding.

[OBP]

Ok I will post something tomorrow.

[whschimmel]

Many thanks in advance!

[OBP]

Is Customer Number actually DealerID?

[whschimmel]

the customer number is the field 'Dealercode' (851000)

[OBP]

Wouter, are there already records in the database that will need the "Call Number" converted to the new Format?

[whschimmel]

no, it's all test data

[OBP]

Wouter, this version has the simpler Customer CallNumber just incrementing for all Customers, you can have it incrementing seperately for each Customer as well.
I have taken the liberty of tidying up your Forms a little and setting the table Relationships for the Tables that supply data to the Melding Table via Combo boxes, to do so I changed the Fields in the melding Table from text to Number type Integer to store the StatusID and UserID rather than the text.
On the Melding Form I have left the dealercode field visible, it does not need to be, it is only there for the VBA code to use.
The VBA uses a New Query called Que_CallNumber.

[whschimmel]

Thank You! It's working. You saved me hours of searching!!