Export Filtered Form Data to Excel (1 Viewer)

freidaf

Registered User.
Local time
Today, 06:48
Joined
Aug 13, 2012
Messages
44
Hello,
I am trying to export data from a filtered Access form to Excel by clicking on a command button. I found a function written by Bob Larson to export data from a filtered Access form to Excel, but I am getting an error 438, "Object doesn't support this property or method."

My form's name is "frmReviewData" and my command button is: "cmdExport." The function name is "Send2Excel"

The code I have to call the Function on the OnClick event of the button is: Call Send2Excel(Forms!frmReviewData, "strSheetExport").

I have attached a document containing the function and a screen shot of my form. I am not experienced with VBA so the error is probably obvious to someone with experience.

Thank you for your help!
 

Attachments

  • Review Data Form.pdf
    70.8 KB · Views: 442

Ranman256

Well-known member
Local time
Today, 09:48
Joined
Apr 9, 2015
Messages
4,339
use a continuous form to show all records,
then when the user fills in the boxes, then clicks the FIND button, use vb to build the where clause:

Code:
sub btnFind_click()
dim sWhere as string

sWhere = "1=1"
if not IsNull(txtBox1) then sWhere = sWhere & " and [field1]='" & me.txtBox1 & "'"
if not IsNull(txtBox2) then sWhere = sWhere & " and [field2]='" & me.txtBox2 & "'"
if not IsNull(txtBox3) then sWhere = sWhere & " and [field3]='" & me.txtBox3 & "'"

if sWhere = "1=1" then
   me.filterOn = false
else
   me.filter = sWhere
   me.filterOn = true
endif

'save to query for export

sSql = "SELECT * FROM tblCompany WHERE " & sWhere

set qdf = currentdb.querdefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name
end sub

'export the query here:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsResults", vFile, True,tabname
 

JHB

Have been here a while
Local time
Today, 14:48
Joined
Jun 17, 2012
Messages
7,732
.. but I am getting an error 438, "Object doesn't support this property or method."
...
You need to find out exactly which code line cause the problem, to do so comment out the error handling in the Sub, (place a ' in front of On Error Goto err_handler)
Code:
[B][COLOR=Red]'[/COLOR][/B] On Error Goto err_handler
The post back which code line cause the problem.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:48
Joined
May 7, 2009
Messages
19,175
i have a Sub too that will do the export of filtered form/subform.
Code:
Public Sub subExportToExcelFilteredForm(ByVal strMainFormName As String, _
                                Optional ByVal strSubformName As String = "", _
                                Optional ByVal ExcelFile As String = "")
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' parameters:
    '
    ' strMainFormName   = the name of the main form
    ' strSubformName    = (optional) the name of the subform to export (if subform you want to export)
    ' ExcelFile         = (optional) the complete path+name of excel file where the data will be exported
    '                     if not specified will use Form/Subform name, whichever is supplied and
    '                     will be saved where the db resides.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef
    
    Dim strSQL As String
    Dim bolWithFilterOn As Boolean
    Dim strTempQryDef As String
    Dim strRecordSource As String
    
    strTempQryDef = "__zqry"
    
    If strSubformName = "" Then
        bolWithFilterOn = Forms(strMainFormName).Form.FilterOn
        strRecordSource = Forms(strMainFormName).Form.RecordSource
        If ExcelFile = "" Then _
            ExcelFile = Replace(CurrentProject.path & "\", "\\", "\") & strMainFormName & ".xlsx"
    Else
        bolWithFilterOn = Forms(strMainFormName)(strSubformName).Form.FilterOn
        strRecordSource = Forms(strMainFormName)(strSubformName).Form.RecordSource
        If ExcelFile = "" Then _
            ExcelFile = Replace(CurrentProject.path & "\", "\\", "\") & strSubformName & ".xlsx"
    End If
    If InStr(strRecordSource, "SELECT ") <> 0 Then
        strSQL = strRecordSource
    Else
        strSQL = "SELECT * FROM [" & strRecordSource & "]"
    End If
    
    ' just in case our sql string ends with ";"
    strSQL = Replace(strSQL, ";", "")
    
    If bolWithFilterOn Then
        strSQL = strSQL & _
            IIf(InStr(strSQL, "WHERE ") <> 0, " And ", " Where ") & _
            IIf(strSubformName = "", Forms(strMainFormName).Form.Filter, Forms(strMainFormName)(strSubformName).Form.Filter)
    End If
    
    Set db = CurrentDb
    
    'create temporary query
    Set qrydef = db.CreateQueryDef(strTempQryDef, strSQL)
    'db.QueryDefs.Append qrydef
    Set qrydef = Nothing
    
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    tableName:=strTempQryDef, _
    FileName:=ExcelFile
    
    ' Delete the temporary query
    db.QueryDefs.Delete strTempQryDef
    
    Set db = Nothing

End Sub

to export filtered/form:

Call subExportToExcelFilteredForm("yourFormName", , "path&name of excelfile")

to export the filtered subform:

Call subExportToExcelFilteredForm("yourFormname", "yourSubformname", "path&name of excelfile")
 

freidaf

Registered User.
Local time
Today, 06:48
Joined
Aug 13, 2012
Messages
44
Hello,
Thank you all for your replies. I tried the suggestion offered by JHB and the following like of code was highlighted:

Set rst = frm.RecordsetClone

Any advice on how to proceed?

Thank you
 

JHB

Have been here a while
Local time
Today, 14:48
Joined
Jun 17, 2012
Messages
7,732
.. I tried the suggestion offered by JHB and the following like of code was highlighted:

Set rst = frm.RecordsetClone

Any advice on how to proceed?
It is because the correct name is frmReviewData and NOT frm

 

Attachments

  • Frm.jpg
    Frm.jpg
    9.1 KB · Views: 3,516

freidaf

Registered User.
Local time
Today, 06:48
Joined
Aug 13, 2012
Messages
44
Many thanks JHB for identifying my careless mistake! The Excel spreadsheet that appeared after correcting the code looks beautiful!

Also, thanks to all who took the time to respond to my post. This forum is such a valuable resource to people like me who still have much to learn. I appreciate your time and willingness to share your knowledge!
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
Hello All -
I am also having difficulty with exporting data from a filtered Access split form to Excel by click on a command button.
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
My form's name is "Vendor Listing beta - CombinedV2" and my command button is: "cmdExport." The function name is "Send2Excel"

The code I have to call the Function on the OnClick event of the button is: Call Send2Excel(Forms!"Vendor Listing beta - CombinedV2", "strSheetExport").

Public Function Send2Excel(frm"Vendor Listing beta - CombinedV2" As Form, Optional strSheetExport As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
' On Error GoTo err_handler
Set rst = "Vendor Listing beta - CombinedV2".RecordsetClone
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetExport) > 0 Then
xlWSh.Name = Left(strSheetExport, 34)
End If
xlWSh.Activate
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting.
' You can comment out or delete
' any of this below that you don't want to
' use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function
 

JHB

Have been here a while
Local time
Today, 14:48
Joined
Jun 17, 2012
Messages
7,732
You didn't write what difficulties you do have.

That seems not to be correct, remove the red marked text:
Public Function Send2Excel(frm"Vendor Listing beta - CombinedV2" As Form, Optional strSheetExport As String)
Correct is:
Public Function Send2Excel(frm As Form, Optional strSheetExport As String)
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
I figured it out and it works.


Public Function Send2Excel(VendorListing As Form, Optional VendorList As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
' On Error GoTo err_handler
Set rst = VendorListing.RecordsetClone
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(VendorList) > 0 Then
xlWSh.Name = Left(VendorList, 34)
End If
xlWSh.Activate
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting.
' You can comment out or delete any of this below that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Calibri" 'Font
.Size = 11 'Font Size
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
I am having issues with another problem. I created an on click command button to clear the fields in my form after a search. When I click the clear fields button and perform another search by entering criteria in the fields I get an error message - Runtime error 3075.

Thanks for any assistance you can provide.


Below is the VBA for the command Search and Clear Fields.


Private Sub cmdSearch_Click()
''Purpose: Build the criteria string form the non-blank search boxes and apply to the form's Filter.

Dim strWhere, task, SearchDateRange As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.


''Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtVENDOR) Then
strWhere = strWhere & "([Vendor] Like ""*" & Me.txtVENDOR & "*"") And "
End If

''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtSTATE) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtSTATE & "*"") And "
End If
''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtDRUGMANUFACTURER) Then
strWhere = strWhere & "([Is Vendor a Drug Manufacturer] Like ""*" & Me.txtDRUGMANUFACTURER & "*"") And "
End If

''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtDEVICEMANUFACTURER) Then
strWhere = strWhere & "([Is Vendor a Device Manufacturer] Like ""*" & Me.txtDEVICEMANUFACTURER & "*"") And "
End If
''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFEDERALDRUGIDENTIFIER) Then
strWhere = strWhere & "([Federal Drug Facility Establishment Identifier] Like ""*" & Me.txtFEDERALDRUGIDENTIFIER & "*"") And "
End If
''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtCOMMENTSFKA) Then
strWhere = strWhere & "([Comments] Like ""*" & Me.txtCOMMENTSFKA & "*"")"
ElseIf Not IsNull(Me.txtCOMMENTSFKA) Then
strWhere = strWhere & "([FKA] Like ""*" & Me.txtCOMMENTSFKA & "*"") And "
End If
''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtLICENSENUMBER) Then
strWhere = strWhere & "([License Number] Like ""*" & Me.txtLICENSENUMBER & "*"") And "
End If
''Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtLICENSETYPE) Then
strWhere = strWhere & "([License Type] Like ""*" & Me.txtLICENSETYPE & "*"") And "
End If

''License Expiration Date field example.
If Not IsNull(Me.txtEXPIRATIONDATEFROM) And Not IsNull(Me.txtEXPIRATIONDATETO) Then
SearchDateRange = "([Expiration Date] >= #" & Me.txtEXPIRATIONDATEFROM & "# and [Expiration Date] <= #" & Me.txtEXPIRATIONDATETO & "#)"
strWhere = strWhere & "(" & SearchDateRange & ") And "
End If

''Date Added field example.
If Not IsNull(Me.txtDATEADDEDFROM) And Not IsNull(Me.txtDATEADDEDTO) Then
SearchDateRange = "([Date Added] >= #" & Me.txtDATEADDEDFROM & "# and [Date Added] <= #" & Me.txtDATEADDEDTO & "#)"
strWhere = strWhere & "(" & SearchDateRange & ") And "
End If


''Finally, apply the string as the form's Filter.
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - 5)
Me.Filter = strWhere
Me.FilterOn = True
''Me.txtTotal = FindRecordCount(task)
End Sub

Private Sub cmdClearField_Click()
Dim task As String
'''===Clear all text boxes, list boxes and combo boxes
Me.txtVENDOR = ""
Me.txtSTATE = ""
Me.txtDRUGMANUFACTURER = ""
Me.txtDEVICEMANUFACTURER = ""
Me.txtFEDERALDRUGIDENTIFIER = ""
Me.txtCOMMENTSFKA = ""
Me.txtLICENSENUMBER = ""
Me.txtLICENSETYPE = ""
Me.txtEXPIRATIONDATEFROM = ""
Me.txtEXPIRATIONDATETO = ""
Me.txtDATEADDEDFROM = ""
Me.txtDATEADDEDTO = ""
Me.FilterOn = False
Me.Refresh
End Sub
 

Attachments

  • RunTimeError3075.jpg
    RunTimeError3075.jpg
    45.6 KB · Views: 129

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
Firstly I suggest you add this line at the start to clear existing strWhere strings
Code:
strWhere=""

Your code isn't handling nulls correctly.
So instead of bypassing a section with nulls its adding code with double ** and double ## etc

Also please use code tags to make code much easier to read. Click the # button on the toolbar above your post and paste your code between the tags that appear.
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
I figured it out and it works now. Thank you, JHB & ridders.

Code:
Private Sub cmdSearch_Click()
    ''Purpose:   Build the criteria string form the non-blank search boxes and apply to the form's Filter.
    
    Dim strWhere, task, SearchDateRange As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    
    
    ''Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtVENDOR) Then
        strWhere = strWhere & "([Vendor] Like ""*" & Me.txtVENDOR & "*"") And "
    End If
    
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtSTATE) Then
        strWhere = strWhere & "([State] Like ""*" & Me.txtSTATE & "*"") And "
    End If
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtDRUGMANUFACTURER) Then
        strWhere = strWhere & "([Is Vendor a Drug Manufacturer] Like ""*" & Me.txtDRUGMANUFACTURER & "*"") And "
    End If
    
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtDEVICEMANUFACTURER) Then
        strWhere = strWhere & "([Is Vendor a Device Manufacturer] Like ""*" & Me.txtDEVICEMANUFACTURER & "*"") And "
    End If
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFEDERALDRUGIDENTIFIER) Then
        strWhere = strWhere & "([Federal Drug Facility Establishment Identifier] Like ""*" & Me.txtFEDERALDRUGIDENTIFIER & "*"") And "
    End If
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtCOMMENTSFKA) Then
        strWhere = strWhere & "([Comments] Like ""*" & Me.txtCOMMENTSFKA & "*"") And "
    ElseIf Not IsNull(Me.txtCOMMENTSFKA) Then
        strWhere = strWhere & "([FKA] Like ""*" & Me.txtCOMMENTSFKA & "*"") And "
        End If
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtLICENSENUMBER) Then
        strWhere = strWhere & "([License Number] Like ""*" & Me.txtLICENSENUMBER & "*"") And "
    End If
    ''Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtLICENSETYPE) Then
        strWhere = strWhere & "([License Type] Like ""*" & Me.txtLICENSETYPE & "*"") And "
    End If
   
    ''License Expiration Date field example.
    If Not IsNull(Me.txtEXPIRATIONDATEFROM) And Not IsNull(Me.txtEXPIRATIONDATETO) Then
        SearchDateRange = "([Expiration Date] >= #" & Me.txtEXPIRATIONDATEFROM & "# and [Expiration Date] <= #" & Me.txtEXPIRATIONDATETO & "#)"
        strWhere = strWhere & "(" & SearchDateRange & ") And "
    End If
    
    ''Date Added field example.
    If Not IsNull(Me.txtDATEADDEDFROM) And Not IsNull(Me.txtDATEADDEDTO) Then
        SearchDateRange = "([Date Added] >= #" & Me.txtDATEADDEDFROM & "# and [Date Added] <= #" & Me.txtDATEADDEDTO & "#)"
        strWhere = strWhere & "(" & SearchDateRange & ") And "
    End If
        
        
    ''Finally, apply the string as the form's Filter.
    If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - 5)
    Me.Filter = strWhere
    Me.FilterOn = True
    ''Me.txtTotal = FindRecordCount(task)
End Sub
 
Private Sub cmdClearField_Click()
Dim task As String
'''===Clear all text boxes, list boxes and combo boxes
Me.txtVENDOR = Null
Me.txtSTATE = Null
Me.txtDRUGMANUFACTURER = Null
Me.txtDEVICEMANUFACTURER = Null
Me.txtFEDERALDRUGIDENTIFIER = Null
Me.txtCOMMENTSFKA = Null
Me.txtLICENSENUMBER = Null
Me.txtLICENSETYPE = Null
Me.txtEXPIRATIONDATEFROM = Null
Me.txtEXPIRATIONDATETO = Null
Me.txtDATEADDEDFROM = Null
Me.txtDATEADDEDTO = Null
Me.FilterOn = False
Me.Refresh
End Sub
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
Good Evening Everyone:
I need some help with a split form I am creating in MS Access. I have two
list boxes named Vendor1 (ListVendor) and LICENSE TYPE (ListLicenseType) that I would like to search by vendor. My problem is that if I use one of the "For Each" the search works but I am trying to search by both of the list boxes is where I am having problems combining the search by multiple search fields. I hope that makes sense.

Any help with trying to write the VB code so that it search in both the list boxes if I click on more than one of the Vendors or License Types in the list boxes.



Code:
Private Sub cmdSearch_Click()
Dim varItem As Variant
Dim strWhere As String
Dim task As String
 
'''====Search by Vendor field in Vendors table
'''====Clear all list boxes
For Each varItem In Me!ListVendor.ItemsSelected
' use ASCII Chr(34) to replace Double Quotes in VB
'Chr(34) = "
' Use "Or" to get result from all Vendors
    strWhere = strWhere & "[Vendor] = " & Chr(34) & Me!ListVendor.ItemData(varItem) & Chr(34) & "Or"
Next varItem
    If Len(strWhere) = 0 Then
        task = "select * from Vendors "
    Else
        strWhere = Left(strWhere, Len(strWhere) - 2) '' remove 2 characters at the end, that is Or.
        task = "select * from Vendors where (" & strWhere & ")"
    End If
 
 
'''====code for LICENSE TYPE list box
'''====Search by LICENSE TYPE field in Vendors table
'''====Clear all list boxes
For Each varItem In Me!ListLicenseType.ItemsSelected
' use ASCII Chr(34) to replace Double Quotes in VB
'Chr(34) = "
' Use "Or" to get result from all License Type
    strWhere = strWhere & "[License Type] = " & Chr(34) & Me!ListLicenseType.ItemData(varItem) & Chr(34) & "Or"
Next varItem
    If Len(strWhere) = 0 Then
        task = "select * from Vendors "
    Else
        strWhere = Left(strWhere, Len(strWhere) - 2) '' remove 2 charactors at the end, that is Or.
        task = "select * from Vendors where (" & strWhere & ")"
    End If

    Me.Filter = strWhere
    Me.FilterOn = True

End Sub
 

Attachments

  • VendorDatabase.jpg
    VendorDatabase.jpg
    62.1 KB · Views: 147

JHB

Have been here a while
Local time
Today, 14:48
Joined
Jun 17, 2012
Messages
7,732
Try the below:
Code:
Private Sub cmdSearch_Click()
  Dim varItem As Variant
  Dim strWhere As String
  Dim task As String
   
  '''====Search by Vendor field in Vendors table
  '''====Clear all list boxes
  For Each varItem In Me!ListVendor.ItemsSelected
    ' use ASCII Chr(34) to replace Double Quotes in VB
    'Chr(34) = "
    ' Use "Or" to get result from all Vendors
    strWhere = strWhere & "[Vendor] = " & Chr(34) & Me!ListVendor.ItemData(varItem) & Chr(34) & " Or "
  Next varItem
   
  '''====code for LICENSE TYPE list box
  '''====Search by LICENSE TYPE field in Vendors table
  '''====Clear all list boxes
  For Each varItem In Me!ListLicenseType.ItemsSelected
    ' use ASCII Chr(34) to replace Double Quotes in VB
    'Chr(34) = "
    ' Use "Or" to get result from all License Type
    strWhere = strWhere & "[License Type] = " & Chr(34) & Me!ListLicenseType.ItemData(varItem) & Chr(34) & " Or "
  Next varItem
  If Len(strWhere) = 0 Then
    task = "select * from Vendors "
  Else
    strWhere = Left(strWhere, Len(strWhere) - 4) '' remove 4 charactors at the end, that is Or.
    task = "select * from Vendors where (" & strWhere & ")"
  End If

  Me.Filter = strWhere
  Me.FilterOn = True

End Sub
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
Morning JHB -
Thanks for responding. I tried the VB code. It works if you select from one of the list box but if you select from both list boxes it doesn't work together.
Any thoughts?
 

JHB

Have been here a while
Local time
Today, 14:48
Joined
Jun 17, 2012
Messages
7,732
Then post your database with some sample data, zip it then you haven't 10 post yet.
 

dilemma908

Registered User.
Local time
Today, 09:48
Joined
May 21, 2018
Messages
18
Hi JHB -
Attached is the zipped database. Thanks for any assistance you can provide.

I truly, truly appreciate your time and assistance!! :):)
 
Last edited:

Users who are viewing this thread

Top Bottom