Linked Forms 1

[khwaja]

I have a projects table and I manage the data input through a form. Along the way came the need for us to raise purchase orders against each project and keep a record of that too. As many purchases are associated with each project, I created an additional table (tblPO) to hold PO data and added a FK based on tblProjects PK (ProjectsID).

I created a query based on these two tables, added fields from tblPO and picked up FK ProjectID from tbl PO. I also selected couple of field from main projects table in order to show project name and location on the purchase order. It works well in the query and when I add details in PO related fields, I can see the FK is updated instantly. But when I linked the Purchase Order form based on this query to main projects forms using a command button, my efforts to create a record were not successful. I could see the Project ID field in the PO form was showing a zero and eventually I got an error message that MS Access engine cannot find a record in the table 'Projects with key matching field ProjectID. I tried both types of joins (inner and outer) and there is no luck.

Will appreciate if someone could help.

Regards

 

[Quehay]

(Forgive me if I'm telling you stuff that you already know)

Make a distinction between a query that's used for informational purposes (shows parent fields) and one that's for supplying a form with necessary data. For the form just use the parent ID / FK field.

You should have an Orders table with:

OrderID
ProjectID (is this required?)
Detail1
Detail2 etc.

Once you've created this table, use the Relationships GUI tool to create a 1 > M relationship between Projects and Orders, and set referential integrity on (this won't require that there be a Project ID, however, just ensure that the ProjectID referenced indeed lives in the Projects table, and that no Project may be deleted if it has child records in Orders (or at least it will require a Cascade delete and warning to that effect).

You can then drag this table into the form and the subform will be created with the proper LinkParent and LinkChild properties.

If this is a retrofit for existing orders be sure to update all the existing orders with the proper ProjectID. Also, if you've created a Number field (without using lookup Wizard) that references the Project ID it will have a default of 0 if you don't get rid of this (a very stupid Access behavior).

 

[khwaja]

Thank you very much for your response.

I just want to clarify that there are:

 No pre existing orders, so I don’t need to worry about updating these
 I don’t want to have Purchase Order appear as a sub form. I need user to be able to access this form from the Projects screen using a command button. User may then fill in the info as relevant to the project with the proviso that user may decide to raise more than one PO in the same session while linked to the relevant project. (Hence one to may relationship).
 I have set up my tables as advised and I do have Projects ID as the FK in tblPurchase Orders to make things easier. This FK did in fact have ‘0’ as the default value, which I removed.
 I am pretty clear about the relationships and I set these up as such in GUI screen. .I am not too clear whether I should have an inner or outer join but I tried both.

In short, I have a 1-M relationship between two tables, recognised through a query. I created a main form based on projects table and the other based on the query with projects and orders linked. Both forms are connected through a command button. I am able to add any record in the ‘many’ side in a query but not in the form. So issue is whether there is any code I need to have in the ‘many’ form to allow me to add more than one orders.

What I understand from you is that if I am calling a second form related to one project, I should not have any other info within the purchase order relevant to the project itself. I therefore removed all project related info and I am still far from adding a record.

Could you kindly review your response based on these clarifications?

 

[Quehay]

You've got a child form that isn't a subform. When you have a subform Access takes care of providing the parent/fkey value behind the scenes.

With an independent child form you need to provide the foreign key overtly. Put a textbox on the form with the default value: forms!frmTheParentForm![FKEY Field]

*After testing you'll want to make this control invisible.

This form should be modal so that no changes in parent form record may be made while this child form is open (you don't want to change parent records while operating on child records). To make it open in modal set the modal property to TRUE in the form AND in the DoCmd.OpenForm ,,,acDialogue manner as well (this is the last possible argument in the OpenForm method). Make sure that the query behind this form actually selects both the FKey field in the child table (orders) and the referenced field from the parent (Projects), or else you won't be able to add new records.

Hope this gets you going but post again if not...

 

[khwaja]

I am very grateful for your quick responses. Thank you for correcting my terminology. I should have known that this was an independent child form.

I tried using text box technique with the default value, which did in fact bring up the PO with the Project ID but it still would not let me save a new record. Got the same message I mentioned earlier. Maybe I am making some silly mistake but to be sure I will describe it once again that the underlying both tables are linked with an outer join with all records from projects and only those records from orders which match. In the query I have created based on these two tables, I have added the entire orders table (asterisk) in the grid and nothing else. In the stand alone orders form I am picking up all orders data plus the text field you advised to relate to the projects form. I have added this text to 'default value property: [Forms]![Projects]![ProjectID]. There is nothing in the Control Source property, which I understand is to be left blank.

My tables and fields are as under:
tblProjects: ProjectID (PK), detail 1, detail 2...
tblPurchase orders: POID (PK), ProjectID(FK), Detail1, Detail 2,,..

Any idea if I am making mistake somewhere. I am really frustrated.

Kind regards

 

[Quehay]

Make sure that the query that's behind the Orders form has the tblProjects.ProjectID field in it as well--without this you won't be able to add new records (I think that this what's giving you trouble here...).

Make the join an equi-join. There should be no records that don't have a Project ID matching a record in the Projects table.

Good luck!