Open main form goto rec AND rec on subform

[christopher007]

If I have two tables Table1 & Table2 with a one to many relation ships, create a form with Table1 and Table2 as subform. I now want to open this form from another form and select a record in the main form and a related record in the subform.

Could someone be kind enough to show me how with VBA code.

Best Regards
Chris

 

[PHV]

Take a look at the 4th argument of the DoCmd.OpenForm method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[christopher007]

Ok this is the code I know how to use to open a form and filter on a field in the main part of the form:-

Dim stLinkCriteria As String

stDocName = "service customers"
DoCmd.OpenForm stDocName, , , stLinkCriteria

What I am having trouble with is trying to filter a related record in the sub form. I have tried to use AND in the “ stLinkCriteria = "[ID]=" & Me![List0] “ bit but cant seem to get it right or can it not be done this way ?

 

[christopher007]

Sorry should of read :-

Code is:-

Dim stLinkCriteria As String

stDocName = "service customers"
stLinkCriteria = "[ID]=" & Me![List0]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Qestion: How do use a AND condition in my stLinkCritera string so I can filter on field [id] on main form and also on a field called [id] in the sub form.

Thanks in advance.
Chris

 

[PHV]

Something like this ?
stLinkCriteria = "[ID]=" & Me![List0] & " AND [subID]=" & Me![subform control name].Form![ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[christopher007]

Thanks for the reply, however may be I am not making it clear, I am trying filter/select a record on the main form then select/filter a record on the subform.

On the main form I need to filter on [id] and also [id] in the subform which is a related table.

Suppose I want to write code to select [id]=9 in main form and [id]= 45 in subform. Subform is called SR :-

stLinkCriteria = "[ID]=9 AND [SR].Form![ID]=45"

but this wont work ?

 

[PHV]

Keyword for your search: OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[christopher007]

Ok I have searched on the openArgs and think the code below should work though I do have a couple of quick question:-


Dim sfrm As Form, rst As DAO.Recordset, Idx As Integer

Dim idClient As Long, idDeal As Long

Set frm = Me!Deals.Form

Idx = InStr(1, Me.OpenArgs, ".")
idClient = Val(Left(Me.OpenArgs, Idx - 1))
idDeal = Val(Right(Me.OpenArgs, Len(Me.OpenArgs) - Idx))

Set rst = Me.RecordsetClone
rst.FindFirst "[Client_ID] = " & idClient
Me.Bookmark = rst.Bookmark

Set rst = sfrm.RecordsetClone
rst.FindFirst "[deal_ID] = " & idDeal
sfrm.Bookmark = rst.Bookmark

Set sfrm = Nothing
Set rst = Nothing


1. The line “Set frm = Me!Deals.Form” should this be “Set sfrm = Me!Deals.Form”

2. II now get “Run time error ‘91’: Object variable or with block variable not set

Thanks for your help on this one.

 

[PHV]

In which event procedure is your code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[christopher007]

the procedure is in:- Form_Load()

 

[TheAceMan1]

How are ya christopher007 . . . . .

Maybe this:

[blue]   Dim namMain As String, namSub As String
   Dim sfrm As Form, Criteria As String
   
   namMain = "[purple][b]service customers[/b][/purple]"
   namSub = "[purple][b]Deals[/b][/purple]"
   Criteria = "[Client_ID] = " & [purple][b]idClient[/b][/purple]
   DoCmd.OpenForm namMain, , , Criteria
   
   Set sfrm = Forms(namMain)(namSub).Form
   sfrm.Filter = "[deal_ID] = " & [purple][b]idDeal[/b][/purple]
   sfrm.FilterOn = True
   set sfrm=nothing[/blue]

See Ya! . . . . . .

 

[christopher007]

Ok So I now have this code :-

Dim namMain As String, namSub As String
Dim sfrm As Form, Criteria As String

namMain = "service customers"
namSub = "SR"
Criteria = "[ID] =45"
DoCmd.OpenForm namMain, , , Criteria

Set sfrm = Forms(namMain)(namSub).Form
sfrm.Filter = "[ID] =9"
sfrm.FilterOn = True
Set sfrm = Nothing

However goes to main record but wont select the record in subform, my subform is saved as “service requests”, the subform control on the main form is called “SR” so is the above correct?

 

[TheAceMan1]

christopher007 . . . . .

Yes . . . correct.

Earlier you posted: [purple][Client_ID][/purple] & [purple][deal_ID][/blue].
Are you sure [blue][ID][/blue] is correct for both Main & subform?

Earlier you posted: Set frm = Me![purple]Deals[/purple].Form . This indicates the [blue]subForm control name[/blue] is [purple]Deals[/purple], not SR! Verify that the [blue]Name[/blue] property of the subForm control is [blue]SR[/blue] . . . .

See Ya! . . . . . .

 

[christopher007]

I have checked my code, cant see nothing wrong, still all I get is the main form filtering correctly but cant filter on the subform, it goes to a blank record and there defiantly is a related record id=9 for record id=45.

 

[christopher007]

My fault, it works thanks to all who input into helping me, thank god there are people with out eristic tendencies.

Cheers Chris