If CHECK BOX is TRUE then DATE FIELD ENABLED

[YNWA]

I would like it linked so everything ties into each other.

patient table links to mother table and death table

death table not sure if it needs to link to mother table as the death relates to the patient and not the mother.

mother table links to lifestyles.

Do I need a FK of patientID in every table to link it all back to the master table of patient or will having the following be ok...

patient ID
-----
motherID
patientID
-----
deathID
patientID
-----
lifestyleID
motherID

Then you can link togehter in a query to pull results out?

 

[YNWA]

Correct as that's the only way you can set the criteria.

Remember, this is only filtering the records so for new records you will have to manually input the PatientID and/or MotherID. Try using the Default Value property of the control on the subform to refer to the textbox on the Patient and/or Mother subforms.

Does my record source in subforms need to be a query? Or will table be sufficient? Whats the difference between using the two?

 

[vbaInet]

Setting a criteria can only be done via a query. So yes it has to be a query. That's where you put the criteria mentioned in my other post.

Re your other post:

Patient subform to Mother subform
Mother subform to Death subform OR Patient and Mother subforms to Death form
Mother subform to Lifestyle subform

Those are the links.

 

[YNWA]

Sorry mate.

I'm getting confused now.

Could you look at the following and throw the code in for me as I'm not 100% on what to do or if what I am doing is right.

Do the set up relationships, form links etc... look ok?

 

[vbaInet]

Keep at it and you will get it. If I felt this was a very difficult task then I would ask you to post your db.

First thing you do is create three queries, one for Patient table, Mother table and Death table.

Once you've done that, set the Record Source of each of your subforms to each of those queries.

Let me know when that's done and I will guide you on the next step.

 

[YNWA]

Ok thanks. I will crack on with it Monday as I am leaving work now.

What are the benefit of linking my forms to queries as apposed to tables?

Should I be doing this for database forms I create?

Do the queries just pull every field from the relevant table?

CHeers for your help mate, speak to you monday. Have a good weekend.

 

[vbaInet]

If I remember correctly I think one of the problems you would have by setting your form to the table directly is record locking in a multi-user environment. There are also performance benefits too.

As I've already mentioned, you need to set criteria to the record source of your subform and without a query you can't set criteria. Think about it, can you set criteria in a table? The answer would be no. So you need a query so you can set its criteria to one of the IDs on another subform. Getting the gist of it now?

The queries will pull all the records from the table for each field that you included. But for the purpose of linking your subform you need to limit it somehow, that's where under one of the ID fields you set it to a control on the other subform under the Criteria.

Have a good one too.

 

[YNWA]

If I remember correctly I think one of the problems you would have by setting your form to the table directly is record locking in a multi-user environment. There are also performance benefits too.

As I've already mentioned, you need to set criteria to the record source of your subform and without a query you can't set criteria. Think about it, can you set criteria in a table? The answer would be no. So you need a query so you can set its criteria to one of the IDs on another subform. Getting the gist of it now?

The queries will pull all the records from the table for each field that you included. But for the purpose of linking your subform you need to limit it somehow, that's where under one of the ID fields you set it to a control on the other subform under the Criteria.

Have a good one too.

I have now created a query for each of the tables, each one pulling the same fields from table to query. Is this correct?

Can you define what you mean by "criteria"? I have slept and forgotten the line I was on?

Do you mean criteria where by all forms link to the main form ID?

 

[vbaInet]

Just to clarify, is your main form supposed to Patients? If that's the case and if the Patients form is a single form (i.e. not datasheet or continuous) then you don't need a subform, but you still need a query, which you've done.

I would have thought you've used Queries before? In a query you set criteria in the Criteria row. That's what I meant by criteria.

Now you've got the queries, set the RECORD SOURCE property for each form to each of the queries. For your first form, which I think is Patients, set the main form to the Patients query.

 

[YNWA]

In the database attached previously, I have 4 forms.

main form = frmRecords
death form = subFrmDeathInfo
mother form = subFrmMother
lifestyles form = subFrmLifestyle

frmRecords ID linked to subFrmDeathInfo ID
frmRecords ID linked to subFrmMother ID

subFrmMother ID needs to be linked to subFrmLifestyle ID but I can not get this linked either.

Previously I was usng query builder in the record source but now I have created 4 queries and put the query names into each form/sub forms record source.

See what I have attached here, does this look ok?

Do you know how to get mother subform linked to lifestyles subform?

 

[vbaInet]

It's a step by step process. I've just given you two steps already. I'm sure you're thinking this is supposed to be easy? What I'm explaining to you is a workaround, not a standard Access feature.

The other step is to Open the Lifestyle query in design view, under the Criteria cell for MotherID put this:

[Forms]![frmMain]![ID]

For it to be synchronised when you move through records on the Mother subform, you would need to requery the lifestyle subform on the Mother's On Current event.

Me.Parent.nameOfLifestyleSubform.Form.Requery

 

[YNWA]

Cheers mate... I found this solution at weekend, is this same thing...

Fortunately, there's a simple workaround. Create an intermediate text box on the main form for the LinkMaster value. This references the current record in the master subform, then the detail subform can link to that value. By making this control invisible, your users won't see this link.
To reference a value in a subform, use the following syntax for the text box's Control Source property:

=[subFormName].[Form]![ControlName]
​

where subFormName is the name of your master subform control, and ControlName is the name of your control that the detail records are filtered.
In the sample database, this becomes:

=[subMaster].[Form]![txtID]
​

Now that the master value is exposed, we call this control 'txtMasterID' and use it as the Link Master Field for the second subform.

 

[vbaInet]

Yes, that's another way. Good find. Does that work for you?

 

[YNWA]

Yes, that's another way. Good find. Does that work for you?

Aint tried either yet.

Which is best option in your opinion?

 

[vbaInet]

You can use either. I think for simplicity use the option you found and with that method you don't need to use Me.Requery because that's handled by the Link Master/Child properties.

Test it out and try adding, deleting and editting records on the Mother and Lifestyle subforms to see the effects.