You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest blogger is Alison Balter. Alison is the founder of InfoTech Services Group, Inc. and has authored 14 Access books since 1995.
When working in a client/server environment, it is imperative that you limit the number of rows that the user brings over the network wire. It is therefore necessary that you supply a criteria form that allows the user to specify the rows that they want to return. The problem is that the user often designates criteria that is way too broad. In that situation, you must determine that there is a problem, and require the user to create criteria that returns less rows. An example is the form shown below.
As you can see, the user first selects from a Search For combo box to designate the field within they wish to search. They then determine whether they are looking for the criteria as an exact match, starts with, or whether the search string is contained within the field. If the user enters criteria that returns over 500 rows, the message box below appears.
The user must narrow their criteria and try again. The exception to this is if the field being searched is either the Vendor Used Cage Code or the Requested Cage Code. In that case the message shown below appears, and only the top 500 rows appear.
The code below accomplishes all of the functionality described above.
Private Sub SearchProjects() Dim strFieldToSearch As String Dim strValueToSearch As String Dim strOperatorToSearch As String Dim strWhereClause As String Dim strWhereClauseSQL As String Dim strWhereClause_Acct As String Dim strWhereClauseSQL_Acct As String 'Declare recordset used to hold the count of rows returned from the server Dim rst As ADODB.Recordset Dim lngRowCount As Long strFieldToSearch = Me.cboSearchFor.Value strValueToSearch = Me.txtFindItem strOperatorToSearch = Me.cboSearchWithText.Value 'Evaluate whether the user has selected Starts With, Exact Match, or Contains Select Case strOperatorToSearch Case "S" 'Starts With strWhereClause = strFieldToSearch & " Like '" & _ strValueToSearch & "*'" strWhereClauseSQL = strFieldToSearch & " Like '" & _ strValueToSearch & "%'" Case "E" 'Exact Match strWhereClause = strFieldToSearch & " = '" & _ strValueToSearch & "'" strWhereClauseSQL = strFieldToSearch & " = '" & _ strValueToSearch & "'" Case "A" 'Contains strWhereClause = strFieldToSearch & " Like '*" & _ strValueToSearch & "*'" strWhereClauseSQL = strFieldToSearch & " Like '%" & _ strValueToSearch & "%'" End Select Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection 'Open the recordset, returning the count of ProjectID 'from vwSearchRequisitions based on the criteria the user has designated rst.Open "SELECT COUNT(ProjectID_Projects) AS NumRows " & _ "FROM vwSearchRequisitions WHERE " & strWhereClauseSQL lngRowCount = rst!NumRows rst.Close 'If the count returned from the server is less than or equal to 500, 'all is well, and the data is displayed in the appropriate subforms If lngRowCount <= 500 Then Me.fsubSearchRequisitions.Form.RecordSource = _ "SELECT * FROM vwSearchRequisitions WHERE " & _ strWhereClause & " ORDER BY EnteredOn DESC" Me.fsubSearchRequisitions.Form.Requery Me.fsubSearchRFQ.Form.RecordSource = _ "SELECT * FROM vwSearchRequisitions WHERE (" & _ strWhereClause & ") AND ProjectID_RFQ IS NOT NULL" & _ " ORDER BY EnteredOn DESC" Me.fsubSearchRFQ.Form.Requery Me.fsubSearchBid.Form.RecordSource = _ "SELECT * FROM vwSearchRequisitions WHERE (" & _ strWhereClause & ") AND ProjectID_BID IS NOT NULL" & _ " ORDER BY EnteredOn DESC" Me.fsubSearchBid.Form.Requery Me.fsubSearchAcct.Form.RecordSource = _ "SELECT * FROM vwSearchRequisitions WHERE (" & _ strWhereClause & ") AND ProjectID_Acct IS NOT NULL" & _ " ORDER BY EnteredOn DESC" Me.fsubSearchAcct.Form.Requery Else 'If the search returned more than 500 rows, but the user is searching for 'Vendor Used Cage Code or Requested Cage Code, return the top 500 rows. If Me.cboSearchFor.Column(0) = "Vendor Used Cage Code" Or _ Me.cboSearchFor.Column(0) = "Requested Cage Code" Then MsgBox "Data Being Reduced to 500 Most Recent Rows", _ vbInformation, "Warning" Me.fsubSearchAcct.Form.RecordSource = _ "SELECT TOP 500 EnteredOn, ProjectID_Projects, " & _ "ExpiresOn, ProsIDNum, " & _ "SnapReqID, GeneralReqID, CustFileNum, ReqCageCode " & _ "ReqVendor, PNNum, NSN, Qty, UOM, PartDesc, " & _ "GROUP BY EnteredOn, ProjectID_Projects, " & _ "ExpiresOn, ProsIDNum, " & _ "SnapReqID, GeneralReqID, CustFileNum, ReqCageCode " & _ "ReqVendor, PNNum, NSN, Qty, UOM, PartDesc, " & _ "HAVING (" & strWhereClause & _ ") AND ProjectID_Acct IS NOT NULL ORDER BY EnteredOn DESC " Else 'For any other field display a message that the criteria must be narrowed MsgBox "Your Criteria is Returning Too Many Rows. " & _ "Narrow Your Criteria and Try Again", vbExclamation, _ "Please Try Again" Exit Sub End If End IfEnd Sub
The code first determines whether the user has selected Starts With, Exact Match, or Contains. Note that regardless of their choice, the code sets the value of two variables: strWhereClause and strWhereClauseSQL. It must do this because the SELECT COUNT is executed on the server. SQL Server does not understand the asterisk (*). It is therefore necessary to create a variable with the percent (%) sign, the wildcard in SQL Server.
Now the code is ready to open the recordset, returning the count into lngRowCount. Next the code evaluates lngRowCount. If it is less than or equal to 500, all is well, and the data is displayed in a datasheet. The data is based on a vwSearchRequisitions with the appropriate WHERE clause applied (based on the selected criteria).
If the search returned more than 500 rows, but the field being searched was either Vendor Used Cage Code or Requested Cage Code, a message box appears warning the user that only the top 500 rows will appear in the datasheet, and then SELECT TOP 500 is used to return the appropriate rows.
Finally, if the user is searching any other field, and the number of rows returned is greater than or equal to 500, a message appears, and the search is aborted.
Comments: (8) Collapse
good article alison - agree wholeheartedly we specialize in multi-user access db's where speed is paramount - delivering as few records as possible in every situation is how we maximize the speed of the application - regardless of the number of users we use unbound forms therefore no records are delivered and so the forms open instantly - there may be one drop-down that needs to be populated to get things rolling we then use a search screen with multiple options - we build the sql statement on the fly as in your example - typically the user is only interested in one record at a time in most cases with unbound forms you dont have record locking issues either - lock the record only when the user goes into edit mode obviously there must be a "down side" and it is that the applications become vba intensive - then again all the "big" systems are code intensive anyway goldsoft generates most of the code itself so it is always of the same style - the programmer is faced with one "mentality' - once they master that every module in every db is easily controlled as an example we open an orders form called f_order the controls supplier_id and product_id have the rowsource set to a query called "q_id_blank" which has the sql code SELECT Null AS 0, Null AS 1;
two fields both set to null the form opens instantly as there are no records delivered to populate the supplier_id dropdown - in the open event of the form we now put the code Me!supplier_id.RowSource = "q_suppler_id" Me!supplier_id.ReQuery while the user is deciding what to do the supplier dropdown is populated behind the scene - no perceived delay once the user chooses a supplier we then populate the product_id dropdown and so on thanks for a good article
Interesting solution... but the implementation has a serious security flaw: Can anyone say "SQL Injection attack?" I realize this is sample code, but it should still follow best practices. Sven.
"When working in a client/server environment, it is imperative that you limit the number of rows that the user brings over the network wire." I guess the Project Gemini team won't agree with this statement. I've seen a video demo where something like 100M rows gets dumped into the Gemini client in Excel...and that's from a single table. The demo shows multiple tables in the client, so it's anybody's guess how many rows are in these other tables.
I noticed on the subforms the navigation buttons are displayed, but the search textbox is not visible.
I've tried to find a method to make the search textbox not visible, but with no success. Does anyone know how that is done?
Sven, You mentioned SQL Injection attacks from this code sample.
I'm writing an application that uses Access FE and SQL Server BE. In most cases I'm using stored procs to retrieve and update data on the BE. I need info on SQL Injection attack and how to code to prevent them. Is using stored procs all that is required?
are you kidding me? this code is just _AWFUL_-- it runs a query twice for starters.. I'd reccomend using Access Data Projects, they allow you to easily filter for the top 500 rows, etc and it makes it easy for end users to manage this filter/limit. 100 lines of code, just in order to do something that is built into ADP? Are you kidding me? -Aaron
Aaron, You are right, running query twice is not good, but that is quite simple query which should run fast. If you know any other way to open form fast, you are welcome to share. BTW TOP(number) clause or TopValues query parameter can be used in "classic" Access as well.. ADP doesn't allow use of local tables and doesn't have full support of MS SQL 2008, so for now some people doesn't use ADP at all..
Trying to customize the template "revenue forecast" in Excel. The template comes with 4 tabs, one is read me (which provides little assistance), a revenue forecast, calculations, and dashboard. the revenue forecast tab comes with 41 lines, I added another 40, and can't figure out how to manipulate the DSUM to include my new lines of data in the calculation tab. Went to IS Department, they couldn't figure it out. Anyone have an idea, or where is the best place to post this information? fjhawley@comcast.net
Comments: (loading) Collapse