Go To A Record In A Subform Based On A Search Form

[kimmanie]

I am creating a simple database for my employer but VB coding baffles me and I am stuck on the last portion of it. Some help would be greatly appreciated as I have been working on this particular coding for two days by trying different variations and researching online.


Here's what I have:

Parent form : woinstrfrm
subform control: wolabfrm
Subform name: wolabfrm_sub

I have a <searchfrm> which allows the user to pull up a particular record on the main form based on search parameters. Two of those parameters reference the parent form and they work properly. The last search I need to filter to a record on the subform <Instorder> so it shows the associated parent form and that particular record in the subform (ideally I would prefer it didn't filter the subform but just displayed that record). The name of the text box on my search form is <IOtxtbx>

I have tried the following:

Private Sub IOsearch_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "woinstrfrm"
stLinkCriteria = "Forms![woinstrfrm].[wolabfrm].Form[Instorder] = " & Me.IOtxtbx
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

This gives me a syntax error 3075

So I tried this:

Private Sub IOsearch_Click()

Dim stDocName As String

stDocName = "woinstrfrm"
DoCmd.OpenForm stDocName
With Forms![woinstrfrm]![wolabfrm].Form
.Filter = "Me!IOtxtbx = Forms![woinstrfrm].[wolabfrm].Form[Instorder]"
.FilterOn = True

End With

End Sub

This opens the form but not to the proper record. I've been looking online and trying to adapt other formulas I've seen that are similar to my project. As I've said I'm really clueless and would appreciate any insight into my problem.

Thank you!

[June7]

Where is this code? Is it all behind the main form?

Need code to filter the main form and also code to filter the subform? Not seeing that.

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

I don't know the answer but here is a link with info that may help you.

You could always post a copy of the database so readers could review the issue.

[kimmanie]

Work Order - Copy.zip

Here is the db. From the search form the first three filters work fine - they are linked to the main form. The last one <IOtxtbx> is supposed to link to a field on the subform (found on the main form). I cannot for the life of me figure out how to filter properly.
Any help would be awesome.

[kimmanie]

the code is behind the search form.
I'm not sure how to properly filter the main and sub form, therein lies my problem.
I have posted the db for analysis. Thank you.

[June7]

Personally, I would integrate 'search' form into woinstrfrm. I would put the search controls in woinstrfrm form header section. Will greatly simplify applying filter to subform.

Why is 'search' form bound to Labour table but the data you want to filter is instructions table - why is 'search' form bound to data at all? Why is textbox bound to Instorder field? Entering search value will change the record.

I never set Caption property in table. It is just confusing, I prefer to see actual field names when viewing table.

[kimmanie]

I have moved the search controls into the main form header as suggested. I am still unsuccessful in properly coding for the <IOtxtbx> data to open the subform at the proper Instorder field. How to I get the GoToRecord to find the subform field??

[June7]

Have to set focus on the subform container first.

Want to provide revised db?

[kimmanie]

Work Order.zip

Here is the update. Thank you so much .

[June7]

The subform can only show records that are related to the main form record. Suggest you use a combobox for the Instruction Order criteria input. The RowSource would be limited to values associated with the main record. This is known as dependent or cascading combobox.

I always give subform container name different from the object it holds, like ctrLabour.

This code worked for me:

Private Sub IOsearch_Click()
Me.ctrLabour.SetFocus
DoCmd.FindRecord Me.IOtxtbx, , , , , acAll
End Sub

BTW, Search is misspelled on one of the command buttons.