where statement, macro tools, main and subform

[MGF23]

Hi guys



I have a main form and a sub form linked by a 1 to many relationship. The field NC ref # number is the PK on the ‘main form’ and the FK in my other table on which my ‘subform’ is based.

When I place a subform on the main table both NC ref#'s relate/sync to each other - which is what i want.

Now I want to remove the sub form (from the main form) and put a command button in its place.

I’ve used the wizard to put the button on the main form and open up this subform.

Can anyone suggest a where statement I can use in macro tools to make sure NC REF# on main form matches NCR Ref# on the sub form each time the control button is clicked.

Currently (for example) i could be on record: NC REF #20 - click the button and the subform shows NC ref#1.

hope that is not too cryptic!

Many thanks!

[ranman256]

Just leave the subform alone. Make it invisible.
then when the button is clicked , subform.visible = true

no linking needed.

[ssanfu]

I don't use macros... ever.

BTW, "NC ref #" is an extreamly poor field name. It has spaces in the name and a special character (which happens to be a date delimiter).
Better would be "NC_RefID_PK" for the primary key and "NC_RefID_FK" for the foreign key field.

In VBA, it would be something like this:

Private Sub btnOpenSubForm_Click()

    DoCmd.OpenForm "MySubForm", , , "[NC_RefID_FK] = " & Me.NC_RefID_PK

End Sub

Change "MySubForm" to the name of your sub form.
"btnOpenSubForm" is what I named the button.

The macro *might* be like this (again, I never use macros, so this couold be way off):

[MGF23]

Thanks for the response folks. I solved this one late last night via the VBA route. Great tip about the field name Steve. I've changed that now - that was me being lazy with 'short-handing' the word 'number'.

[ssanfu]

At one point I used names like "NC_RefNo". That was not real clear, so I switched to "NC_RefNum".

I use "conserving energy" instead of "lazy"


Happy you solved the problem.....