×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tables and their relationships

Tables and their relationships

Tables and their relationships

(OP)
I am designing a system which is based on a schema, but i don't know how to link reference tables to validate if the user has entered valid data or complete rubbish.

E.g
tbl_order
Order_id (PK)
Advice_no
Supplier_id
Dept_no
Destination_no
No_of_singles
Unique_ref_no

Reference table
tbl_Supplier
Supplier_id (PK)
Supplier_desc

How do i validate wether the user has entered a valid supplier number?

Can anyone help?

RE: Tables and their relationships

create relationship  
one to many from tbl_Supplier to tbl_order

RE: Tables and their relationships

(OP)
Can i create a one to many relationship between the tbl_supplier and tbl_order even if there is data in the supplier_tbl?

RE: Tables and their relationships

You can also use the "lookup" properties for the Supplier_id field in tbl_order, whether or not you set a relationship (though I recommend the relationship):

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT tbl_Supplier.Supplier_id, tbl_Supplier.Supplier_desc FROM tbl_Supplier ORDER BY tbl_Supplier.Supplier_desc;
Bound Column: 1
Column Count: 2
Column Heads: No
Column Widths: 0"
List Rows: 8
List Width: Auto
Limit To List: Yes

RE: Tables and their relationships

There is a FAQ in the "Access: General Discussion" forum at this site under the Forms section you may be interested in. It allows you to use the NotOnList event to trigger pulling up the Supplier table. This way if the user enters an existing supplier, everything runs fine. If the enter one that is not in your supplier table, a message box pops up asking them if they need to enter a new supplier to the supplier table. It will show you some different settings for your Supplier ID field in your table and/or form that you might find useful. Column Width of zero? JTRockville may have miskeyed this. Probably should be ) 0,1 instead.

Also yes you can put all kinds of add'l data into your supplier table such as Supplier Name, Phone, Contact Name, Fax, Adress, Supplier Type, etc. For instance if you had 25 diferent supplier types, you could have a Supplier Type lookup table related to that field too.

RE: Tables and their relationships

Note that Elizabeth's solution is form based, while my solution and advpay's solution are table based. All are appropriate and can be implemented independently or concurrently.

Also..
I prefer 0" as the column width rather than 0",1", if you have only two columns in the combo and are hiding one. That way, if you have your list width at Auto, then resize your combo-box, your column will grow or shrink appropriately.

RE: Tables and their relationships

JTRockville, thanks for the tip. Sounds like the column widths you do NOT key are autosized f the control width is autosize. I've never come across that before, and will try it.

RE: Tables and their relationships

BTW, if you use JTRockville's solution, the properties set for that field in the table will be the default for theat field when it is listed in any form or report that you later list, so you would not have to even set the propetire in the form. I think the best solution is to implement BOTH ideas, that way you have the lookup functionality at the table level, but you also have the ability to pop up a form to add in new table values when appropriate.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close