By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,302 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,302 IT Pros & Developers. It's quick & easy.

Form Filters or Rebuild SQL Statement as Recordsource?

P: n/a
HK
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

Feb 20 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.