combining 10 sub forms in one main form - Pls Help

[Dasun]

Hey guys,

I have a little bit of knowledge about Access. I learnt everything by myself from practice and I have no exact idea of how tables are mapped in relations and other tables connecting stuff even though I tried hard to understand.

I have an Access form bound to two fields called ID number and Received Date.

those are the main two fields I need to manage this Patient Management System.

And this form contains another 10 subforms which used to store laboratory test results and other procedure and details of the patient.

But all of these tables are connected using either ID or Received Date of the Patient where each record of every table must be able to identify each record uniquely using those two main keys of the patient.....

The 10 subforms are bound to 10 separate tables where I want each of these subforms should be arranged in one main form for data entry.

I have several questions

1) I set the link child and master fields to ID in each subform. And I want to put a save record button at the end of the main subform.so when I'm done entering data into every subform and when save button is clicked(which is in the main form) all data should be saved in tables. But at the moment, when I click the save button it does not work that way

2) It is a huge waste of time to keep entering ID number to each of 10 subforms. I need to know if I can find a way for the ID number to be automatically displayed in every text box in each subform where ID text box is located.




My heartfelt Gratitude will be gone to all the supporters.
love you all

[June7]

If these tables are properly related and the Master/Child links are set to the primary/foreign keys of the tables, then the value should automatically save to the child table when data is entered. If you want to make the db available by attaching to post or uploading to fileshare site such as box.net and post link, I will look as. Make a copy, remove confidential data, run Compact & Repair, put in Windows zip file if too big.

[Dasun]

Tnx a loy June7

I uloaded a txt file.Itll have the link to download it. Thr is no exact data in it at the moment cuz Im develoing it.(and you too now).

cheers buddy!

[Dasun]

Data Entry form is the main form that Im stuggling with. I think i set the relationships as much as i can

ID field is the main primary key in all tables - it has text data type in a special input mask

Received date is also has to be linked to some tabes for searching purposes


Pls ask me if u hav any questions. Im waiting for you help

tnx again

all the suforms that start with Prefix 'DE' is for data entry.

other subforms are exact copies of them for "search form" that I wud make later.

[June7]

Size of the downloaded db was 12.48MB. I ran Compact & Repair and the size reduced to 1.7MB. Always run C&R before upload and should also run it periodically. Editing db design causes size to increase.

The table relationships are all set as one-to-one. This is unusual but might be suited to your situation. Several tables have identical structure. This is not conventional but workable.

What do you mean by 'Received date is also has to be linked to some tabes for searching purposes'? None of the date fields are set as keys. Don't think I would use a date field as a key. You can use date as filter criteria.

The project opens with Search Form open. I don't really understand what this form is for.

What specific issue are you having with the DataEntry form? Aside from the fact that it is huge and can't see control labels in form view. Appears you used the wizard to create forms. I never use the wizards. They do things I don't like and make it harder to edit design. I manually build with the designers instead.

You might look at use of tab control to build a more compact structure. Can put subforms on tabs.

[Dasun]

Tnx for the help mate,

Hmmm actually the form tha I m having the prob lem is Data Entry form, As you said all of the relationships are not 1 to 1. I found some that shoud be 1 to many.

any how I think I have figured out the thkng at the moment with your generous help.

And can u pls let me know if u have any idea to automatically select ID field in all subforms as the on change event of the ID field.

I mean some VB code. Becuz its waste of time to enter ID field to every field.

tnx very much mate.

[Dasun]

June7 , what do you mean by Tab control. Can u expalin what is happening with them and how can it be used in a suitable way.

[June7]

Google: Access tab control

Here is one tutorial: http://www.techonthenet.com/access/t...bcontrol01.php

[June7]

There is no need to enter the ID_No in each of the subforms. As soon as entry is made into any other field, the child record will be initiated and the foreign key field will populate with the ID_No entered into the textbox on main form. In fact, the subform ID_No controls should be locked against data entry or not even displayed.

I did some resizing of subforms and their controls to get the labels to show.

[Dasun]

So you mean when I enter ID_No in Baseline1 table, and when I click the add record button, all the tables will have the ID-No which I entered right???

If this is the case i think it is ok to delete the ID-No field text boxes in all subforms except in Data Entry from isn't it

[June7]

Not necessarily ALL of the child tables will have the ID_No. Only those where you add other data. A child record will automatically have the ID_No when you enter other value into the subform. So, yes, showing the ID controls not needed.

The reason for related tables is so that you don't have big table with lots of empty fields. Records are created in child tables only when there is data.

Add Record button might not even be necessary. A record is committed to table when moving to another record or form is closed.

You might also want to change the Tab Order of the controls. The main form ID_No should be 0 in the Tab Order property.

Also, make sure the ID_No field in Baseline1 table is set to not allow duplicates.

Also, just hit me that for the table relationships that are one-to-one, a subform in DataSheet or Continuous view is not appropriate because there can be only one child record.

[Dasun]

Tnx for the help June 7,

up to now I have encountered these problems

My PK is ID which is autonumber
I have uniqe key called ID_No (which is ma serial nbr for records)

All the saubform have this "Intermediate" called relationship in them. I cud not create it as 'one to many' i dnt know why cuz it did not allow me to do when i ws creating relationships. I hope Intermediate relationship does the same task as One to Many does.
and I have already set the link child and master link fields as ID in every one(Making all tables have one to many relationsships{technically :- "INTERMEDIATE" } with the table which main form is bound)

So once the main form is given the ID-No input, as soon I go out of the text box(after_insert), all other subforms displays that value...... but! when I click the add record button and the subform tables do not have the record(it is not saved within the tables), Only the Record in the main form is only saved.

My problem is why it is displaying the ID_No value in subforms but why it is not saving in subform tables

I forgot to say that I have set Data Entry feature turned off and Allow Edits field is ON

SOmebody help pls.....

[June7]

It is not INTERMEDIATE, it is INDETERMINATE. This is not good. Probably have the wrong fields linked.

Do you want to make the revised project available? Replace the upload and I will download again.