Query Filteration - From A Form

I want a query to return a result after filteration upon the basis of entries 
as per the entry in ID of either the form "Clnt" or the form "Trans", 
whichever is opened. 

However, if the "Trans" form is open the following query still asks for the 
Parameter Value "[Forms]![Clnt]![SetAc].[Form]![ID]" and ends if Cancel 
pressed.

SELECT COA.AcNo, COA.Acc, COA.Set
FROM COA
WHERE (((COA.Set)=[Forms]![Clnt]![SetAc].[Form]![ID]) Or 
((COA.Set)=[Forms]![Trans]![ID]));

How 2 overcome/resolve the same?

-- 
Thanx & Best Regards,

Faraz!
0
Utf
3/3/2010 11:34:01 AM
access 16762 articles. 3 followers. Follow

3 Replies
883 Views

Similar Articles

[PageSpeed] 54

You can't do that.

You've asked Access to evaluate two expressions. The query passes the first 
expression to the Expression Service. If the first form is open, the ES 
returns the value. If not it pops up the parameter dialog. Then it passes 
the second expression to the ES, and the same thing happens. Therefore, the 
only case where no parameter dialog appears is when *both* forms are open.

I take it from your question that you want to resolve either, not both. 
Therefore you cannot use a parameter in the query. You must use another 
approach, e.g.
- different queries for each form, or
- omit the parameters from the query, and apply a filter to the form 
instead.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Faraz Ahmed Qureshi" <FarazAhmedQureshi@discussions.microsoft.com> wrote in 
message news:4688A684-5A4D-425E-8C2B-BAA4226851FB@microsoft.com...
> I want a query to return a result after filteration upon the basis of 
> entries
> as per the entry in ID of either the form "Clnt" or the form "Trans",
> whichever is opened.
>
> However, if the "Trans" form is open the following query still asks for 
> the
> Parameter Value "[Forms]![Clnt]![SetAc].[Form]![ID]" and ends if Cancel
> pressed.
>
> SELECT COA.AcNo, COA.Acc, COA.Set
> FROM COA
> WHERE (((COA.Set)=[Forms]![Clnt]![SetAc].[Form]![ID]) Or
> ((COA.Set)=[Forms]![Trans]![ID]));
>
> How 2 overcome/resolve the same?
>
> -- 
> Thanx & Best Regards,
>
> Faraz! 

0
Allen
3/3/2010 1:27:35 PM
Both the "Clnt" AND the "Trans" forms need to be open. However they can be 
minimised or even hidden. When you open up the Trans form, you could have it 
open the CInt form and hide it. The same could happen with the CInt form 
opening the Trans form as hidden. 

Put code in the Open event of the Trans form:

DoCmd.OpenForm "Clnt", acNormal, , , acFormEdit, acHidden

Do something similar for the CInt form.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Faraz Ahmed Qureshi" wrote:

> I want a query to return a result after filteration upon the basis of entries 
> as per the entry in ID of either the form "Clnt" or the form "Trans", 
> whichever is opened. 
> 
> However, if the "Trans" form is open the following query still asks for the 
> Parameter Value "[Forms]![Clnt]![SetAc].[Form]![ID]" and ends if Cancel 
> pressed.
> 
> SELECT COA.AcNo, COA.Acc, COA.Set
> FROM COA
> WHERE (((COA.Set)=[Forms]![Clnt]![SetAc].[Form]![ID]) Or 
> ((COA.Set)=[Forms]![Trans]![ID]));
> 
> How 2 overcome/resolve the same?
> 
> -- 
> Thanx & Best Regards,
> 
> Faraz!
0
Utf
3/3/2010 2:01:01 PM
"Faraz Ahmed Qureshi" <FarazAhmedQureshi@discussions.microsoft.com> wrote in 
message news:4688A684-5A4D-425E-8C2B-BAA4226851FB@microsoft.com...
>I want a query to return a result after filteration upon the basis of 
>entries
> as per the entry in ID of either the form "Clnt" or the form "Trans",
> whichever is opened.
>
> However, if the "Trans" form is open the following query still asks for 
> the
> Parameter Value "[Forms]![Clnt]![SetAc].[Form]![ID]" and ends if Cancel
> pressed.
>
> SELECT COA.AcNo, COA.Acc, COA.Set
> FROM COA
> WHERE (((COA.Set)=[Forms]![Clnt]![SetAc].[Form]![ID]) Or
> ((COA.Set)=[Forms]![Trans]![ID]));
>
> How 2 overcome/resolve the same?
>
> -- 
> Thanx & Best Regards,
>
> Faraz! 

0
De
3/13/2010 5:50:36 PM
Reply:

Similar Artilces:

Sub Form Combo Box
I don't understand why this code does not work. When the user enters a date in a text box the subform combo box (CA_NAME) should only show two choices. But nothing happens, no error, nothing.. Help please.... Private Sub TR_ACKNOWLTR_GotFocus() If Me.TR_ACKNOWLTR <> " " Then Me!frmCaseLog!CA_NAME.RowSource = "Select * from tblCommitteeName where [C_Committee] = 'AG'or 'AL'" End If End Sub "Dan @BCBS" <DanBCBS@discussions.microsoft.com> wrote in message news:F6A659C4-1110-449F-91F5-23FD17A41014@microsoft.com... >I...

Query and Count no records Want a Zero
I am trying to do a simple query and count at the same time and if I don't have any results that match it doesn't give me a value of zero. It doesn't even give me a row. What is my solution? You probably use a where clause. The WHERE clause is evaluated first, before the GROUP/COUNT, and if no record passes the condition, no record would return. You can use: SELECT SUM(iif( condition, 1, 0)) FROM table or SELECT COUNT(*) FROM (SELECT * FROM table WHERE condition ) If you have a lot of records, the second expression could be faster, since it MAY use indexes, ...

Result of crosstab query in form
Hi. In Access 2007 I'm trying to display the result of a crosstab query in a (continuous) form. In the Form_Open event I fill the label Caption and text control ControlSource with the field names from the query. But when I open the form the text controls display the #Name? error. Even when I fill the control sources with the correct field names (selected from the list) I get the message the name is not in the list... What is the solution? Thanks in advance, Ronald. Ronald wrote: >In Access 2007 I'm trying to display the result of a crosstab query in a >...

display selection in form
Hi, In a form I display products. One of the fields in the records is "supplier". Beneath this field I placed a commandbutton "details". Clicking this button shows details of the supplier. These details are stored in a query called "supplierinfo". I built a form "frm SuppInfo"that displays the info of this query. How can I display only the matching record of "supplierinfo" Example: the value of "supplier" in the form products is "Johnson". Clicking the commandbutton must display the record in the query where the val...

Internet Queries
I am importing data from the internet but just selecting a small numbe of tables from the internet pages (Some share price data and analyst estimates). My problem is that every so often the selceted tables ar de-selected and either no tables or random tables are pulled throug instead. I have not been able to write a macro to pull through just th selected tables. Has anyone else had this problem? Is there a way roun this. Example page imported would be: http://uk.finance.yahoo.com/q/ae?s=LLY Do a Data, Import External Data, New Web Query Tables selected are Company Name: Eli Lilly &...

Using Bound Form and controls to inquire and update with other requirements
Most of the stuff I have done in Access mainly dealt with unbound forms due to the fact that I have issues with how error checking is done. It's been a long while since I have worked with Access and now having to get back into it, but only this time with bound forms and controls. I am still needing to use my custom error checking code to get around the fact that without such code, when doing data validation on individual controls (when moving from one control to another) as opposed to doing data validation on the whole form (moving from one record to another record), data validati...

Replace Option Group Value from form with text in report header
Hi all, Question: How can I convert the numerical option group value from a form to a string value and place it in the control source of a textbox in a report header? I have a form used to 'build' reports: users choose a query, a report, and then the rest of the controls on the form are used to build a 'where' clause for the DoCmd.OpenReports statement attached to a command button which opens the report based on all the user's choices. I've tried creating a variable on the report in the declarations area and then using a select case statement in the 'open&...

Querying Data from Access
All, I am using the charting features in Excel 2007 to build various charts from data stored in Access. I want the user to be able to select the Region, Country, or Site that he wishes to see data for, along with a range of dates. I have queries in Access that will summarize the data the way I need it. What I would like to know, is how do I "paramaterize" the queries so that I can use the dates the user chooses, for example? Thanks, Steve Here is a good example from EXCEL VBA 2003. for I think what you're wanting Public Sub CallStoredProcedure() Const Co...

Junk Mail Filter Rule
Is there a way I can set up a rule that looks for an attachment, "DELETED.TXT", for the email and the attachment to be deleted. (So far I find a rule that searches for an attachment but it just looks for any and all.) TIA What version of Outlook? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, RFJ asked: | Is there a way I can set up a rule that looks for...

Advanced "mixed" filter possible?
Is it possible in Outlook 2003, to make an advanced filter so that I filter out every mail with attachments (to my unwanted-email folder), except those emails from my friends ...? Regards Morten ...

Repeat data from previous records in a form
I am creating a a form for data entry and want to know if there is a way to repeat data in fields from the previous record (or default to previous record). For example, I user has 100 entries - I would like to set up the form so they do not have to enter their ID each time they enter a record. When you hold the ctrl key and click the apostrophe key ' access copies the contents of the same field from the previous record. This assumes that the new record immediately follows the old record so you may have to sort the source table and requery it after each time a new record is inserted...

Filtering a combo box
I have a spreadsheet with 2 combo boxes. The first box calls from a list of 5 items. The second box calls from a list of 50, and they are related to combo box 1. How do I filter combo box 2 so that only items related to combo box 1 show in the list? These combo boxes will be copied down the page for multiple selections. Is this possible and if so how ? .....Thanks Hi Steve, I don't know if you're committed to a combo box or not. If not you might consider using data validation, which will probably work much faster than a combo box. Debra Dalgleish has an excellent websi...

Filter a List Box Using a Combo Box
I have a dialog box that contains a list box of all my records; basically this is used as a way for a person to find/edit a previously-entered record (select from list box and click to edit/view). The list box is based on a simple query. I want to add a combo box that will allow me to filter the list box by one of the fields - so the user could select what type of record he/she would like to see, and the list box would automatically filter. I know I need an After Update event on the combo box, but I can't figure out the code. The complicating factor is that there are other filter...

Forms Drop down list
I created a form that works from a table. From the form you can enter information and it will put it into the table. I would like to create a field in the form where the user can selects what to be inputed from a drop downlist. how would I do that? You need to first create another table which contains the values you want to list, each as a separate row in the table. If the values to be looked up are unique, then the 'referenced' table and the 'referencing' table can have the same column, e.g. a States table could have a State column, and the referencing table, e.g. a ...

Frequency per month (advanced filtering?)
Hi, I have a long list looking like this: Box number Ship date Dealer code 343 2008-12-12 SE1212 344 2008-12-12 SE1212 345 2008-12-12 SE1212 346 2008-12-12 SE1212 348 2008-12-12 SE1213 349 2008-12-12 SE1213 350 2008-12-12 SE1213 351 2008-12-12 SE1213 352 2008-12-12 ...

How do I develop an employment agreement form
Need assistance in developing an employment agreement form Specifically what kind of help are you looking for? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Quirky" <Quirky@discussions.microsoft.com> wrote in message news:016FABD1-D4CC-422C-B4ED-A52D52C1E44F@microsoft.com... > Need assistance in developing an employment agreement form ...

How to..use worksheet form and save data to an Excel list
I'm using Excel 2003. Is is possible to create a worksheet form where the user enters data, saves the data and the data is then saved to the last row in an Excel list? Once the data is saved by the end user, I would like the form to refresh so that the end user can continue entering additional data. Does anyone know of any documentation or examples I could review regarding the above as I have never done this before? TIA. Maybe just Data|Form would be sufficient. If that doesn't fit your needs, look at John Walkenbach's enhanced data form: http://j-walk.com/ss/dataform...

Hiding forms
Hi, Is any anyway to hide all the forms in a database which is much like the hidden tables as show below. Set db = CurrentDb For i = db.TableDefs.Count - 1 To 0 Step -1 db.TableDefs(i).Properties("Attributes").Value = dbHiddenObject Next Thanks This code snippet loops through the AllForms collection and sets the Hidden attribute to True. Dim lngLoop As Long For lngLoop = 0 To Application.CurrentProject.AllForms.Count - 1 Application.SetHiddenAttribute acForm, _ Application.CurrentProject.AllForms(lngLoop).Name, True Next lngLoop To reverse the process: Di...

Query Date Range
How can I use Between/And to prompt for date range that includes the start and end date? Hi, Design a select query for your table. add all fields to the design grid. In criteria box of the date field write this Between #BeginningDate# And #EndingDate# and run the query to find results. JB "karenfocus" wrote: > How can I use Between/And to prompt for date range that includes the start > and end date? My current criteria is in a query date field and is written Between [Enter start date] And [Enter end date] but when I run the query from the first day of the month to the...

What are you ASAPI Filters
I think I may have accedently removed a ASAPI filter that was installed into IIS when I installed CRM 1.2. What do you have installed and where are the excecutables located? ~n there is a file called CRMISAPI.dll that .SRF mappings use to start with. -- John O'Donnell Microsoft CRM MVP "nborders" <nborders@yahoo.com> wrote in message news:920248ec.0407271026.540414df@posting.google.com... > I think I may have accedently removed a ASAPI filter that was > installed into IIS when I installed CRM 1.2. > > What do you have installed and where are the excecutab...

Importing image into Image Control on a Form
Access97 I have a form for spare parts information and selection. For the parts I have a library of jpg files held in a shared drive. When the operator selects a part number in a list I have the image appear in an Image control on the form - this is working very well and is fast. But there is a pesky "Importing [image path and filename]..." box flashing momentarily on the screen each time. Is there a way to suppress/hide this pop-up ? Any help gratefully appreciated. Piri Check http://www.mvps.org/access/api/api0038.htm at "The Access Web" -- Doug Steele, Microsoft A...

Running Windows Media Player from MFC Application Query?
Hi all I am making an application in which i displaying in a list control all the mp3 files belonging to a folder selected by the user. I want to add the following functionality. As soon as the user double clicks on any of the displayed MP3 file, i want to invoke the windows Media Player and play that MP3 file. Now how i invoke the Windows Media Player for my MFC Application? Waiting for suggestions Regards ...

Multiple Muliselect Listbox form update
Hello, I am trying to work though getting multiple muliselect listboxes to work. Got the first one to work but I can't get the second one to work. They are going to be cascading to filter a subform. my code that doesn't work is: Private Sub BarType_Click() Dim MyDB2 As Database Dim qdf2 As QueryDef Dim i2 As Integer, strSQL2 As String Dim strwhere2 As String, strIN2 As String Dim ctl2 As Control Dim frm2 As Form Dim varItm2 As Variant sDocName2 = "BarInquiry SearchBarTypes" Set MyDB2 = CurrentDb() Set frm2 = Forms!BarInquiry Set ctl2 = frm2!BarType ...

Find Duplicates Query
I have a Find Duplicates Query based on the "Name Field". Some sample results are below. John Doe 12/3/2007 John Doe 12/15/2007 John Doe 1/28/2008 Is there a way to further restrict results to only return results that the date fields are within 30 days of other? So in the above example, the bottom result would not be returned because it is not within 30 days of any other results. Any help is greatly appreciated. Thanks, Matt. Use a subquery to identify whether there is another record within 30 days. This kind of thing: SELECT ID, [TheName], [TheDate] FROM ...

Protect Form and Table
Hello, could anyone show me the best way and quick way to protect the Form and fields at the Table from authorized change. I try to hide the table and put a password on the form, however, it's still too risky. User still can unhidden the table and change the data without going through the Form. Any assistance would be greatly appreciated. If you 1. convert the final product to an MDE file so the user can't get to design changes on the form. 2. go into Startup options and remove the default menus and add your own custom ones. EITHER 3 OR 4 should be done, but both don't...