Command button to open a sub form

[Guest]

Form 'A' (frmGreenForm) has machine details such as unique ID number (primary
key), serial number, date new, value and so forth. I have added a command
button to the form with the idea of opening a subform(sbfrmTemplate) giving
details of the ingredient template for the particular machine displayed on
the form. How can I get Access to display only the info for the machine in
question and not details for all machines?
I have tried the command button route because there physically isn't room
for a subform on the form.

tia

 

[aclelland]

Form 'A' (frmGreenForm) has machine details such as unique ID number (primary
key), serial number, date new, value and so forth. I have added a command
button to the form with the idea of opening a subform(sbfrmTemplate) giving
details of the ingredient template for the particular machine displayed on
the form. How can I get Access to display only the info for the machine in
question and not details for all machines?
I have tried the command button route because there physically isn't room
for a subform on the form.

tia

I think what you want to do is use the WhereCondition option when you
open up the subform. You would then do something like

DoCmd.OpenForm "sbfrmTemplate", acNormal, ,
WhereCondition:="machineId=" & Me.machineId

that will open the subform and only show records with machineId as
it's unique ID.

I hope that helps,
Alan

 

[Rick Brandt]

Form 'A' (frmGreenForm) has machine details such as unique ID number (primary
key), serial number, date new, value and so forth. I have added a command
button to the form with the idea of opening a subform(sbfrmTemplate) giving
details of the ingredient template for the particular machine displayed on
the form. How can I get Access to display only the info for the machine in
question and not details for all machines?
I have tried the command button route because there physically isn't room
for a subform on the form.

Alan gave you the correct answer. You use the WHERE argument of the OpenForm
method.

I just wanted to point out that if the second form is not embedded inside your
first form then it is no longer a subform. You can use the WHERE clause so it
will open with a filter to show records related to the record on your first
form, but that is all you are doing (applying a filter).

The form will not stay synchronized with your other form if you change the
record there and any new records you add will not automatically inherit the
linking field value(s) from the other form. Those features can be replicated
with some additional code, but you only get them automatically with an embedded
subform.

If space is the only reason to use a separate form I would look at using a
TabControl on your first form. That should easily allow you to use a real
subform.

 

[Guest]

Thanks for that. Nearly there. Data from the entire table was displayed
though, not just for the particular machine.
I have put a modified version of your control 'On Click' on the command
button thus:
Private Sub Command382_Click()

DoCmd.OpenForm "sbfrmTemplate", , , stLinkCriteria
Where = "um_ref" & Me.um_ref

End Sub

The last bit (Where = ....) doesn't appear to have any effect on the results.

 

[Guest]

Thanks Rick,
The TabControl option may be better in this case as I would like
synchronisation (if poss) when I change records in the main form.

 

[Rick Brandt]

Thanks for that. Nearly there. Data from the entire table was displayed
though, not just for the particular machine.
I have put a modified version of your control 'On Click' on the command
button thus:
Private Sub Command382_Click()

DoCmd.OpenForm "sbfrmTemplate", , , stLinkCriteria
Where = "um_ref" & Me.um_ref

End Sub

The last bit (Where = ....) doesn't appear to have any effect on the results.

It needs to be INSTEAD OF stLinkCriteria and if you notice he had a colon in
front of the = sign.

There are two ways to supply arguments to Access functions. The most common way
is to simply use values separated by commas. With that syntax you must keep the
arguments in order and you must still supply the comma for any argument you
skip.

The second way is to use argument name/value pairs. In that syntax you name the
argument first, followed by := followed by the value you want to supply. With
that syntax you can supply arguments in any order and you don't need to account
for arguments you are not using.

It is less often used, but some like to use the second syntax in Newsgroup posts
to avoid line-wrapping problems.

 

[aclelland]

It needs to be INSTEAD OF stLinkCriteria and if you notice he had a colon in
front of the = sign.

There are two ways to supply arguments to Access functions. The most common way
is to simply use values separated by commas. With that syntax you must keep the
arguments in order and you must still supply the comma for any argument you
skip.

The second way is to use argument name/value pairs. In that syntax you name the
argument first, followed by := followed by the value you want to supply. With
that syntax you can supply arguments in any order and you don't need to account
for arguments you are not using.

It is less often used, but some like to use the second syntax in Newsgroup posts
to avoid line-wrapping problems.

I think you need to change
Where = "um_ref" & Me.um_ref
to
WhereCondition:= "um_ref=" & Me.um_ref

Note also to = sign