FORM WITH SUBFORM(DATASHET) 1

[kaniz]

How can I solve this problem. I have 2 tables linked by id.
Link is one to many relantionship. I created the form and the subform using the form wizard. The suborm is datasheet.
What is happenning righ now is that sometimes the record on on the main table won't have have any reocord to link for example

main form
id 1
last name Smith
first name John

subform

id child name 1
1 Eagle Beaver
1 Blue Jay

if a person does not have any children we still want it to be stored, but it stores the record on the main table but there is nothing on sub table because there are no children. How do I make so that if there are no children just to store the record

thank you for any suggestions

 

[ghubbell]

Hi Kaniz, If I understand you correctly you are in fear that by entering a record in the main form and nothing in the subform, you will end up with an inability to store the mainforms record in the mainform's table? Well if you don't enter anything in the subform, there won't be anything in the subforms table. But you should be able to enter records in the mainform without consequence. Maybe you could check the links between the two forms: The Child link should be the ID number from the table used for your subform. The Master (or parent) should be the ID number from the table used for your main form. You can check these by placing the main form in design view and clicking once on the subform's frame. Then select the properties button on the toolbar to show the properties dialog. Select the data tab and look at "link child fields". click on the "field" and you will see a "..." button on the right. click this and you will see an explanation of the link. Gord
ghubbell@total.net

 

[kaniz]

Hello Gord, thank you for your response. I am not sure whether this should be a one to many relantionship of just one to 1. When I designed the tables I designed as 1 to many. Enforce referencial integrity, then I deleted the relantionship because I need to make changes to the tables and access required to delete the relantionship. Now I am trying to set the relantionship back and enforce referencial integrity and access said that it can't do that

 

[ghubbell]

Hi Kaniz,
This should be a one to many relationship.
You have a blank record or a "wrong number" in the child table.

Use the "find unmatched record" query wizard to find the record in your sub table that does not have a "parent" in the main table. Once this record is corrected you will be able to relink the tables. Gord
ghubbell@total.net

 

[kaniz]

Thank you Gord, this seem to solve the problem. What is the best way to avoid this situation where there are unmatched records. I am not sure if the tables were unlink at some point in time therefore I have many records in the child table with no parents. What would you suggest to avoid this situation from happenning again. The id of my main table is autonumber and the id in the child table is number. Is this the best way to define this tables. thanks again Gord

 

[ghubbell]

Hi Kaniz, and of course, you're welcome! By connecting your main table Id to your sub table ID in the relationships window: right click on the database window to see the relationships, show your two tables, and if no relationship exists click and drag the id from your main, and drop to the ID of your sub. A dialog will pop up and you would like at minimum: enforce referential integrity. The other two options of cascade update or delete are your choices, and based on how you choose to handle your data. Oh. If the join line is unsucessful, read the message it says carefully. You may still have a "child" with no "parent" or another small problem, but in any case, you need that line! Gord
ghubbell@total.net

 

[kaniz]

Thanks again Gord. got it to work. one last question. I been asked to set user rights on this database because of the problems I had before records not matching on the parent table. Only one person will be doing the data entry. Everyone else will be allow to print. Can acces handle security on a database.

 

[ghubbell]

You're welcome Kaniz! Sure Access can. First thing to do is make a backup copy of your Db just in case anything goes wrong, then Tools-Security-"User Level Security Wizard". Go slowly and read carefully all the questions. This wizard can set up some pretty decent security for you and even if it takes one or two trys, (always keeping your back up!) I'm sure you will get it to run just as you would like. Gord
ghubbell@total.net