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

hubelea

Registered User.
Local time
Today, 12:09
Joined
Nov 9, 2011
Messages
33
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

"Access- Imagineer that!"
Local time
Today, 13:09
Joined
Nov 1, 2006
Messages
549
are you filtering by form or using a query or what?
 

hubelea

Registered User.
Local time
Today, 12:09
Joined
Nov 9, 2011
Messages
33
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
 

hubelea

Registered User.
Local time
Today, 12:09
Joined
Nov 9, 2011
Messages
33
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

Registered User.
Local time
Today, 12:09
Joined
Nov 9, 2011
Messages
33
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!
 

Users who are viewing this thread

Top Bottom