open a second subform from a first subform on the same parent 1

[SKBrede]

I can not open a second subform from a first subform on the same parent - I am using the following procedure that is called from a double click event on the first subform

Public Sub OpenScheduleDetails(passedJobID As Variant)
Dim JID As String
JID = passedJobID
DoCmd.OpenForm "forms![frmMS]![frmControlDispatch]", , , "JobID = " & JID
End Sub

I have tried many variation on the form address syntax but continue to get the following error mssg

form [frmMS]![frmControlDispatch] is either missing for is misspelled.

Any help would be appreciated

 

[PHV]

The DoCmd.OpenForm method tries to open a MAIN form ...

You wanted this ?
DoCmd.OpenForm "frmControlDispatch", , , "JobID=" & passedJobID

Furthermore, if JobID is not a numeric field:

DoCmd.OpenForm "frmControlDispatch", , , "JobID='" & passedJobID & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SKBrede]

PH I have tried your suggestion, but it results only in the opening of a second frmControlDispatch which is isolated from the Main form and does not affect the subform results

 

[PHV]

So, what do you want to do ?
A subform is automatically open with its parent form !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SKBrede]

HI PH
After the parent form is opened, I want to open the second subform with a record where the criteria for the second record is contained in the first subform

Thanks,
Steve

 

[MajP]

Do you have a second subform control on the main form, or do you want this to replace the subform in the first subform control? When you say "Open", where do you expect it to open?

 

[SKBrede]

Majp
I have the second subform already on the Parent along with the first - I would like to just update the data in the second subform area with data selected from the first subform area.
thanks,Steve

 

[MajP]

The way I understand this is you have two subforms, and you want the second one filtered to some value. The name of my subform control is "subFrmControl2"

  With Me.subFrmControl2.Form
    .Filter = "JobID = " & passedJobID
    ' or "JobID = '" & passedJobID & "'"
    .FilterOn = True
  End With

If there is no subform in subfrmControl2 then you would need something like this.

me.subFrmControl2.sourceobject = "frmControlDispatch

 

[SKBrede]

Majp Sorry, I should have been more clear
I have the second subform already on the Parent along with the first - I would like to update the record in the second subform area with a record selected by using the data from a control on the first subform . The process would be initiated by using a click event on the first subform's control. The problem appears to be in addressing the second subform from the first subform's control event
thanks again
,Steve

 

[MajP]

to refer to a control on a second sub from the first subform where the name of the second subform control is "subformcontrol2" and the control on subformcontrol2 is "nameofcontrol"


Me.parent.subformcontrol2.form.nameofcontrol

 

[SKBrede]

Majp Still not quite what I need . There are two subforms on the Parent, the first and the second - I would like to update the record in the second subform area with a record selected by using criteria from a control on the first subform . The process would be initiated by using a click event on the first subform's control which contains the second form's record criteria. My problem is that I can not create a command syntax in the first forms click event code that can open the second subform with it's recordsource being obtained from the first subforms control value

Thanks for your help and your patience
Steve

 

[MajP]

in the second subform what exactly do you want to do. I am not sure what you mean by update

1)add a new record with a "jobID" = somevalue from some control on sub 1?
2)open the record in sub 2 with a "jobID" = somevalue from some control on sub 1 ?
3)create a new record with all the information from subform one?

Are the two subforms from two different tables?

 

[SKBrede]

Hi MajP

Yes I want to open an existing record on subform2 as selected by the criteria (JobID) on subform1. Both forms have the same single table as their source. Here is the code that I try to execute upon the click event on subform1.

Private Sub TS4Name_Click()
Dim thisjob As String
thisjob = Me.TS4JobID
If thisjob > 0 Then

DoCmd.OpenForm "[frmMS]! [frmControlDispatch]", , , "JobID = " & thisjob
End If
End Sub

I can open either form separately, but when I try to open subform2 (frmControlDispatch), I get the error message that the entire string ("[frmMS]![frmControlDispatch]") is either missing or misspelled

Again thanks for your help

 

[MajP]

Your terminology is wrong. You do not open a form in a subform. The docmd.open form will never work.
1. You can either filter the subform to a specific record.
2. Or change the source object of the subform control.

So as I said before. If there is already a form loaded in your subform control 2. Then all you really want to do is filter the records in sub form 2 that match the job id of subform 1.

Something like this. When you click TS4Name in sub1 it will filter all the records in sub 2 to those that match the JobID in TS4JobID

Private Sub TS4Name_Click()
 if Me.TS4JobID > 0 then
    With Me.parent.subFrmControl2.Form
    .Filter = "JobID = " & Me.TS4JobID
    ' or "JobID = '" & Me.TS4JobID & "'"
    .FilterOn = True
  End With

If subform control 2 does not have a form in it. (it is an empty sub form control) you have to first set the source object of subform control 2. (tell the subform control what form goes in the control)

Private Sub TS4Name_Click()
 if Me.TS4JobID > 0 then
    me.parent. subFrmControl2.sourceobject = "frmControlDispatch"
    With Me.Parent.subFrmControl2.Form
    .Filter = "JobID = " & Me.TS4JobID
    ' or "JobID = '" & Me.TS4JobID & "'"
    .FilterOn = True
  End With

 

[MajP]

It sounds like you may want to synchronize subforms. So if jobID equals 1 in TS4JobID, then you want to see record/s with jobID = 1 in second subform.

I do it this way
1) Put a hidden text box on the main form called "txtBxLink"
2) On the "on current" event of subform 1 and the "after update" event of TS4JobID place this code.
me.parent.txtBxLink = me.ts4jobID
3)In the properties of subform control 2
Link Master: [txtBxLink]
link child: = [jobID]

 

[SKBrede]

MajP,

I really appreciate your help -- I have not had a chance to get back to the project, but will tommorow and will try your suggestions, and am grateful for your help

Steve