Setting sub-form for VBA

[kingche.chan]

I am going to setup the sub-form by VBA. I want to dynamically define the source of the sub-form to read the record, such as "select * from tblRecord where field='ABC'" or "select * from tblRecord where field='MAN'", etc. But I don't know how to define the sub-form records by VBA accordingly. So, it is able to read the above SQLs at the time when required. May someone guide me through the way? Thank you.

Yours sincerely,
George Chan

 

[pbaldy]

You set the subform's RecordSource property to the desired SQL string.

 

[Galaxiom]

The SQL needs to be assigned to the RecordSource Property of the Source Object of the subformcontrol.

From the main form:

Me.subformcontrolname.Form.RecordSource = "SELECT ....."

 

[ma t]

Good morning, is there a way to change the "link master" 'link child" properties of a subform control in VBA? This is what I am attempting- change the target of a subform control and define the link properties in one event (I am a VBA novice so I am not sure if "event" is the correct term.)

This is my current code:

Private Sub Tab1ButtonLabel_Click()
Me.Parent.SF2Cont.SourceObject = Me.Tab1FormName
End Sub

What I would like to do is this:

Private Sub Tab1ButtonLabel_Click()
Me.Parent.SF2Cont.SourceObject = Me.Tab1FormName
Where Me.Parent.SF2Cont.PID = Me.Parent.TempPID
End Sub

(I have a text box control in the form header of the parent form named "TempPID"

Thank you in advance to all who contribute and Happy New Year!

 

[pbaldy]

Sure; look at LinkMasterFields and LinkChildFields in VBA help.

 

[ma t]

Thank you for the response, are you referring to the "help" button in access or is it a part of this website? I have been searching for days to try to find the answer, both in and out of access, and I must be doing something wrong...

 

[pbaldy]

By VBA help I mean the help functionality within the VBA editor.

 

[ma t]

okay i will, thanks for the tip.

 

[ma t]

Thank you again for your help, I found this reference...

Syntax

expression.LinkChildFields
expression A variable that represents an ObjectFrame object.


Is there a place I can see an example of how to create the actual line of code? I am new to VBA and much of it just doesn't make sense to me yet...

 

[ma t]

This is what I am experimenting with... I have a subform control named "SF2Cont" and a text box control named "TempFID" both on the same parent form. The subform that loads into the SF2Cont contains the field FID.

Private Sub Tab2ButtonLabel_Click()
Me.Parent.SF2Cont.SourceObject = Me.Tab2FormName
Me.Parent.LinkMasterFields = Me.Parent.[TempFID]
Me.Parent.SF2Cont.LinkChildFields = Me.Parent.SF2Cont.[FID]

I am getting an error, application-defined or object-defined error, and the line that refers to the master field has a yellow highlight.

What am i doing wrong?

 

[pbaldy]

It's expecting the name of a field or fields. Also, you are referring to a form, whereas you need to refer to the subform control. It's the object which has those properties.

 

[ma t]

I'm sorry, I guess I need to start from the first. Sorry to be such a slow learner... I appreciate your patience. I have a parent form with a textbox named "tempfid" in the form header. I have a subform control named "SF2Cont" which loads a form with a field named "fid". I have tried so many variations of things trying to get this to work. I've been working on this for several days. It is very frustrating! I have been reading as many forums as I can and looking for simple examples of how to write the code. Alas, I am not getting anywhere. To answer your question, I am referring to the sf control... and trying to refer to the "parent" form with me.parent but I guess that is just not right. my parent form is an "instance" of a form (using Allen Browne's method) so that is why referring to it by it's name ("MainNavigationForm") doesn't work... any and all help GREATLY appreciated!

 

[ma t]

Post continued... maybe i would like to try putting this code in each subform "load" or "open" event? I have several subforms. Each load into the same control (SF2Cont) via an on click event in another adjacent subform. Some of the forms need to (child) link on a field named PID, and some on a field named FID. The parent form has two text boxes named "temppid" and "tempfid" which would be the master side of the link. The parent form itself has no data source. That is why I am not just using the property setting in design view... I need it to be dynamic based on the form that is loading. Thank you again, very very much!

 

[pbaldy]

Notice the differenct between the first and the second?

Me.Parent.LinkMasterFields = ...
Me.Parent.SF2Cont.LinkChildFields = ...

They should both refer to the control.

 

[ma t]

Thanks for the tip!

I have this now...

Private Sub Form_Load()
Me.Parent.SF2Cont.LinkMasterFields = TempFID
Me.Parent.SF2Cont.LinkChildFields = FID
End Sub

I am error free... however the form is not filtering at all... it opens with all records... help?

 

[pbaldy]

I believe it wants a string, so try

Me.Parent.SF2Cont.LinkMasterFields = "TempFID"
Me.Parent.SF2Cont.LinkChildFields = "FID"

 

[ma t]

Well, I'm not getting errors, but Im getting all the records still... i tried putting this in the onclick event as well as in the on open and on load of a subform, with all the same results. Sorry to be such a pain! I feel like I am so close and yet so far.

 

[pbaldy]

The master link might need to be the form reference, since it isn't bound (just noticed that). I haven't tried that myself, but I think it can be a form reference rather than a field name.

 

[ma t]

Thanks to all who contributed- after daayyyyyyyys of searching, I found this....
http://bytes.com/topic/access/answers/733295-change-link-child-master-fields-vb

basically, you have to set the links to "" and then set them to what you want them to be.

 

[ma t]

This is what is working now- but I will try the last suggestion from PBaldy too...

Option Compare Database
Private Sub Form_Load()
Me.Parent.SF2Cont.LinkChildFields = ""
Me.Parent.SF2Cont.LinkMasterFields = ""
Me.Parent.SF2Cont.LinkChildFields = "FID"
Me.Parent.SF2Cont.LinkMasterFields = "TempFID"
End Sub