473,320 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Form Filters or Rebuild SQL Statement as Recordsource?

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
0 1065

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Louis | last post by:
I'm running the following code but it keeps failing because Select isn't a part of the action query method: Private Sub Command2_Click() Dim SQLStr As String SQLStr = "SELECT...
3
by: RC | last post by:
I can't quite grasp the concept of creating custom reports depending upon what options a user picks on a Form. For example, the user clicks on a "Print Reports" button and a Form pops up. On the...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
1
by: Steve Miles | last post by:
I've got a form with a combo box and begin/end dates. When any of the three are changed I set a subform's recordsource so the records returned are filtered based on the three fields. The syntax...
10
by: glenn354 | last post by:
I have a form I would like to use in front of several queries. For example, I want to use frmA but only looking at the records retrieved by qryX. Then I want to use frmA again, but only looking at...
2
by: Jeff | last post by:
Hi, I am trying to use vb code to set the filter on a form so that only people with a common property can see each other on a form when they open it (like if they are in the same department)....
2
by: KashMarsh | last post by:
Access 2003 I need to have a user filter records on a linked, continuous form and then I want to run various reports/queries from this recordset the user created. I only need to see the PK...
2
by: Robert | last post by:
I am trying to give the user dynamic search capabilities to select almost any record in the database from criteria they select. Everything seems to work except when I open the display form to...
2
by: fstenoughsnoopy | last post by:
I have a customer order database and I need to pull a customers information, ie first name, last name, address, city, state, zip, phone, etc, into the oder table. i don't know how to go about...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.