Set recordsource to unbound subform 1

[micang]

Access 2003

Hi All,

I have 2 unbound visible subforms - "a" = datasheet view "b" = single view on a main form(single view).

On the "b" subform I need to change the recordsource when a record is viewed in "a" subform.

If I put the following code on one of the fields in subform "a":

Private Sub a_field_GotFocus()
Dim MVCode As String
Dim mvSQL As String
MVCode = MVL_CODE
mvSQL = "select field1,field2 from table1 where mvl_code = '" & MVCode & "'"
Form_b.RecordSource = mvSQL

and am in subform "a" and I view the record on the above field, then subform "b" updates as it should, no problem.

However, there are many fields in subform "a", so a I would like the recordsource to be set when any record is viewed, not just when that particular field is viewed.

So I put the above code in the oncurrent even of subform "a", but subform "b" does not update according to the recordset, nothing happens, but you can see that subform "a" is calculating, but subform "b" does not change.

I have also tried refresh and requery on form "b", but no joy.

Any guidance will be appreciated.

Michael

 

[MajP]

This is the way I do it:

put a hidden text box on main form "txtBxLink"

link subForm2 to txtBxLink
master: [txtBxLink]
child: MVcode (I think)

Yes you can link a subform to a text box or any control. Change the value in the control and the subform changes. Try it manually at first by typing a value in the text box

Now on the main form, catch the on current event of the first sub form.

Public WithEvents subFrmA As Form

public sub Form_Load()
  'set your object equal subform A
  Set subFrmA = Me.subFrmCtlA.Form
end sub



Private Sub subFrmA_Current()
  'now you can catch the on current event of subform A
  'and set the value of the txtboxlink
  On Error GoTo errLbl:
  Me.txtBxLink = subFrmA.MVcode
    Exit Sub
errLbl:
   Call errHandler(err.Number, err.Description, "Error in in xxx")
End Sub

Ensure that on subform A under "events", "Event Procedure" is selected.

 

[MajP]

The other way instead of catching the oncurrent event in the main form. You can do this in subform A in the on current event.

me.parent.txtBxLink = me.MVCode

 

[MajP]

There is one more way. Instead of using txtBxLink on the main form. You can reference the actual control in subformA

In subform b:
master: [subFormCtlA].form.MVCode
child: MVCode

in subform A in the on current event

Me.Parent![subformCtlB].form.Requery

 

[micang]

MajP,

I can't thank you enough, this is fantastic, it works brilliantly and I have learnt so much.

I really appreciate your help.

Michael

 

[MajP]

AceMan provides FAQ702-5860 which is a 4th way to synch forms.

 

[micang]

Thanks for the link, really helpful.

Michael