search form errors

[waymond]

On my powersearch form I have the following code I keep getting the following error:

Microsoft cant find the following field form_company referred to in your expression when I debug it goes to the line

' set record source to Subform
Me![Form_Company].RecordSource = MyRecordSource


Private Sub Search_Click()

'On Error GoTo Err_VIEW_Click
'------------------------------ Starting here ----------------------
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Marketing] WHERE "

' Use values entered in text boxes in form header to create criteria for WHERE clause.
'text box name on form 'Field name in Table 'blank info ' number of times run Addt
AddToWhere [Find1], "[Company]", MyCriteria, ArgCount
AddToWhere [Find2], "[PrimarySic]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] <> "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Company]"
ElseIf Me![Find2] <> "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [PrimarySic]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Company]"
End If


' set record source to Subform
Me![Form_Company].RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub


I have a subform with the following code:

rivate Sub Command23_Click()
On Error GoTo Err_Command23_Click


Dim SyncCriteria As String
Dim f As Form, rs As Recordset

'Define the from object and recordset object for the
Set f = Forms(Screen.ActiveForm.FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = "[UstID]='" & Me![UstID] & "'"

' find the corresponding record in the Marketing table
rs.FindFirst SyncCriteria
f.Bookmark = rs.Bookmark


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Meetinglog"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

In a module I have the following code
Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If
End Sub

 

[lupins46]

Me![Form_Company].Form.RecordSource = MyRecordSource

This assumes that your subform CONTROL on your form is also called Form_company.

 

[TheAceMan1]

How are ya waymond . . . . .

Be aware: a subform on a mainform is a control like any other. As such, you have a choice of referencing it as a control:

[blue] Me![Form_Company]
Forms!MainFormName![Form_Company][/blue]

or as a form:

[blue]Me![Form_Company].Form
Forms!MainFormName![Form_Company].Form[/blue]

See Ya! . . . . . .