Using different form strings to filter

Hello.  Is there a way to code a query to have it use a filter string
if a form is open, or ignore it if the filter is not open?

Example:

Query Name:  Query1
Form:  Form1 and Form2
Query fields:  Field1 and Field2

I have a string in field 1 and 2

Forms!Form1!Field1 in the first field of the query, and Forms!Form1!
Field2 in the second field.  Can I also have Forms!Form2!Field1 in the
query as well?  I have tried this, but a box pops up and asks for the
information from form 2 when it is not open.

Thank you.
0
nybaseball22
1/20/2008 4:06:37 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
541 Views

Similar Articles

[PageSpeed] 43

hi,

nybaseball22@gmail.com wrote:
> Hello.  Is there a way to code a query to have it use a filter string
> if a form is open, or ignore it if the filter is not open?
Yes, there are some ways to do it, I'd prefer the use of a "proxy" 
function in a standard module, e.g.

Option Compare Database
Option Explicit

Public Function ProxyFilter As Variant

   ProxyFilter = Null
   If CodeProject.AllForms.Item("yourForm").IsLoaded Then
      ProxyFilter = Forms![yourForm]![yourField]
   End If

End Function

And use the following condition in your query:

   WHERE IsNull(ProxyFilter) OR field = ProxyFilter

You need a function for each field you like to query.


mfG
--> stefan <--
0
Stefan
1/20/2008 4:19:40 PM
Thank you.  I tried this and it does not seems to be working.  I
inserted the code in a module (I think this is what you told me to do)
and entered the string you provided in my query field.  I am getting
an error : Invalid Syntax.  Any idea what I am doing wrong??

Thanks again
0
nybaseball22
1/20/2008 6:31:49 PM
nybaseball22@gmail.com schrieb:
> Thank you.  I tried this and it does not seems to be working.  I
> inserted the code in a module (I think this is what you told me to do)
It must be a standard module, not a form, report or class module.

> and entered the string you provided in my query field.  I am getting
> an error : Invalid Syntax.  Any idea what I am doing wrong??
What Access version do you use?

The SQL of your query should read like that:

SELECT *
FROM [yourTable]
WHERE IsNull(ProxyFilter()) Or [yourField]=ProxyFilter()



mfG
--> stefan <--
0
Stefan
1/20/2008 6:54:26 PM
Acces 2003.  I tried the SQL view insert, but when I switch back to
design view, it only shows ProxyFilter() in the OR column, and when I
execute, I get an error "Undefined function "ProxyFilter' in
expression.
0
nybaseball22
1/20/2008 7:18:30 PM
Reply:

Similar Artilces:

GetOpenFileName + Using own dialog template
Hi. I have a question: I am using the GetOpenFileName function to display the Windows FileDialog. I want to use the EXPLORER functionality and therefore I declared the flag OFN_EXPLORER in the OPENNAME-structure. Now here is the problem: I want to use my own dialog-template instead of the standard-file-open dialog. So i wrote my own template and set the attribute templateName of the OPENNAME structur to my own template. Furthermore i activated the flag OFN_ENABLETEMPLATE. But when I call the GetOpenFileName function with these properties I get the errorcode CDERR_GENERALCODES and the dialog is...

Form not populating
Hello all... I have a edit record form, which was working, until I added record navigation buttons to the form (memberinfo) that has the edit record button that opens it. I'm not sure where the problem is, but the eidt record form no longer populates the information. The filter shows the correct info, but the text boxes are all blank. Here is the code for the edit record button on the memberinfo form. -------------------------- Dim stDocName As String Dim stLinkCriteria As String stDocName = "EditMemberInfo" stLinkCriteria = "[MembershipNumber]=...

Continuous Form Use
I created a form similar to an Access form. It's for logging in phone calls and messages. Is there a way to set it up like in Access where a single form appears on 1 page but can generate subsequent pages within the file? Or is setting it up as a table the only way it can be done? Our IT contractor doesn't want me to use Access otherwise this would be a breeze. Any suggestions or help is appreciated. Thanks. You can spawn forms off a form, and access the data on that form. -- HTH RP (remove nothere from the email address if mailing direct) "ryjack" <ryjack@disc...

Comments using Validation input
A few days ago a question relating to comments grabbed my attention. The reply informed us that the comment in question was able to be seen by clicking the cell it was attached to and as long as that cell remained active the comment could be seen. So we have a comment which is not a comment visible only when the cell is selected as opposed to a comment which is visible when the cursor is over the cell. Sounds confusing but it achieves the result of showing a "floating" comment which remains in the same place on the screen even when it applies to a cell in a 'frozen' section a...

Receivable transactions using Integration Manager
Hi, I'm new to Integration manager and while trying to import receivable transactions of type "invoice" into Great Plains using Integration Manager, I receive the following errors: ERROR: Field 'Customer ID' does not have a default value. ERROR: Field 'Distribution.Distribution Account' does not have a default value. I am using a tab delimited text file for import. Customer Id is of the format 000001 Distr. Account is of the format 9999-999-99-99-99 Can anyone help me with this. Check each one of your mapping and make sure it says 'use source' an...

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

Help
My Regional Manager insists on things being perfect - need assistance with this problem. Working in Governemtn department - we have many users with unusal names and surnames - not common to standard dictionaries. My Rm wants to forward emails received with these peoples names in the original message - without outlook spell checking each of these names before sending. I have set up the email options under spelling - to ignore original message when forwarding - which it does but still spell checks the original message's recipients before sending. How do I set this up to exclude the n...

Filters
Hi I have a spreadsheet that contains many cols of which one displays dates. I very often had to select entries by date, so I apply the filter and select the require date, and then copy the results to another sheet to work on I would like a macro that would automate the entire procress Load the file, apply the filter and then copy the reults to another worksheet. I am stuck on how to pause the macro to input the the required date- which varies quite a bit. Will any one help please Thanks ...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

Print button on Report form
Hi all :-) =20 Access 2007 Pro Plus, Vista Ultimate SP2 I am preparing a small app for a friend who is handicapped, and am = trying to make things as simple as possible for them to use. I am trying = to limit the number of functions and clicks necessary to accomplish = tasks as much as possible, and would like to try an idea. I want to add a small button on the report form that will print the = report when clicked. I have done this on display forms to print as a = report, but, not on a report form. In trying to add such a button to the = report form in 2007 I am not being offered...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

Filters #3
In Excel 2000, I have a spreadsheet that the filter worked fine. I used it for a lot of "contains" filtering. It now appears that since I've added additional text to some cells, when I do a filter, I'm not seeing all the unique values in the drop down filter list. I've checked the area selected and this is fine. Any recommendations? Hi Katie There is a limit http://www.contextures.com/xlautofilter02.html#Limits You can use EasyFilter maybe (No problem there) http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Kat...

User forms #4
Rather than linking excel to a VB front end, how powerful are the VB userforms? If I want to create a front end of my excel workbook with sheets which will just display information that the cells calculate th how hard would this be? I also want to try and hide the fact that it is an excel spreadsheet? Thanks in advance Padrai -- Message posted from http://www.ExcelForum.com Shouldn't be too hard, especially if you know VB! To hide the fact the file is in excel you can do things such as hidin worksheets and also hiding toolbars when the worbook_open event i triggered. This can al...

List of fonts used in a Word doc -- the easy way
The VBA scripts that I have found all implement a character-by-character scan throu= gh the document, which can take a long time in later versions of Word. You can blame Wor= d's "formatting" feature for the slowdown. There=E2=80=99s a faster way: Save the document in .rtf format, then open the .rtf fi= le in a text editor such as notepad.exe. The fonts are listed in a font table ( {\fonttb= l ) located at the very beginning of the file. Each font entry looks something like thi= s: = {\f0\froman\fprq2\fcharset0 Times New Roman;} Any entries with a character set ...

Test linked form for data
Hi Groupies How can I test to see if a linked form, that might not be visible, has data? My main form has a yes/no box that enables a button when it is set to yes. The button opens a linked form. When the user moves to the next record, I would like the main forms BeforeUpdate event to make sure that the yes/no box is not set at No while there is data in the linked form for that record. I have tried something like: If Me.Form.frmVehicles.RecordsetClone.RecordCount > 0 And Me.CompanyVehicle = False Then Cancel = True MsgBox "Please check vehicles", vbOKOnly Me.f...

VC6/VC7 difference
ANother subtle change is that virtual functions must include the 'virtual' keyword is VC7 whereas in VC6 yhey did not. eg class A : public { func F (); }; class B : public A { func F (); } A* p = new B(); p -> F(); In VC6 this would execute B::F(), whereas in VC7, it executes A::F() unless 'virtual' is specified in class A::F. Not wrong, but something that is only picked up by detailed testing. I am surprised that in VC6 it behaved that way. Are you sure thats what happened in VC6? I dont have access to VC6 right now, so I cannot confirm it. ------ Ajay Kalra aja...

sending email from outlook using AOL
After much exasperation have finally got outlook to send email whilst using my AOL internet connection. The issue is that I have to have my aol email address entered in the Outlook email box but I use my outlook email address in the reply email address box. The problem is that any recipient of an email from outlook will see my aol email address in the from box and if they click on reply it will be sent to my aol email box and not to my outlook email box. If my aol email address is not entered, in the Outlook email box, the email is rejected by aol. Using AOL 9 & Outlook 2002 Any ...

Music files using "Package for CD" Option in Powerpoint
I am trying to burn my show onto a disk, and the show has songs linked to it. I know the songs are not embedded into the show, so I was looking for a solution for this. I saw the "package for CD" option in the menu and according to that, it is supposed to take your show, and all linked files and burn it onto a disk to be played back using powerpoint viewer. I made sure the box was checked to include linked files. After the disk burned, no music. I tried putting all the music and show in one folder and then running the package for cd tool, still no music. Is ther...

Conditional Summing across ranges using arrays
Hi all - I'm struggling to find a formula that will solve my problem: I have a matrix of numbers defined by a series of numerical column and row headers. RefCel 1 2 3 4 5 . . . m 1 a # # # # 2 # # # # # .. 5 b # # # # .. n # # # # # What I'm trying to do is for a give column header and a subset of row headers, sum the intersecting values. Let's assume that the subset are held in a named range Subset and the row and column headers are in range called RowHead and ColHead and...

Filter List Box
Hello, is there a way to filer data from a List Box like is done for forms? I will use 2 combo Box to filter data as follow: Box1Options: Active, Incative All (meaning: show all record, show only if active is true or only if active is false) Box2Options: Red, Green, Yellow, Purple (Show only record where Red is true, or Only these where Green is true and so on The selections can run in any combination, meaning that you may or may not have acombination from each combo box. Thank you, Silvio Thank you, Silvio The listbox can be populated from a query so there is noth...

Query by Form: Output to Listbox?
Hi, I have been asked to look at a database at work that has a query by form where the user can select a table to query. There are three columns of boxes on the form. The first column are drop-down combo boxes that allow the user to select the fields depending upon the table selected above. The next column of five are for the comparison operators (<,>, etc) and the third column of five boxes are text boxes where the criteria values are typed. There is one connector box that has two values: OR or AND. The form works and displays the query in a table view. This is the code behind the but...

filter #6
why does filter not show every choice in the column below...sometimes it does, but many times some of the cell's contents do not appear Autofilter will show only the first 1,000 unique entires. For a workaround, see Debra Dalgleish's Limits to Dropdown Lists http://contextures.com/xlautofilter02.html#Limits In article <CEF672C6-918B-40CF-85CE-08E9C9B1618B@microsoft.com>, "david bloom" <anonymous@discussions.microsoft.com> wrote: > why does filter not show every choice in the column below...sometimes it > does, but many times some of the cell&#...

Print preview difference in MFC42 and MFC71
When my project was compiled under VC++ 6.0, when print preview is activated, the preview window is an SDI window However, under VC++.Net 2003, the print preview is now also a MDI child window, how can I change this behaviour? ...

string concatenation
Please help me with this task: I would like to define each "cell value" a string of chars with a code name (for example, cell A1: coded name="101", val="The"; cell A2: code name=102, val="quick"; cell A3: coded name=103, val="brown"; cell A4 coded name=104, val="fox", etc.) Then some how on Cell B9 for example, I want to define this formular: "101" + "102" + "103" + "104" so that I can have the visible value in Cell B9 as: "The quick brown fox" And Cell B10, for example, anothe...

Using a txt string in formulae
Right this is an anoying little beggar but probably simple and im failing to see the wood from the trees so to speak ! right i have a workbook with multiple sheets and a front sheet the front sheet reports results of counts from the other sheets using =COUNT(sheet1!A:A) and =COUNTIF(Sheet1E:E,"Neg room") and other similair formula my question is this In column A i have the name of the sheet so I.E. A1=sheet1 which hyperlinks to the named sheet and columb B has the first formulae and so on and so on! my question is is there a way i can add the Word name of the sheet the Count form...