|
| I have an inventory form designed in continuous format so that users
can edit quantity on hand information. I have numerous filters set up
because the inventory table holds sever hundred thousand records.
Should I be using a form filter instead of the following code?
I use the following function to change the forms record source.
UpdateView is called anytime a user makes changes to the checkboxes,
radio buttons, etc.
Public Function UpdateView()
Dim strSQL As String
Dim strExcludeZero As String
Dim strOrderBy As String
Dim strItem As String
Dim strCat As String
If chkExcludeZero = True Then
strExcludeZero = "and bal_qty <> 0 "
Else
strExcludeZero = ""
End If
If chkLimitCat = True Then
strCat = "and ccategory ='" & cboCat.Value & "'"
Else
strCat = ""
End If
If optSearch = 1 Then
strItem = "item_loc ='" & Me.cboItemLocation.Column(0) & "'"
ElseIf optSearch = 2 Then
If Nz(txtSearch.Value, "") = "" Then
MsgBox ("You must search for something!!")
Exit Function
End If
strSearch = txtSearch.Value
strItem = "item_desc LIKE '*" & strSearch & "*'"
End If
If optOrderBy = 0 Then
strOrderBy = "bal_qty DESC"
ElseIf optOrderBy = 1 Then
strOrderBy = "item_cd ASC"
ElseIf optOrderBy = 2 Then
strOrderBy = "item_desc ASC"
End If
strSQL = "Select ccategory, item_cd, item_desc, bal_qty, reord_lvl,
item_loc FROM xmsalinv1 WHERE " + strItem + " " + strExcludeZero + " "
+ strCat + " ORDER by " + strOrderBy + ";"
Me.RecordSource = strSQL
Me.Requery
|
|