User defined type not defined

[Mac R]

I am a little confused on what you mean and interested in what you are having your users be able to type. Would you be able to attach a sample database of what you are describing or clarify in a bit more detail?
Maybe someone here can help you integrate criteria.

Working on a synopsis for you...

[Mac R]

Vita:

Howdy…
I’d be happy to go into it further. Please be aware I am a hack at best, no professional training, not a coder by profession, so take this into consideration.



This is an example of what I was talking about. The form is not bound to anything. The sub-form is bound to table t_item_master and basically has a record source of the table name in order to design it, so upon open, all records in t_item_master display.

As the user interacts with the top portion of the form, they can begin to focus in on certain records that meet their entered criteria. For example, in the Description Like field, the user could enter in some string and once they hit enter, an After Update event kicks off (more on this later) and the sub-form refreshes and produces any records that match. Below, the user enters “spice” and presses enter.

The Description Like field embeds an asterisk before and after the provided string in code in order to do the Like search.

SQLWhere = "Where [Name] Like '*" & Me![Desc] & "*'"

The search fields are interrelated. If criteria are found in more than one search field, the returned results are based on all provided criteria. Using the above example, if the user then selects “Spices – Bulk” from the Category drop down and presses enter (or tab), the results display.


For this screen, five criteria fields are provided along with numerous sort options. Each criterion has two Event Procedures set up, one for After Update and one for On Dbl Click. Each triggers a function called RefreshDisplay. The On Dbl Click event clears the field first by setting it to Null, then calls the function.
Defined variables:

Option Explicit
Dim SQL, SQLWhere, SQLSort, varFileString As String
Dim Msg As String
Dim Resp As Variant

The RefreshDisplay function:

Function RefreshDisplay(Optional varOutPutType As Integer)
' Select Case varOutPutType Do I need this?
If varOutPutType <> 9 Then
    SQL = "Select * from t_Item_master "
Else
    SQL = "Select [Item UUID], [Name] from t_Item_Master "
End If
 
SQLWhere = ""
SQLSort = ""
If Not IsNull(Me![Dept]) Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [Department] = '" & Me![Dept] & "'"
    Else
        SQLWhere = "Where [Department] = '" & Me![Dept] & "'"
    End If
End If
If Not IsNull(Me![Cat]) Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [Category] = '" & Me![Cat] & "'"
    Else
        SQLWhere = "Where [Category] = '" & Me![Cat] & "'"
    End If
End If
If Not IsNull(Me![Desc]) Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [Name] Like '*" & Me![Desc] & "*'"
    Else
        SQLWhere = "Where [Name] Like '*" & Me![Desc] & "*'"
    End If
End If
If Not IsNull(Me![vUPC]) Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [UPC] Like '*" & Me![vUPC] & "*'"
    Else
        SQLWhere = "Where [UPC] Like '*" & Me![vUPC] & "*'"
    End If
End If
If Not IsNull(Me![Supplier]) Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [Supplier] = '" & Me![Supplier] & "'"
    Else
        SQLWhere = "Where [Supplier] = '" & Me![Supplier] & "'"
    End If
End If
If Me![Flagged] = -1 Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [Flag] = -1 "
    Else
        SQLWhere = "Where [Flag] = -1 "
    End If
End If
If Me![Tagged] = -1 Then
    If SQLWhere <> "" Then
        SQLWhere = SQLWhere & " And [PrintTagFlag] = -1 "
    Else
        SQLWhere = "Where [PrintTagFlag] = -1 "
    End If
End If
 
If Me![DepartmentSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Department]"
    Else
        SQLSort = " Order By [Department]"
    End If
End If
 
If Me![CategorySort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Category]"
    Else
        SQLSort = " Order By [Category]"
    End If
End If
 
Select Case Me![SalesUnits]
    Case 1
        SQLSort = " Order By [USalesTY] Desc"
    Case 2
        SQLSort = " Order By [USalesLY] Desc"
    Case 3
        SQLSort = " Order By ([USalesTY] + [USalesLY]) Desc"
End Select
 
Select Case Me![SalesDollars]
    Case 1
        SQLSort = " Order By [DSalesTY] Desc"
    Case 2
        SQLSort = " Order By [DSalesLY] Desc"
    Case 3
        SQLSort = " Order By ([DSalesTY] + [DSalesLY]) Desc"
End Select
 
If Me![NameSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Name]"
    Else
        SQLSort = " Order By [Name]"
    End If
End If
 
If Me![RetailSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Price]"
    Else
        SQLSort = " Order By [Price]"
    End If
End If
 
If Me![MarginSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Margin] Desc"
    Else
        SQLSort = " Order By [Margin] Desc"
    End If
End If
 
 
If Me![SupplierSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Supplier]"
    Else
        SQLSort = " Order By [Supplier]"
    End If
End If
 
If Me![CostSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Cost]"
    Else
        SQLSort = " Order By [Cost]"
    End If
End If
 
If Me![CDate] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [CreationDate] Desc"
    Else
        SQLSort = " Order By [CreationDate] Desc"
    End If
End If
 
If Me![LSDate] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [Last Sold Date] Desc"
    Else
        SQLSort = " Order By [Last Sold Date] Desc"
    End If
End If
 
If Me![OHSort] = -1 Then
    If SQLSort <> "" Then
        SQLSort = SQLSort & ", [QOH]"
    Else
        SQLSort = " Order By [QOH]"
    End If
End If
 
SQL = SQL & SQLWhere & SQLSort & ";"
'MsgBox SQL
Me![sf_item_master_list].Form.RecordSource = SQL
Me![sf_item_master_list].Form.Requery
 
End Function

I will most likely get hollered at (deservedly so) for using a reserved word (SQL) as a variable in my code. I need to clean that up.

This produces records specific to the users’ criteria. This is powerful for the user, but there are times when reports are required or even exports to Excel for further work. The buttons on screen provide for this. Excel:

Private Sub btnExport_Click()
Resp = MsgBox("Export the current data set to Excel?", 36)
If Resp = 7 Then Exit Sub
Dim vOpen As Boolean
Resp = MsgBox("Open the Excel File after export?", 36)
If Resp = 7 Then vOpen = False Else vOpen = True
 
Dim vSQL As String
RefreshDisplay
vSQL = SQL
CreateTempQueryFromSQL vSQL: DoEvents
DoCmd.OutputTo acOutputQuery, "AdHocExcelQuery", acFormatXLSX, , vOpen
DoEvents
DeleteTempQueryDef
 
End Sub

This is where the AdHocQuery I was talking about comes into play. My function CreateTempQueryFromSQL creates the QueryDef in the front end db, uses it for whatever, and deletes it. In my opinion, davegri’s function is cleaner.

The same holds true for a report. I open the report using the SQLWhere variable, so the report only contains the data displayed on screen.

Lastly, the user can "drill down" to detailed data on any item showing on screen by double clicking on the item name. This opens an item detail screen with just about everything the user could want to know about the item.



I have gone pretty deep here not knowing exactly what you were looking for. Hope this explains more about how the process works. Please reach out should you wish more info.