Hi 
I have 5 tables and each have two field "PartyId" And "PartyName" common.

Now i have two TxtBox Controls on it Name "TxtPartyId" And "TxtPartyName".

I want that if a user enters a Party ID in TxtPartyId, then the Code look
these 5 Tables One By One And Pull the PartyName in TxtPartyName where it
found.

Note: All 5 tables will have seperate Party Id and there will not be any
duplication of any Party id, in any table. (One Party Code will be assign to
the Only a single Party.)

Thank you

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

Malik

If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps 
your data would benefit from a bit more normalization.

Having an ID field in common is ... common.  Having a ...name field shared 
among tables is ... a spreadsheet!

Please post the table structure (fieldnames, examples of data) if you want 
folks here to offer more specific suggestions.

-- 

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"malik via AccessMonster.com" <u49026@uwe> wrote in message 
news:a15c0b81ae5e3@uwe...
> Hi
> I have 5 tables and each have two field "PartyId" And "PartyName" common.
>
> Now i have two TxtBox Controls on it Name "TxtPartyId" And "TxtPartyName".
>
> I want that if a user enters a Party ID in TxtPartyId, then the Code look
> these 5 Tables One By One And Pull the PartyName in TxtPartyName where it
> found.
>
> Note: All 5 tables will have seperate Party Id and there will not be any
> duplication of any Party id, in any table. (One Party Code will be assign 
> to
> the Only a single Party.)
>
> Thank you
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 

thanks 4 the response.
I have following 5 Tables.
1. Tblcustomer
 Partyid
Party Name
Address 
Phone
Fax 
email
Isvendor(yes/no)field
Isbroker(yes/no)field

2. Tblvendors
Partyid
Party Name
Address 
Phone
Fax 
email
Iscustomer(yes/no)field
Isbroker(yes/no)field

3. Tblbrokers
Partyid
Party Name
Address 
Phone
Fax 
email
Isvendor(yes/no)field
Iscustomer(yes/no)field

4. TblOtherParties
Partyid
Party Name
Address 
Phone
Fax 
email

Now i have two of some other tables
1. TblDrvoucher
Drvoucherno
Drdate
Isposted(yes/no)

2.TblDrvoucherBndy
SerialNo
Drvoucherno (has a relation with TblDrvochers.Drvoucherno.)
Accountno (** Now this field is going to use first 4 tables. Party id field
of first 4 tables will have relation with this field).
DrAmount
Narration
.................................

Now i want to pull party name if user enters pary id.

If there is a need of normalization, please guide me in brief.

Thank u.

Jeff Boyce wrote:
>Malik
>
>If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps 
>your data would benefit from a bit more normalization.
>
>Having an ID field in common is ... common.  Having a ...name field shared 
>among tables is ... a spreadsheet!
>
>Please post the table structure (fieldnames, examples of data) if you want 
>folks here to offer more specific suggestions.
>
>> Hi
>> I have 5 tables and each have two field "PartyId" And "PartyName" common.
>[quoted text clipped - 11 lines]
>>
>> Thank you

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

To start with, since your tblCustomer has [PartyID] and [PartyName], storing 
[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther) is 
unnecessary and exposes your database to data integrity issues.  For 
example, what happens if someone changes his/her name?  You make the change 
in tblCustomer ... but how does that change make it into all the other 
places?!

Far simpler is to store it once, in the tblCustomer table, then only use the 
PartyID (?CustomerID?) as needed in the other tables.

Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY 
similar to tblCustomer.  I suspect you could further normalize your data 
structure by using a SINGLE table for person-related info (i.e., name, 
address, phone...), and a PersonID (not PartyID, not CustomerID, not 
VendorID, ...).

I have no way to know what situation you are attempting to model.  Can a 
person be a customer, a vendor, a broker, AND an other, all at the same 
time?  Without knowing the relationships, it's hard to offer specific 
suggestions.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"malik via AccessMonster.com" <u49026@uwe> wrote in message 
news:a15ce461802ce@uwe...
> thanks 4 the response.
> I have following 5 Tables.
> 1. Tblcustomer
> Partyid
> Party Name
> Address
> Phone
> Fax
> email
> Isvendor(yes/no)field
> Isbroker(yes/no)field
>
> 2. Tblvendors
> Partyid
> Party Name
> Address
> Phone
> Fax
> email
> Iscustomer(yes/no)field
> Isbroker(yes/no)field
>
> 3. Tblbrokers
> Partyid
> Party Name
> Address
> Phone
> Fax
> email
> Isvendor(yes/no)field
> Iscustomer(yes/no)field
>
> 4. TblOtherParties
> Partyid
> Party Name
> Address
> Phone
> Fax
> email
>
> Now i have two of some other tables
> 1. TblDrvoucher
> Drvoucherno
> Drdate
> Isposted(yes/no)
>
> 2.TblDrvoucherBndy
> SerialNo
> Drvoucherno (has a relation with TblDrvochers.Drvoucherno.)
> Accountno (** Now this field is going to use first 4 tables. Party id 
> field
> of first 4 tables will have relation with this field).
> DrAmount
> Narration
> ................................
>
> Now i want to pull party name if user enters pary id.
>
> If there is a need of normalization, please guide me in brief.
>
> Thank u.
>
> Jeff Boyce wrote:
>>Malik
>>
>>If your 5 tables share the same fields ([PartyID], [PartyName]), perhaps
>>your data would benefit from a bit more normalization.
>>
>>Having an ID field in common is ... common.  Having a ...name field shared
>>among tables is ... a spreadsheet!
>>
>>Please post the table structure (fieldnames, examples of data) if you want
>>folks here to offer more specific suggestions.
>>
>>> Hi
>>> I have 5 tables and each have two field "PartyId" And "PartyName" 
>>> common.
>>[quoted text clipped - 11 lines]
>>>
>>> Thank you
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 

On Wed, 30 Dec 2009 11:52:05 GMT, "malik via AccessMonster.com" <u49026@uwe>
wrote:

>Hi 
>I have 5 tables and each have two field "PartyId" And "PartyName" common.

Then you have at least four misdesigned tables. Relational databases use the
"Grandmother's Pantry Principle": "A place for everything - ONE place! - and
everything in its place".

The PartyName should exist in the table for which PartyID is the Primary Key,
*and noplace else*. Storing the partyname redundantly in all five tables
wastes space, and much more importantly, risks update anomalies, where the
same partyID has different partyNames in different tables.

>Now i have two TxtBox Controls on it Name "TxtPartyId" And "TxtPartyName".
>
>I want that if a user enters a Party ID in TxtPartyId, then the Code look
>these 5 Tables One By One And Pull the PartyName in TxtPartyName where it
>found.

>Note: All 5 tables will have seperate Party Id and there will not be any
>duplication of any Party id, in any table. (One Party Code will be assign to
>the Only a single Party.)

You need a master Parties table with PartyID as the primary key and PartyName
as the (only?) other field.

You can display the partyname on your form by using a DLookUp or a Combo Box
displaying both fields; with the combo box solution, you can use a textbox
with a control source like

=comboParty.Column(1)

to display the second column (it's zero based) of the combo.
-- 

             John W. Vinson [MVP]

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....)  and will use Party Id on other Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox will
confirm him that he has typed the desired Party Id correctly.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field 
BuT
The TxtPartyName has source [PartyName] From "TblParties"  just for display
the name.

Now where the Customer, Vendor, Broker  concern. 

A customer can be a Vendor and a vendor can become a Broker after some time.


Ur suggestion are very helpful for me. 

thank u.







Jeff Boyce wrote:
>To start with, since your tblCustomer has [PartyID] and [PartyName], storing 
>[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther) is 
>unnecessary and exposes your database to data integrity issues.  For 
>example, what happens if someone changes his/her name?  You make the change 
>in tblCustomer ... but how does that change make it into all the other 
>places?!
>
>Far simpler is to store it once, in the tblCustomer table, then only use the 
>PartyID (?CustomerID?) as needed in the other tables.
>
>Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY 
>similar to tblCustomer.  I suspect you could further normalize your data 
>structure by using a SINGLE table for person-related info (i.e., name, 
>address, phone...), and a PersonID (not PartyID, not CustomerID, not 
>VendorID, ...).
>
>I have no way to know what situation you are attempting to model.  Can a 
>person be a customer, a vendor, a broker, AND an other, all at the same 
>time?  Without knowing the relationships, it's hard to offer specific 
>suggestions.
>
>More info, please...
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> thanks 4 the response.
>> I have following 5 Tables.
>[quoted text clipped - 75 lines]
>>>>
>>>> Thank you

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

***Jeff ****

Ok. let me explain my way.
I will save the [PartyId] and [PartyName] only one Time in table
(TblCustomer","TblVendors",etc....)  and will use Party Id on other Places.

Now why I need Party Name on other place?????

If the user remember the Party Id and types in , The Party Name TxtBox will
confirm him that he has typed the desired Party Id correctly.

For Example: (If I have a single table of parties "TblParties")
A Form has two fields name "TxtPartyId" and "TxtPartyName"
The Form's source is "TblDrVouchers".(As I mentioned before.)
Now
When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
"Accountno" field
BuT
The TxtPartyName has source [PartyName] From "TblParties"  just for display
the name.

Now where the Customer, Vendor, Broker  concern.

A customer can be a Vendor and a vendor can become a Broker after some time.

Ur suggestion are very helpful for me.

thank u.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

It sounds like you've decided to keep your current structure.  I'll mention 
again that the design appears not to be well-normalized, based on what I've 
interpreted from your descriptions.

If your design is not well-normalized, you'll find that both you and Access 
will have to work overtime to overcome a data design for which Access is NOT 
optimized.

.... and I still don't understand the inter-relationships among Customers, 
Vendors, Brokers and Others ...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"malik via AccessMonster.com" <u49026@uwe> wrote in message 
news:a15ef6d10134d@uwe...
> Ok. let me explain my way.
> I will save the [PartyId] and [PartyName] only one Time in table
> (TblCustomer","TblVendors",etc....)  and will use Party Id on other 
> Places.
>
> Now why I need Party Name on other place?????
>
> If the user remember the Party Id and types in , The Party Name TxtBox 
> will
> confirm him that he has typed the desired Party Id correctly.
>
> For Example: (If I have a single table of parties "TblParties")
> A Form has two fields name "TxtPartyId" and "TxtPartyName"
> The Form's source is "TblDrVouchers".(As I mentioned before.)
> Now
> When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
> "Accountno" field
> BuT
> The TxtPartyName has source [PartyName] From "TblParties"  just for 
> display
> the name.
>
> Now where the Customer, Vendor, Broker  concern.
>
> A customer can be a Vendor and a vendor can become a Broker after some 
> time.
>
>
> Ur suggestion are very helpful for me.
>
> thank u.
>
>
>
>
>
>
>
> Jeff Boyce wrote:
>>To start with, since your tblCustomer has [PartyID] and [PartyName], 
>>storing
>>[PartyName] in each of the other tables (tblVendor, tblBroker, tblOther) 
>>is
>>unnecessary and exposes your database to data integrity issues.  For
>>example, what happens if someone changes his/her name?  You make the 
>>change
>>in tblCustomer ... but how does that change make it into all the other
>>places?!
>>
>>Far simpler is to store it once, in the tblCustomer table, then only use 
>>the
>>PartyID (?CustomerID?) as needed in the other tables.
>>
>>Next, those other tables (tblVendor, tblBroker, tblOther) look REMARKABLY
>>similar to tblCustomer.  I suspect you could further normalize your data
>>structure by using a SINGLE table for person-related info (i.e., name,
>>address, phone...), and a PersonID (not PartyID, not CustomerID, not
>>VendorID, ...).
>>
>>I have no way to know what situation you are attempting to model.  Can a
>>person be a customer, a vendor, a broker, AND an other, all at the same
>>time?  Without knowing the relationships, it's hard to offer specific
>>suggestions.
>>
>>More info, please...
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Access MVP
>>
>>> thanks 4 the response.
>>> I have following 5 Tables.
>>[quoted text clipped - 75 lines]
>>>>>
>>>>> Thank you
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 

On Wed, 30 Dec 2009 17:28:08 GMT, "malik via AccessMonster.com" <u49026@uwe>
wrote:

>***Jeff ****
>
>Ok. let me explain my way.
>I will save the [PartyId] and [PartyName] only one Time in table
>(TblCustomer","TblVendors",etc....)  and will use Party Id on other Places.
>
>Now why I need Party Name on other place?????
>
>If the user remember the Party Id and types in , The Party Name TxtBox will
>confirm him that he has typed the desired Party Id correctly.

You need to *DISPLAY* the PartyName.

That does not mean that you need to *STORE* the party name.

>For Example: (If I have a single table of parties "TblParties")
>A Form has two fields name "TxtPartyId" and "TxtPartyName"
>The Form's source is "TblDrVouchers".(As I mentioned before.)
>Now
>When a user enter Party Id , The PartyId will stored in "TblDrVouchers"'s
>"Accountno" field
>BuT
>The TxtPartyName has source [PartyName] From "TblParties"  just for display
>the name.

Use a Combo Box with a RowSource like

SELECT PartyID, PartyName FROM tblParties ORDER BY PartyName;

and a control source of AccountNo.

On the form, put a textbox with a Control Source of

=comboboxname.Column(1)

This will allow the user to select a valid PartyID, and display the
corresponding party name, *without* any need to store the party name
redundantly in a second table.

-- 

             John W. Vinson [MVP]