MS Access Mixed points

[m9jiihmr@gmail.com]

Dear Sir
Greetings.
I have some MS Access points. I am using MS ACCESS 2010.

I have two tables. One is Group and another is MasterEmail
Table Group has following fields
Field Name Data Type
Group Text(Primary key, There can be only one type of group )
Group_description Text
GroupID AutoNumber
Table MasterEmail has following fields.
Group Text (This field is Lookup field as combo box, which uses the value list exist in Group field group table.
Email Text
Email_uid AutoNumber
1.
I want to make relationship between these two tables using Group. What I did is, I dragged Group field from Group table to Group field in MasterEmail Table. System is showing One to Many relationship that is right. But I want to enforce referential integrity between these two tables using Group field which is common in both tables.
How this can be done. What are the ways of doing it. If there is any suggestion of changing structure of table, I may go for it.

2.
I want to make a form using combo box, which is linked to Group field in MasterEmail Table. What is want, when user click on any of the Group from combo box, selected record in that Group should come up on screen.
I can do this by writing
Select * from MasterEmail where Group=”What ever”;
I want this functionality using form, that mean, user select the Group, from the combo box, and record from MasterEmail Table displays.
3.
I have a form using MasterEmail table, Which asks first Group name and then Email.
What I want is, I want to get Group field pre filled in the form, and prefilled should be from just previous record’s Group field value. ( Something like persistent ).
4.
In the MasterEmail Table, there is field named Email, that’s has email addresses.
How to write a query or program that identify valid email id ( How to identify Valid Email ID)


Thanks and Regards
Manoj

 

[Pat Hartman]

Welcome aboard
Let's start with the group table. You have defined a text field as the primary key and yet you have an ID field defined as an autonumber. The only reason to use an autonumber field is if you need to use it as the primary key. Using the group field as the PK is not wrong but I rarely do it because I've been burned too many times by changes and you don't ever want to be in the position of having to change the primary key since that ripples to all your other related tables, queries, forms, reports, and code. So, I almost always use an autonumber field as the PK and that is what all relationships will be built on. To enforce the business rule that the group field must be unique, I would add a unique index.

Master email doesn't seem to have a PK defined. If you created this table to allow a group to have multiple emails, then this table needs an autonumber PK and it will use GroupID (long integer) as the FK to the GroupID (autonumber) PK in the group table. If a group has only a single email address then it belongs in the group table rather than a separate table.

1. Make the relationship in the relationship window on GroupID in the MasterEmail table to GroupID in the Group table. Check the box that says to enforce RI and you should also be safe in checking the Cascade Delete box. That way if you delete a group, the database engine will delete the associated email records. If you don't check Cascade Delete, you will not be allowed to delete group if there are any related records in email. You would need to delete all the related email records manually before the db engine would allow you to delete a group.
2. I'm confused by your request. If you use a main form for group and a subform for email, you don't need any code. If you want the email table to be the "master" table in a form, use a query that joins the email table to the group table. This lets you select columns from both tables. Set the locked property to Yes for any columns that come from the group table. You don't want anyone to accidentally change them on this form. As soon as you choose a group from the combo, Access will automagically populate all the columns bound to the group table. Again, no code is required.
3. Previous is a nebulous term when it comes to relational databases. At any point in time any record could preceed any other. If you have a number of entries to make for a group, it would be best to use a form/subform with group as the form and email as the subform.
4. I don't have code handy. You could use Instr() to determine if there is an @ sign. You would also need to search for spaces or other invalid characters.

And last but not least, do not use lookups on tables. They cause nothing but trouble in queries and code. Just use combos on your forms.