Searching for records in a subform with a combo box located in main form

[nharrison]

So I have a main form, and in the header I have a combo box where a user can search for contacts, but I want the box to search for records in a subform, not on the main form. On the combo box's After Update property I have this code:

Private Sub cboNameSearch_AfterUpdate()
On Error GoTo cboNameSearch_AfterUpdate_Err

    If (IsNull(Screen.ActiveControl)) Then
        Exit Sub
    End If
    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    TempVars.Add "ActiveControlValue", "[Screen].[ActiveControl]"
    If (CurrentProject.IsTrusted) Then
        Screen.ActiveControl = Null
    End If
    If (Form.FilterOn) Then
        DoCmd.RunCommand acCmdRemoveFilterSort
    End If
    DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue
    TempVars.Remove "ActiveControlValue"

cboNameSearch_AfterUpdate_Exit:
    Exit Sub

cboNameSearch_AfterUpdate_Err:
    MsgBox Error$
    Resume cboNameSearch_AfterUpdate_Exit

End Sub

In the body of the form I have a subform with the control name "MarketingTargetsSubform", and the main form's name is "Marketing Targets". I'm trying to use a simple doCmd.GoToRecord, but I keep getting the error "An expression you entered is the wrong data type for one of the arguments", and then in the debugger it jumps to the key line:

DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

The subform is not linked to the main form in any way (no child or master fields)

I've checked syntax and googled as much as I can on the topic. Any suggestions?

 

[SOS]

You need to change this:
DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

to this

DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form.Name, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

 

[nharrison]

Ok so I tried

DoCmd.SearchForRecord acForm, Forms![Marketing Targets2]!MarketingTargetsSubform.Form.MarketingTargetsSingle, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

where "MarketingTargetsSingle" is the object name of my subform, to no avail. The error message now reads "Application-defined or object-defined error", Run-time error '2465'.

 

[SOS]

I'm sorry, I didn't say to replace .NAME with the name. Leave it exactly as I wrote it, with .NAME as the part.

DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form.Name, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

 

[nharrison]

Well, that fixed my syntax problem, for which I am very grateful.

But now I have a new error: "The object 'MarketingTargetsSingle' isn't open."

Where "MarketingTargetsSingle" is the object name (not control name) of my subform.

Except it is, it is loaded when I open the main form, and I can see it. Don't really get why it would register otherwise...any thoughts?

Thanks again for all the patience and help

 

[SOS]

Well, that fixed my syntax problem, for which I am very grateful.

But now I have a new error: "The object 'MarketingTargetsSingle' isn't open."

Where "MarketingTargetsSingle" is the object name (not control name) of my subform.

Except it is, it is loaded when I open the main form, and I can see it. Don't really get why it would register otherwise...any thoughts?

Thanks again for all the patience and help

Make sure you are referencing the name of the subform CONTAINER (the control on the main form that houses the subform) and NOT the subform name itself. It is okay if the container is named the same as the subform, but if it isn't you need to refer to the container name and not the subform name. So check the subform container and see if it is named something else.

 

[nharrison]

I believe that part is correct.

I used
DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form.Name, acFirst, "[Contact ID]=" & TempVars!ActiveControlValue

where
"Marketing Targets" = main form
"MarketingTargetsSubform" = subform control name (container)
"MarketingTargetsSingle" = subform object name (not included in code)

 

[nharrison]

OK, so I made an executive decision and reworked the structure of what I am trying to do in my grand scheme. So, I solved this problem by replacing the issue.

My new setup works much better, but I'm having a small issue, which I'll outline in a new thread here:

http://www.access-programmers.co.uk/forums/showthread.php?p=864428#post864428