unable to edit data in query with complex relations !!!

[momani]

helloo



I will try to explaine the problem that I have faced....
this is my first time designing a database .
I am using MS access to design a DB, and I have constructed the tables with the relations btween them .. some tables have "parallel" relations mainly "one to many".

now I can not edit the records in a query when I includ with parallel relations.

*I mean by parallel that two child tables taking their reference from a third parent table
thanks in advance

[Rainlover]

Your opening question Screams out that your relationships are incorrect for some reason.

You could try to explain every table and its joins data type etc. Or it may be easier to take a screen dump of your relationships window. Just do your best to make it as clear as possible.

[momani]

I hope it is more clear now :P
the problem starts when i inserts boths tblanswerRef and tblQusetion
thanks in advance for you help

[Rainlover]

I assume you are using Autonumber as your Primary Key.

It would help if you maintained some naming conventions. Make sure both the Primary and Foreign Keys have ID after them. Usually people would use exactly the same name for both.

When you do this the Primary key will go Bold

You should when using AutoNumber have only one primary key and this should never be seen by the users.

There are a few more problems but what I have told you may get you out of trouble. I you want to fix the others we can also do that.

It is 1:00 AM here. I have to get some sleep. I will look again tomorrow to see how far you have progressed.

Someone else may jump in and assist while I am away.

[Dal Jeanis]

1) I don't see anything inherent in the relationships you show that would prevent updating on any reasonable query. So my first quess is that your query is (accidentally) unreasonable.

2) Something looks wrong about the relationship (none) between the questions and the answers. You are wanting to track each answer to a specific question, right? So, with the above database, if you build a query right now that JOINS tblQuestion to tblSender to tblAnswers (via tblReceiver) on the common key of SenderRef, that query links ALL the questions for a particular SenderRef to ALL the answers for that SenderRef, then OF COURSE you can't update it. Each question is being returned mutliple times, linked to each answer, also multiple times. If a SenderRef has four questions and four answers, you are getting back sixteen rows of data.

3) So, establish some kind of natural link between the tblQuestion and tblAnswer, and that issue should go away. I would expect that you want the primary key of the Question on the Answer record as a foreign key.

4) I'd also expect that the SenderRef records would be directly related to a Plant or Project. Maybe not always, but usually. You might want to do a little use case analysis to see how you want to store that info.

[Rainlover]

How is it going. Has the problem been solved.

Posting a copy of your Database may be the best thing to do. That way we cab have a look at an actual query that you are creating.

[momani]

thank you very much
I have the relations between tables and I have changed the queries them self and every thing is going so well .thanks again.

now I have another problem :P
actually I have made a main form which not linked to any table (main form) with a comobox in it (say : country),and I have included a sub form also ,and it is linked to a table (say: project).
when the combo box value is updated the sub forms reads this value and returns the records that meet the craiteria ( the projects in that country)... and this is good :P

but when i want to add new project, it does not link it automatically to the country (i.e: when the sub form shows the projects in France I still can add a project and link it to Germany in the sub form )


what should I do to link the new project directly to the chosen country ?!!

many thanks in advance

[Dal Jeanis]

You're welcome - please close threads that have been answered, and post your new questions in a new thread.

Since we're here, though, here's your answer.

Most controls have a default setting (properties, data tab). You should be able to set the combobox default to the value you want.There are a dozen ways to do this, any of which will work.. .some of which would be frowned upon.

METHOD 3 below, if you can get it to work, would be the simplest and most preferred.

Method 1 - if you are using Access 2010 or later, you could have your main form combo box, in its CLICK event, set a temp variable, and in the subform set the combo box default value to be = that temp variable. In Access 2003 or before, you can do the same thing with a public/global variable, but you have to write a public function to retrieve that variable if you want to use it in SQL. I don't think the control's default setting would hit against the Jet engine, but I'd have to test that before I was sure.

Method 2 - if you have set up your subform with "Link Child Fields" and "Link Master Fields" (the usual way), then you should be able to access that information. Left-click the link control on the main form (not the subform itself) and see if you are passing the country in those fields. If so, you should be able to set the control using the child-form's "Link Child Fields". Nope, wrong, that's probably the field you are trying to set! Actually, it's worth looking at this page http://www.fmsinc.com/MicrosoftAcces...ink-Fields.asp and this page http://www.fmsinc.com/microsoftacces...edsubforms.asp to make sure you have your form/subform relationship set up correctly.


Method 3 - you can also refer, in your subform combobox default setting, to the control's form's parent's combobox control's value.

On this page http://support.microsoft.com/kb/113352 I found the following information :

To refer to the value of a control on the parent (main) form from a control on a subform, use the Parent property. For example, the following expression entered in a control on a subform refers to the Customer ID field on the parent form.

   =Parent![Customer ID]

So assuming your parent form's Country combo box is called cmbCountry, you should be able to set the default property of your subform's combobox control to the following. NOTE - The brackets around the control name are only mandatory if there are spaces in the control name.

   =Me.Parent![cmbCountry]

Despite what the Microsoft page said, I've added "Me." in front of the code above because it is the recommended method of address on the Access MVP page http://access.mvps.org/access/forms/frm0031.htm, which I always treat as the Bible.

If you can't get that to work, there's another syntax to reach "the control's form's parent's combobox control's value", and this is how you should think about it when you are trying to remember the syntax (if you can't find that Access MVP page). Basically, start with "Me." and work through each of the steps that leads VB to the specific control you are looking for.

[Rainlover]

thank you very much
I have the relations between tables and I have changed the queries them self and every thing is going so well .thanks again.

now I have another problem :P
actually I have made a main form which not linked to any table (main form) with a comobox in it (say : country),and I have included a sub form also ,and it is linked to a table (say: project).
when the combo box value is updated the sub forms reads this value and returns the records that meet the craiteria ( the projects in that country)... and this is good :P

but when i want to add new project, it does not link it automatically to the country (i.e: when the sub form shows the projects in France I still can add a project and link it to Germany in the sub form )


what should I do to link the new project directly to the chosen country ?!!

many thanks in advance

To point something out that many do not realise.

Relationships are to establish a Join between two Tables and to enforce Referential Integrity. This ensures that you can't create a Record unless it matches the other Table. IE The Foreign Key must match one of the values in the Primary Key.

Then there are joins. A default can be set up in the Relationships window. Usually I leave this alone and just accept the default. Joins can be changed in the query to suit your requirements. This ability to change Joins can be very useful as you will discover.

PLEASE note that with Joins I am referring to changing things like "All Records that Match" or :All Records in one Table and only those that match in the other"

I do not recommend changing the fields that are Joined. If you have to do this then something is wrong with your design.

[Rainlover]

With regard to your second problem.

Why have you chosen to Create a Form Sub Form setup when all you have in the main form is a Combo Box.

I would think that all you need is a Form and put the Combo in the Form's header.

[momani]

hi again ... thanks alot for your fast responce

actually the main form is not linked to any table/query and it contains only a combo box which is now linked to the sub form,


what I am trying to do now is entring a new project directly to the sub form with out the need to enter the country that is already selected in the combobox,
(if I do this now I will have a prject that is not linked to any coutry) ...!!!

the idea of having the combobox in the forms header sounds nice I am tring to apply it but it still cannot link the newly added project to the selected country !!!!

[Rainlover]

My previous post I believe is the way to go.

One form bound to the Table.

Search "Not on List". I would think that you should get a lot of help there.

[momani]

yes yes I got it now and every thing is going well... thanks alot

[Rainlover]

Good to hear.

Best of wishes for the project.