Appending Where Clause to an Existing Form - Access Developers


	Back to Access Developers Comments List

Appending Where Clause to an Existing Form

Tanin Uthayanaka

19 months ago

Hi,

I have a continuous form which has a where clause to control who has access to form.

I would like to add a search text box (e.g. search order#) in the header section which would append the search criteria to the existing where clause.

Thank you in advance.
Tanin

NOTE: I have already enabled the filter option for the user to this themselves, but I would like to make it easier for some user to just enter the criteria in the text box.

	Thomas Gonder @Reply 19 months ago
Maybe try something like: Me.Filter = "[OrderNumber] =" & tbx_SrchOrderNumber Me.FilterOn = True Put this in the AfterUpdate of tbx_SrchOrderNumber and then set Me.FilterOn = False if the text box entry is Null.

	Tanin Uthayanaka OP @Reply 19 months ago

	Tanin Uthayanaka OP @Reply 19 months ago
The image I uploaded may look familiar cause I'm using it as an example :) I am not sure if it would be better to use "where clause" to search instead of "Filter" since I already options to filter the form?

	Tanin Uthayanaka OP @Reply 19 months ago
I found the code I was looking for :) Dim varCDB As DAO.Database, varQDF As DAO.QueryDef Dim varStart As Integer, varLen As Integer Set varCDB = CurrentDb Set varQDF = varCDB.QueryDefs(Me.RecordSource) strSQLStart = varQDF.sql

	Thomas Gonder @Reply 19 months ago
When you say "I already options to filter" I have no idea what you mean as there are so many different ways to filter a form either with a source, VBA code or form properties, to name a few. Exactly what "option" are you using?

	Tanin Uthayanaka OP @Reply 18 months ago
You are correct. There are many ways to search a form where Filter is probably the easiest. I was just trying to learn how to get existing SQL from a form and manipulate the "WHERE" clause for my education. Though it took some coding, I was able to do as I wanted :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen