Command Button linked to SubForm

[Dan @BCBS]

I'm trying to link a command button to a specific record in a subform!

My main form (developed from a table) has a sub form and a command button.
The command button opens another form.

When the user picks a record from the list box then clicks the command
button - I'm trying to display the form linked to the choice made on the
subform.

The common value between the subform and the form I want to open is "ProvNo"

Suggestions??


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "[ProvNo]=" & "'" & f_ProvSub![ProvNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Marshall Barton]

I'm trying to link a command button to a specific record in a subform!

My main form (developed from a table) has a sub form and a command button.
The command button opens another form.

When the user picks a record from the list box then clicks the command
button - I'm trying to display the form linked to the choice made on the
subform.

The common value between the subform and the form I want to open is "ProvNo"

Suggestions??


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "[ProvNo]=" & "'" & f_ProvSub![ProvNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

You didn't post enough details for me to sure, butm assuming
your names are all correct, I think that should be:

stLinkCriteria = "ProvNo='" & f_ProvSub.FORM!ProvNo & "'"

Note that if the ProvNo field in the table is a number type
field, it should be:

stLinkCriteria = "ProvNo=" & f_ProvSub.FORM!ProvNo

 

[Dan @BCBS]

Thank you very much for taking a look at this:
When added your suggestion it opens a form without any apparent link - as if
it's in add mode.

The form I need to open is f_ProvEDIT, the subform is called f_ProvSub and
the value is called the same thing on both "ProvNo".
When I put my curser on a record in the subform and click the command
button, it makes no difference if my courser is on any record or still in the
parent form.

Note: In the code below ( 'stLinkCriteria =...) This ICNNo value is on the
parent form and the form I need to open and it opens the correctly linked
form.

It's just the identification of the value within the subform I can't seem to
hit!!!


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "ProvNo='" & f_ProvSub.Form!ProvNo & "'"
'stLinkCriteria = "[ICNNo]=" & "'" & Me![ICNNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdProvEdit_Click:
Exit Sub

I'm trying to link a command button to a specific record in a subform!

My main form (developed from a table) has a sub form and a command button.
The command button opens another form.

When the user picks a record from the list box then clicks the command
button - I'm trying to display the form linked to the choice made on the
subform.

The common value between the subform and the form I want to open is "ProvNo"

Suggestions??


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "[ProvNo]=" & "'" & f_ProvSub![ProvNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

You didn't post enough details for me to sure, butm assuming
your names are all correct, I think that should be:

stLinkCriteria = "ProvNo='" & f_ProvSub.FORM!ProvNo & "'"

Note that if the ProvNo field in the table is a number type
field, it should be:

stLinkCriteria = "ProvNo=" & f_ProvSub.FORM!ProvNo

 

[Marshall Barton]

Ok, I am really lost now.

Which main form has what subform?

What/where is the list box?

I think you're saying that you want to open form f_ProvEDIT
filtered to one (or more?) records. Then, and this is where
I am really lost, you want to filter (or navigate) to a
record in some subform on form f_ProvEDIT???

If that's even close, then, because of various timing
issues, the newly opened main form's Load event has to be
used to locate the desired record in its subform. The first
trick to doing this is to get the desired record's id field
value to the newly opened form using the OpenForm method's
OpenArgs argument:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , somevalue

Then the code in the load event could be like:

If Not IsNull(Me.OpenArgs) Then
With Me.subformcontrol.Recordset
.FindFirst "[id field name] = '" & Me.OpenArgs & "' "
End With
End If

or for a numeric type id field:

.FindFirst "[id field name] = " & Me.OpenArgs

Or am I still not grasping your situation?
--
Marsh
MVP [MS Access]

Thank you very much for taking a look at this:
When added your suggestion it opens a form without any apparent link - as if
it's in add mode.

The form I need to open is f_ProvEDIT, the subform is called f_ProvSub and
the value is called the same thing on both "ProvNo".
When I put my curser on a record in the subform and click the command
button, it makes no difference if my courser is on any record or still in the
parent form.

Note: In the code below ( 'stLinkCriteria =...) This ICNNo value is on the
parent form and the form I need to open and it opens the correctly linked
form.

It's just the identification of the value within the subform I can't seem to
hit!!!


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "ProvNo='" & f_ProvSub.Form!ProvNo & "'"
'stLinkCriteria = "[ICNNo]=" & "'" & Me![ICNNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdProvEdit_Click:
Exit Sub

I'm trying to link a command button to a specific record in a subform!

My main form (developed from a table) has a sub form and a command button.
The command button opens another form.

When the user picks a record from the list box then clicks the command
button - I'm trying to display the form linked to the choice made on the
subform.

The common value between the subform and the form I want to open is "ProvNo"


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_ProvEDIT"

stLinkCriteria = "[ProvNo]=" & "'" & f_ProvSub![ProvNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

You didn't post enough details for me to sure, but, assuming
your names are all correct, I think that should be:

stLinkCriteria = "ProvNo='" & f_ProvSub.FORM!ProvNo & "'"

Note that if the ProvNo field in the table is a number type
field, it should be:

stLinkCriteria = "ProvNo=" & f_ProvSub.FORM!ProvNo

 

[siva]

hi,

can u help me how to open a sub form
with in a main form using command button
without getting a new form window..

i tried to do but getting a new form window..

my VBA code in command button

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrm_searchby_Address" //this is subform embedded in main form

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

whenever i click the button i need to open the sub form within the main form..

thank u
siva

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Marshall Barton]

can u help me how to open a sub form
with in a main form using command button
without getting a new form window..

i tried to do but getting a new form window..

Subfiorms are dispayed within a subform control on the main
form. You do not open them, they are just there. You can
make a subform control invisible and do some other things,
but you do not "open a subform".

Perhaps you don't have a problem or maybe you are really
after some other effect that I will need more information
before speculating on a way to achieve it.