Adding a Prefix

[Charlienews]

Hi,

My MDB needs to be distributed to our 2 salesmen on their laptops. As they
will be raising quotes with separate replicated BE I need to issue the quote
numbers with a prefix of thier initials. The quote numbers as currently
generated sequentially via a macro and update query which updates the number
in the table. With one BE, this works a treat, but now I need to be able to
keep the quote numbers unique as these will be linked to the orders.

Any help would be appreciated.

Thanks in advance

Charlie

 

[Tom van Stiphout]

On Sat, 19 Jun 2010 18:02:38 +0100, "Charlienews"

The recommended way to do this is to have a hidden QuoteID that is a
random autonumber. That way there will rarely be collisions.

Another option is a standard autonumber, and have the Primary Key over
this Autonumber + the SalesrepID.

If you have to generate a human-readable quotenumber as you describe,
write some VBA code. Search the web for the "DMax+1" technique.
Keywords: BeforeInsert, DMax

-Tom.
Microsoft Access MVP

 

[Charlienews]

Hi Tom,

Thanks for your reposnse.

The end result I need is: RepName / QuoteNumber.

I would use =[RepName]&" "&[QuoteNumber] in the Controlsource but I do need
the final result saved to the table.

Thanks

Charlie

 

[Douglas J. Steele]

Why do you need it saved? As long as you've got the two individual fields
RepName and QuoteNumber in the table, you can create a query that has a
calculated field that concatenates the two values and use the query wherever
you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele/AccessIndex.html
Co-author: "Access 2010 Solutions", published by Wiley
(no private e-mails, please)

Hi Tom,

Thanks for your reposnse.

The end result I need is: RepName / QuoteNumber.

I would use =[RepName]&" "&[QuoteNumber] in the Controlsource but I do
need the final result saved to the table.

Thanks

Charlie

On Sat, 19 Jun 2010 18:02:38 +0100, "Charlienews"

The recommended way to do this is to have a hidden QuoteID that is a
random autonumber. That way there will rarely be collisions.

Another option is a standard autonumber, and have the Primary Key over
this Autonumber + the SalesrepID.

If you have to generate a human-readable quotenumber as you describe,
write some VBA code. Search the web for the "DMax+1" technique.
Keywords: BeforeInsert, DMax

-Tom.
Microsoft Access MVP

 

[Charlienews]

Hi Doug,

This field will be subsequently used for searching and filtering reports.
Giving it a unique identifyer that is not the primary key will allow me to
do this.

I have got around this by using the =[QuoteNumber]&" / "&[Rep] in the
Control source of RepName, with an after update Event Procedure in Rep of
QuoteRef = RepName which fills the QuoteRef field with the text I need to
keep a reference for.

This may seem a little long winded but it got the job done.

Thanks

Charlie

Why do you need it saved? As long as you've got the two individual fields
RepName and QuoteNumber in the table, you can create a query that has a
calculated field that concatenates the two values and use the query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele/AccessIndex.html
Co-author: "Access 2010 Solutions", published by Wiley
(no private e-mails, please)

Hi Tom,

Thanks for your reposnse.

The end result I need is: RepName / QuoteNumber.

I would use =[RepName]&" "&[QuoteNumber] in the Controlsource but I do
need the final result saved to the table.

Thanks

Charlie

On Sat, 19 Jun 2010 18:02:38 +0100, "Charlienews"

The recommended way to do this is to have a hidden QuoteID that is a
random autonumber. That way there will rarely be collisions.

Another option is a standard autonumber, and have the Primary Key over
this Autonumber + the SalesrepID.

If you have to generate a human-readable quotenumber as you describe,
write some VBA code. Search the web for the "DMax+1" technique.
Keywords: BeforeInsert, DMax

-Tom.
Microsoft Access MVP


Hi,

My MDB needs to be distributed to our 2 salesmen on their laptops. As
they
will be raising quotes with separate replicated BE I need to issue the
quote
numbers with a prefix of thier initials. The quote numbers as currently
generated sequentially via a macro and update query which updates the
number
in the table. With one BE, this works a treat, but now I need to be
able to
keep the quote numbers unique as these will be linked to the orders.

Any help would be appreciated.

Thanks in advance

Charlie