Re: [AccessDevelopers] Re: Drop Down to Select Contact

[Toby Bierly]

If you want all studies associated with a contact to be deleted when the contact is deleted, then Yes.   Help explains it pretty well:   ----------------help excerpt start------------------ Why should I use cascading updates or cascading deletes?   For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update and cascade delete related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key values in a primary table, Microsoft Access makes necessary changes to related tables to preserve referential integrity.   If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.   Note   If the primary key in the primary table is an AutoNumber field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.   If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, If you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query, Microsoft Access automatically deletes the records in related tables without displaying a warning. ----------------help excerpt end------------------   HTH, Toby ----- Original Message ----- From: Joe Fonseca To: AccessDevelopers@yahoogroups.com Sent: Wednesday, December 07, 2005 10:34 AM Subject: Re: [AccessDevelopers] Re: Drop Down to Select Contact I have the contactID in the studies table as just a number and it is linked to a contact table that has the autonumber on that one.  It's a one (Contact) to many (Studies) relationship.  I have it set to Enforce Ref. Integrity and Cascade Updates.  Should I have it cascade deleted records? Toby Bierly <[EMAIL PROTECTED]> wrote: You should be storing the ContactID in the Studies table.  All you need to do is use the form wizard to create the form based on the Studies table, or a query of that table. Then, in design mode, change the ContactID text field on the newly created form to a ComboBox (Right-click on the TextBox, Change To-->ComboBox).  You will want the following settings in the properties for the ComboBox: On the Data Tab: Control Source: ContactID (this s! hould already be set by the form wizard) Row Source Type: Table/Query Row Source:  Click on the ... on the right and query for the ContactID and FirstName - LastName1 (two fields) from the Contacts table Bound Column: Leave it at 1 (the ContactID field) On the Format Tab: Column Count: 2 Column Widths: 0";2.5"    (this makes the first column invisible; you can play with the second value as needed) This will allow the user to simply choose a name from the combobox and the ContactID will be inserted into the Studies table. HTH, Toby ----- Original Message ----- From: "Joe" <[EMAIL PROTECTED]> To: <AccessDevelopers@yahoogroups.com> Sent: Monday, December 05, 2005 1:59 PM Subject: [AccessDevelopers] Re: Drop Down to Select Contact > The thing with this is I don't want them to be able to edit the > contact info just select from a drop down.  Then all that contact > info is associated to the study.  So do a combine of the First name - >  Last name and have that as a drop down that is linked to the > contacts.  I will have a seperate form to enter in contact info. > Can I still do that the way you ar talking about  Alienwebmaster? > > > --- In AccessDevelopers@yahoogroups.com, "Richard" <[EMAIL PROTECTED]> > wrote: > > > > It sounds like you already have the relationships developed.  Use > the > > FORM WIZARD in the forms tab of the main DB window, and select > fields > > from BOTH TABLES that you want to see.  The wizard does a very > nice job > > of creating a main form (with the contact information) and a sub > form > > (with the studies info).  Hope this helps! > > > > Alienwebmaster > > > > > > --- In AccessDevelopers@yahoogroups.com, "Joe" <[EMAIL PROTECTED]> > > wrote: > > > > > > I am sorry this is probabally a newbie question.  I have two > tables a > > > contact table and a study table.  I set up my form to view Study > > > details information.  I have a relationship of a One (Contact) > to > > Many > > > (Studies)  I want to have a drop down so I can select one > Contact and > > > associate this to a study.  How would I go about doing this? > > > > > > Thanks in advance > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > > > > > > Yahoo! Personals Let fate take it's course directly to your email. See who's waiting for you Yahoo! Personals Please zip all files prior to uploading to Files section. YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.