how to automatically fill in the beginning of a field based on ano

[John O]

I have a form for invoices in which the vendor field is selected from the
list of vendors as specified in the vendors table. I'd like to find a way
that, once I select the vendor, the Invoice Number would automatically begin
with the two-letter Vendor abbreviation also specified in the Vendor table.
Any help out there?

 

[Steve Schapel]

John,

I would recommend not doing it like this. If you think about it, you
are creating redundancy in your data. Once you have the Vendor
selected, you automatically have the Vendor Abbreviation available.
Therefore you don't need to write it into the Invoice Number.

Of course, if your business processes call for the Vendor Abbreviation
to be *displayed* as part of the invoice number on your forms and
reports including printed invoices, then that's understandable - and a
separate question. You can easily do that via concatenating the Vendor
Abbreviation in, in a calculated field in a query, or the control source
of a calculated control on the form or report itself.

Hope you understand.

 

[John O]

Thank you, Steve. I get what you're saying about the redundancy, and that I
could have the Abbreviation attached to the invoice number in any reports I
create. The problem I'm trying to avoid, though, is the possibility of
having two (or more) invoices with the same number (the primary key) since
some of my vendors have invoice numbers like 003 or 004 that could easily be
duplicated by other vendors.

I was thinking of creating a macro that would insert the Vendor abbreviation
into the Invoice number field once I select the vendor. Would that work?
Honestly, I don't have much experience with the macros.

Thanks.

 

[Steve Schapel]

John,

If the Invoice Number is the Primary Key, then it will not be possible
to have then duplicated.