Export Macro works on form, not subform (1 Viewer)

[hubelea]

I'm using Access 2010, and am a self-taught (with the help of forums like this) database developer. I created a form to search for records in a table based on multiple fields, and added a button with a macro to export the search results to Excel. The button/macro works just great on the form. Then I used the form as a subform on a tabbed page of a custom switchboard. The command button macro now does not export the search results, just the formatting and labels. Any suggestions?

 

[GohDiamond]

are you filtering by form or using a query or what?

 

[hubelea]

Hi - I'm filtering via the form - here's the code:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterUPC) Then
strWhere = strWhere & "([UPC] Like ""*" & Me.txtFilterUPC & "*"") AND "
End If
If Not IsNull(Me.CboFilterCategory) Then
strWhere = strWhere & "([CategoryLookup] = """ & Me.CboFilterCategory & """) AND "
End If
If Not IsNull(Me.CboFilterBrand) Then
strWhere = strWhere & "([BrandLookup] = """ & Me.CboFilterBrand & """) AND "
End If
If Not IsNull(Me.TxtFilterDescr) Then
strWhere = strWhere & "([Descr] Like ""*" & Me.TxtFilterDescr & "*"") AND "
End If
If Not IsNull(Me.TxtFilterSize) Then
strWhere = strWhere & "([Size] Like ""*" & Me.TxtFilterSize & "*"") AND "
End If
If Not IsNull(Me.CboFilterUSDA) Then
strWhere = strWhere & "([USDA_Elig] = """ & Me.CboFilterUSDA & """) AND "
End If
If Not IsNull(Me.CboFilterSupplier) Then
strWhere = strWhere & "([SupplierLookup] = """ & Me.CboFilterSupplier & """) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[GohDiamond]

oh it's no longer Me. because it's a subform, so you have to fix the reference to, for example, Me.txtFilterUPC. Here is some information that may help here: http://access.mvps.org/access/forms/frm0031.htm

 

[hubelea]

Just so I'm clear (because I'm not real proficient at code, and want to be sure before I go through and change it).... The issue is in the actual search code, not in the export macro? Because the search still works, and brings in the results, even though its on a subform. Its the export that doesn't work... And....assuming you say yes, the issue is in this search code, then would a subform that is part of a tabbed control be referenced as a subform or as a control? What would be the proper syntax?

 

[hubelea]

Thanks for your help - I tried using a variety of different syntax options and never could get it to work. I wound up just reorganizing the tabbed controls and set this one up so that you click on a button and it opens the form, then you do the search, and then the macro works. Just couldn't find a way for the macro to work with a subform, works with a form. Thanks anyway!