A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 29th, 2008, 03:56 AM posted to microsoft.public.access.forms
John O
external usenet poster
 
Posts: 5
Default how to automatically fill in the beginning of a field based on ano

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?
  #2  
Old August 29th, 2008, 04:10 AM posted to microsoft.public.access.forms
Steve Schapel
external usenet poster
 
Posts: 1,422
Default how to automatically fill in the beginning of a field based onano

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.

--
Steve Schapel, Microsoft Access MVP

John O wrote:
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?

  #3  
Old August 29th, 2008, 02:19 PM posted to microsoft.public.access.forms
John O[_2_]
external usenet poster
 
Posts: 5
Default how to automatically fill in the beginning of a field based on

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" wrote:

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.

--
Steve Schapel, Microsoft Access MVP

John O wrote:
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?


  #4  
Old August 30th, 2008, 12:17 AM posted to microsoft.public.access.forms
Steve Schapel
external usenet poster
 
Posts: 1,422
Default how to automatically fill in the beginning of a field based on

John,

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

--
Steve Schapel, Microsoft Access MVP

John O wrote:
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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.