Tables and their relationships 1

[Sidney786]

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.<br><br>E.g <br>tbl_order<br>Order_id (PK)<br>Advice_no<br>Supplier_id<br>Dept_no<br>Destination_no<br>No_of_singles<br>Unique_ref_no<br><br>Reference table<br>tbl_Supplier<br>Supplier_id (PK)<br>Supplier_desc<br><br>How do i validate wether the user has entered a valid supplier number?<br><br>Can anyone help?<br>

 

[advpay]

create relationship&nbsp;&nbsp;<br>one to many from tbl_Supplier to tbl_order<br>

 

[Sidney786]

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

 

[JTRockville]

You can also use the &quot;lookup&quot; properties for the Supplier_id field in tbl_order, whether or not you set a relationship (though I recommend the relationship):<br><br>Display Control: <U>Combo Box</U><br>Row Source Type: <U>Table/Query</U><br>Row Source: <U>SELECT tbl_Supplier.Supplier_id, tbl_Supplier.Supplier_desc FROM tbl_Supplier ORDER BY tbl_Supplier.Supplier_desc;</U><br>Bound Column: <U>1</U><br>Column Count: <U>2</U><br>Column Heads: <U>No</U><br>Column Widths: <U>0&quot;</U><br>List Rows: <U>8</U><br>List Width: <U>Auto</U><br>Limit To List: <U>Yes</U>

 

[elizabeth]

There is a FAQ in the &quot;Access: General Discussion&quot; 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.<br><br>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.

 

[JTRockville]

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.<br><br>Also..<br>I prefer <U>0&quot;</U> as the column width rather than <U>0&quot;,1&quot;</U>, if you have only two columns in the combo and are hiding one. That way, if you have your list width at <U>Auto</U>, then resize your combo-box, your column will grow or shrink appropriately.<br>

 

[elizabeth]

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.

 

[elizabeth]

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.