T
TraceyDee
I took Allen Browne's Sample database for the Search Criteria, edited it my
needs and it works perfectly...except, when I add a "Product", which goes to
my product table with primary Product ID (automatic) and {roduct Name (ie:
Adapters, Bags, Ports, etc.) and I re-open my main search form, it doesn't
sort by Product Name but by Product ID
This is based on a query including a few tables (Product Table included)
sorted by Product Name.
I've tried too many "orderby" but I am getting an error when I click on my
search button.
Does anyone know what I'm doing wrong - or what I need here to have it sort
by Product Name ONLY???????
Here's the code from Mr. Browne (edited):
Private Sub cmdFilter_Click()
Dim StrWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtFilterDescriptionMatName) Then
txtFilterDescriptionMatName = Replace(txtFilterDescriptionMatName,
Chr$(34), Chr$(34) & Chr$(34))
StrWhere = StrWhere & "([DescriptionMatName] like ""*" &
Me.txtFilterDescriptionMatName & "*"") AND "
End If
If Not IsNull(Me.cboFilterProduct) Then
StrWhere = StrWhere & "([ProductID] = " & Me.cboFilterProduct & ")
AND "
End If
If Not IsNull(Me.txtStartDate) Then
StrWhere = StrWhere & "([DateAdd] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If
If Not IsNull(Me.txtEndDate) Then
StrWhere = StrWhere & "([DateAdd] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If
If Not IsNull(Me.txtFilterVendor) Then
txtFilterVendor = Replace(txtFilterVendor, Chr$(34), Chr$(34) &
Chr$(34))
StrWhere = StrWhere & "([Vendor] like ""*" & Me.txtFilterVendor &
"*"") AND "
End If
lngLen = Len(StrWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
StrWhere = Left$(StrWhere, lngLen)
Me.Filter = StrWhere
Me.FilterOn = True
End If
End Sub
Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
needs and it works perfectly...except, when I add a "Product", which goes to
my product table with primary Product ID (automatic) and {roduct Name (ie:
Adapters, Bags, Ports, etc.) and I re-open my main search form, it doesn't
sort by Product Name but by Product ID
This is based on a query including a few tables (Product Table included)
sorted by Product Name.
I've tried too many "orderby" but I am getting an error when I click on my
search button.
Does anyone know what I'm doing wrong - or what I need here to have it sort
by Product Name ONLY???????
Here's the code from Mr. Browne (edited):
Private Sub cmdFilter_Click()
Dim StrWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtFilterDescriptionMatName) Then
txtFilterDescriptionMatName = Replace(txtFilterDescriptionMatName,
Chr$(34), Chr$(34) & Chr$(34))
StrWhere = StrWhere & "([DescriptionMatName] like ""*" &
Me.txtFilterDescriptionMatName & "*"") AND "
End If
If Not IsNull(Me.cboFilterProduct) Then
StrWhere = StrWhere & "([ProductID] = " & Me.cboFilterProduct & ")
AND "
End If
If Not IsNull(Me.txtStartDate) Then
StrWhere = StrWhere & "([DateAdd] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If
If Not IsNull(Me.txtEndDate) Then
StrWhere = StrWhere & "([DateAdd] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If
If Not IsNull(Me.txtFilterVendor) Then
txtFilterVendor = Replace(txtFilterVendor, Chr$(34), Chr$(34) &
Chr$(34))
StrWhere = StrWhere & "([Vendor] like ""*" & Me.txtFilterVendor &
"*"") AND "
End If
lngLen = Len(StrWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
StrWhere = Left$(StrWhere, lngLen)
Me.Filter = StrWhere
Me.FilterOn = True
End If
End Sub
Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.Filter = "(False)"
Me.FilterOn = True
End Sub