Conecting table main form and sub form

[bceo]

I have had some experience working with Access 2003, but only with simple standalone databases, which I designed using the Access wizards. I am now working on a database that uses sub forms. My problem is this: I have a main form called frmProblems with a sub form called frmInvolved, which is activated via a command button on the main form. The sub form is not always needed, but when I used it I want it to open up as a new record connected to the table tblProblems (I have a table called tblInvolved where the input goes when entered vis the sub form). I also wish to be able to print the record from the main form and have only the Involved sub form show when I have data. If I do not use the subform then I dont wish the subform to show when I print the record. When I print the record now I get allthe records in the main form"s tableas well as all the records in the sub form's table. Any help would be welcome.

 

[John Big Booty]

If you set up your main form and subform using the subform wizard all the links will get set up automagically. What I would then do is either hide or show the subform dependent on your requirements.

You could use something like;

If [COLOR="Purple"][B]ConditionToShowSubform = True[/B][/COLOR] Then
     Me!frmInvolved.Form.Visible = True
Else
      	Me!frmInvolved.Form.Visible = False
End If

Replace ConditionToShowSubform = True with an appropriate test.​

You would need to put this code in the main forms On Current event as well as testing at some point during the data entry process to show or hide your subform as required.

 

[bceo]

Thanks John, One question, you noted below the code

Replace ConditionToShowSubform = True with an appropriate test.

Inot sure what you mean, but I did add the scripted,as above with no modification, to my Main form as you suggested but I got an Error 438 Object doesn't support this property or method. Any ideas? Once again thanks for helping out I really appreciate it.

 

[bceo]

Thanks John, in your response after your code you mentioned this

Replace ConditionToShowSubform = True with an appropriate test.

I don't know what you meant by this line. I did as you instructed and placed the code (without any modification) where you said to, but I got an error 438: "Object doesn't support this property or method". Obviously, I have done something wrong, but have no idea what. Thanks for your help so far, I really appreciate it, but it seems I need more help.

 

[John Big Booty]

I'm not sure how you are determining when to show and when to hide your sub form.

But for the sake of the argument let's say you have a control, on your main form, called ShowSubFrm which is, let's say, a check box (Yes/No) and it gets checked (somehow) when you wish to show the sub form.

The code would now look something like;

If Me.ShowSubFrm = True Then
     Me!frmInvolved.Form.Visible = True
Else
      	Me!frmInvolved.Form.Visible = False
End If

It doesn't really matter you just have to have a control that you can test with an If statement that will return a consistent result, that will allow you to either show or hide the sub form.

 

[bceo]

Once thanks for getting back to me. I think I need to restate my problem. I all ready have the subform which opens up when I needed it, this is done via a command button which I placed on the Main Form. I have linked through the relationships window the tblProblemNo (auto number) to the same field on the sub form frmInvolved. I do not always need to use the Involved sub form, when I am entering into the main form. I need to connect the Involved record to the tblProblem recordset for this Problem number. In this way when I print the main form the record of the Involved shows up for that problem number only and not the entire invovled sub form records (this is what it is doing now). Also if there is no Involved sub form used for the Problem Number, no Invovled sub report shows when I print the Main Form for the problem. I hope this makes the request clearer. As always thanks for the assistance and your patience in helping with this problem.