Child Form - Revisted? 1

[khwaja]

I know I have raised this question before but I am still without a satisfactory resolution.


I need to be able to create a child form (as opposed to sub form) which opens in the second window and allows you to add a record linked to the main form. This works fine within the query but not on the form even when a one to many relationship is established. To get around this problem, so far I have been managing by showing an invisble FK on the child form and creating a sub form which is spread on full page so that the link relationships are created allow addition of records. But I believe there must be a better way of doing things when you want to create a child form without having to resort to hidden FK method. To give you an idea of my requirement, the database I have is a collection of of various projects and has two tables - tblProjects and tblPOs. While maintaining a project, I raise several purchase orders against one project. So I have created a command button to take me to the PO screen. I would love to be able to maintain my projects in the main form and when necessary

 

[Darrylles]

Hiya,

Hope I've got this right:

1 to many relationship, main form is the 1, you want to add a 'many' record - so you open another form displaying a blank 'many' record (which should be linked to the 1 record when saved).

If this is the case, then I can't see where the sub-form comes in - is it on the child form? If so why?

Also, there seems to be a contradiction, initially you say that you 'need to be able to create a child form', and at the end, you say 'I would love to maintain my projects in the main form'.

Hope my first assumption is correct.

If so, what is wrong with populating the child form fk with the main form pk, and saving the child record?
Are details of the many record displayed on the parent form?
If so, refresh the main form before exiting the child form.

This is slightly confusing, a little more detail would help.

Regards,

Darrylle






"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[khwaja]

Sorry for not being too clear. I have a tbl Projects which is maintained through the main form. Projects ID is the PK. Occasionally, we raise purchase orders relevant to a project. So if I am working on Project A on the main screen, I have a command button which I use to create a Purchase Order form using pop up form specific to the project I was working on. In this purchase order part of project information is also filled in automatically. After filling in PO related info, I print a PO. There are a number of Purchase Orders I raised for this project and these are saved I as create a new one. So there is I to M relationship so that each project may have a number of purchase orders issued in its lifetime. This sums up my requirement.

Under normal circumstances, when you have 1 to M relationship, form - sub form solution when you have one screen composed of main and sub form is ideal. Updating FK is not a problem, which occurs as soon as you move to next record.

However, when you have a secondary/child form linked to the main form (eg the Purchase Order form), I am finding it difficult to save purchase orders, as perhaps FK is not called from the main form. And I keep getting error message. I used the following technique and you are welcome to identify any mistakes I might be making.

I based the main form on tblProjects and the child form on a query based on tbl Projets and tblPO wherein I made sure that ProjectsID from tbl PO is added so that it is updated. So when I tried adding a record within the query, it worked fine and I could see FK being updated. But when I used this query as the basis for my child form, I kept getting error message. After various trials, I gave up and managed to get around this problem by creating a brand new form based on tblProjects with the ProjectID as the only field in this form and its visible property set to 'No'. Then I added the purchase order form as a sub form to this so that master/child relationships are established. And when I called this new form using the command button from the main form, it worked but then I ended up with two navigator buttons in the PO scree, which may be confusing.

I am sure that the process I used is not the right one. I need to be able to call and save a ‘many’ record on a child form without having to create main-sub within the child form.

I hope it is clear now and look forward receiving some feedback from you.

Regards

 

[Darrylles]

Hi khwja,

OK - I think that I assumed right.

I've produced a simple test app. to be absolutely sure that it works and it does.

2 tables: County(1) and Town(many).
County Town
------ ----
pk pk
County Town
County_fk

2 forms : frmCounty (displays 1 County at a time, with record navigation to scroll through County records).
frmTown displays a blank Town record on opening.

frmTown is called from the frmCounty form via a 'Add Town' button which basically opens frmTown with a new record.
frmTown has a 'Save' button ,which when clicked - populates the 'County_fk' field (by referring to the frmCounty form and getting it's pk), saves the record and closes the form.

The Town record is added and is related to the correct County.

I could have pasted ALL of the details here, but I first wanted to make sure this is what you intended. In any case, it will be easier if you have a Yahoo or MSN id, or email address - I can then get the small app. to you.

I'm 'darrylles' at both Yahoo and MSN (darrylles@yahoo.co.uk / darrylles@hotmail.com).

Let me know what MS Access version you have (97 / 2000).

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[khwaja]

Thanks so much. This is precisely what I am looking for. I have sent you an email.

Cheers

 

[khwaja]

Darrylle

Thank you once again for sending me the sample DB. Unfortunately, even copying your technique did not help.

I am sorry for bothering you but would it be possible to kindly have a look on the DB I created based on your sample. I am still unable to update the fk.

This is a project database which should allow people to issue a transmittal document (kind of a memo) against each project as a legal record of drawings issued. So I have a Project A, this may have any number of drawings issued and each of these drawings transmittals is recorded in the form of memo. As you can see, when I click to open the memo button and create a new memo, it does not update the transmittalID. In a couple of records I changed this myself. At the end of the day, not only we should have memos linked to respective projects record but also should be able to issue more memos for the same project.

I am mailing you the DB on your hotmail account.

I hope it is clear but if not please let me know.

Cheers

 

[Darrylles]

Hi khwaja,

Got the db. My example was different to yours in that you are a adding a 3rd level sub-record - mine added a record to a 2nd level subform.

When your 3rd level record is saved, you see no change on the calling form (because there are no 3rd level fields on your calling form).

Anyway, I had the same prob. as you accessing the id of the subform. I've worked around that by using a global variable.

When you first open the db, you may get an error - due to the conversion from my 2000 to '97. Follow the instructions and all should be ok.
When you get in there, go to the module and read on from there.

ATB & Regards

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[khwaja]

I am so very grateful for taking the time to look at the db and resolving the issue too. I am impressed by your description of the problem which was enlightening for me as I always thought second level FK can be added automatically. But based on your solution, what I gather is that it is important for the user to essentially save the record before moving on to next.

Regards

 

[Darrylles]

Hiya,

Stop being grateful khwaja - please. I'm gaining knowledge from helping you.

Problem here is, that after looking at what you sent me, I'm confused.
You display a blank template (in the frmTransMemo form) - it contains no info. apart from the date, and the link to the transmittal table.
The 'memo' form contains exactly the same info. for every record because it's a graphic.

If you want the frmTransMemo to just save without user intervention, then move the 2 lines of code out of the SAVE button into the <On Open> event of frmTransMemo.

This will update, save, and close the form.

Is this what you want?

If not, we need to clarify exactly what these forms are supposed to do.

Kind regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot;

 

[khwaja]

Darrylle

Despite emplyong your methodology, I am still struggling with in terms of saving a printing a child record. I wonder if I can email you db?

Cheers