I have two Parent Forms on one screen, no Sub Forms. So they are not linked as Parent and Child. The First form applies a filter to the Second form. The First Form contains a field called "TaxYear". You double click the TaxYear field (running the code below) and it filters the second form so that the Second Form SPYDTaxYear = the First Form's TaxYear.
The filter by SPYDTaxYear works fine. I need to quickly add a second criteria to the filter called, "PropertyID", an integer. So, on the First Form you double click on the TaxYear field and it should filter the Second Form by both SPYDTaxYear and PropertyID.
Code:
This filter by Tax Year works fine.
Private Sub TaxYear_DblClick(Cancel As Integer)
Dim stTaxYear As String 'From First Form
stTaxYear = Me.TaxYear 'From First Form
Forms![Frm_RecordstatusFrame]![Frm_SPYDTaxYears2].Form.FilterOn = False
Forms![Frm_RecordstatusFrame]![Frm_SPYDTaxYears2].Form.Filter = "[SPYDTaxYear]= '" & stTaxYear & "'"
Forms![Frm_RecordstatusFrame]![Frm_SPYDTaxYears2].Form.FilterOn = True
The above filter works but it needs a second filter criteria of the PropertyID, an integer.My modification of the above filter after adding [PropertyID] errors out with a data type mismatch.
So the only thing I need is correct the filter so that it includes SPYDPropertyID which is an integer.I am prepping for a demo today at 10:00 AM. This is the last issue that is still not working correctly.
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I have a query that retrieves one of its criteria from a form. I have referenced correctly the value on the form from the query, and it works, but what I wanted to do is a bit more complicated: when the form is closed, I want to launch the query with a "default value". I tried to do it in 2 different ways:
a) Defining an "IIf" at the query criteria: I would need a function that checks if the form from which I retrieve the values is open. b) Defining public variables with a default value, which would be changed from the form: I don't know where/when to initialize the value of the variable.
Query gets criteria from form when it's open. If form is closed, query uses default value.
I was wondering if it was possible to add a parameter to the record source Query of a form depending on the value of an other field on the open event of the same form? I was thinking to use 3 Sql statment SQL1, SQL2, SQL3, on the open event of the FrmOpen. On Open then the RowSource of FrmOpen =SQL1 If field? = "A" Then the RowSource of FrmOpen=SQL2 Else the RowSource of FrmOpen=SQL3 End If I did try but it doesn't work so I needed to know if it was possible to achieve this or not?
I have a table Names containing Recnum and Last Name. I have a table Grades including Recnum, Names and Grades. I have a Report which Through a query Reports the grades and averages for each name. I would like to be able to query for an individual name using an entry form. How do I get the name from the form into the Criteria in the query grid.
I have a form that has seven unbound fields named txtCityCriteria1 through txtCityCriteria7
The idea is to have the use type in up to seven cities and have this applied to the criteria of the city field in a query. This query is the datasource for a report. I know how to do a single text ....
[Forms]![frm_OpenReport]![txtCityCriteria1]
in the criteria row would do the job. But how can I do seven?
Oh and if all are left blank i need it to open another form that informs them of their mistake.
I have looked through these forums and have found several possible solutions but I can not get any to work. Any assistance would be greatly appreciated. Take it easy on me I am relatively new to this code thing. Used to do everything in macros.
I have a form I am using to either select an Equipment ID or pull all records if no id is selected. My code in the query looks like the following:
([Forms]![frmWOCommentRpt]![cboEquipID]) Or Like "**"
When I run this, however, it only does the Or part. If I select an equipment ID on the form, it doesn't just pull that. I'm sure this is a simple fix, but I can't see it. Any ideas?
Hi all - I have a suite of reports which provide customer details. They all require the user to input the name of the customer.
The way it works is that I have a query which returns everything for the customer.
Then I have other queries based on the first query which bring back the specific information I want regarding the customer.
This works fine when the customer's name is entered using [Enter Customer Name] in the criteria of the relavant field in the first query.
However, because a user might want to run several different reports on the same customer it becomes a chore for them ot repeatedly have to enter the customer name.
My solution is to have an unbound field in the reports menu for the customer name and to use this as the criteria in the query, so it now reads [Forms]![FrmMenu]![Text42].
The first query runs fine, but the queries based on it are suddenly not returning any records.
There is undoubtedly a simple explanation for this but as I'm quite dim, I can't see it. Could somebody enlighten me and maybe even suggest a solution?
I have looked through the forum, found some things, but not sure if they help with what I need specifically. I tried looking at the microsoft website, but can't really follow some of the things they have there.
On clicking a form (called Manager), I would like a list of some fields from a query (called queryMgr) to pop up, so that I can put in the criteria as I would if I just used the query, and then submit or run, and the form gives the filtered results. I don't want users to open the query and put in their criteria, I just want them to go to the form directly
When I click the form, I want the following to pop up at the same time so I can fill in the criteria:
Date Vendor Number Invoice Number Check Received (this is a Yes or No field)
and then when I click submit or enter, the form gives me say 5 records that match this criteria.
I have a macro that runs 2 queries, they both have criteria that needs to be passed over. how can i do this? I currently have it prompt but how do i get it to pull from the form the macro trigger is in? I also have a report that runs from a query in that macro also. i dont' want the user to have to enter anything since the critereia is in the form. thanx
Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form. I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this
Not sure if this is in the right forum. :confused:
But here goes, my question is;
Is it possible to update the criteria by using a form?
What we are looking to accomplish, is a report that prints out labels in relation to the customers that are chosen in the query. What we want the user to be able to enter multiple Company ID's on this form, which are then picked up by the query and the companies name and address printed out via the report.
The printing out of the report from the form we have done.
The below SQL gets it's criteria from a form but if the form has no Date1 and Date2 values I would like it to return all records. I could not tinker it into submission. :confused: Any pointers would be great.
SELECT [Central Western Region Database].[Date of Term], [Central Western Region Database].[Agent Number], [Central Western Region Database].[First Name], [Central Western Region Database].[Last Name], [Central Western Region Database].[District Name] FROM [Central Western Region Database] WHERE ((([Central Western Region Database].[Date of Term]) Between [Forms]![Compliance Lair Reporting]![Date1] And [Forms]![Compliance Lair Reporting]![Date2]) AND (([Central Western Region Database].Status)="inactive") AND (([Central Western Region Database].[Employee Type])="agent")) ORDER BY [Central Western Region Database].[Date of Term];
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
I am working on a database which has many queries that count different statistics of clients (race,gender,ect) during specific date ranges. I would like to set up a form that you could go in and enter a date range into a text box and it would update the criteria (date range) for each of the queries. How would this be possible? I have looked at some past threads similar to this but they didn't help.
Hi all, I have a select query that pulls in two date values from a textbox on a form.
SELECT DISTINCT Table01.SRV_NAME, Table01.IMP_CUTOFF_DT FROM Table02 INNER JOIN Table01 ON Table02.Servicer = Table01.SRV_NAME WHERE (((Table01.IMP_CUTOFF_DT)=[forms]![frmreport].[txtsqldate].[value])) ORDER BY Table01.SRV_NAME;
However when I run it I get the following error message:"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may...." but when I copy and paste the value of that txbox from the form into the query it works fine.
Can anyone see where the problem lies? I can't work it out.
I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].
I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?
What I have is:
IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)
Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?
Hello, ...I'm not sure if I'm making this question more complex or not or if it's even possible? (I would think so) Anyway...What I am trying to do is create a form in which I can input more than one criteria into a field - We will cal it txtSetValues. Then via a button open a query that will use txtCode as the criteria for searching records in a table. The table is called: tblMASTERTABLE-EmpCount query is called: qryEmpCount
The table currently only has two field; ORG and Name. The criteria I have in ORG is: Like [Forms]![frmEmpCount]![txtCode] I currently can find any specific ORG just fine. I can also use the wildcard and that works fine. But how can I set it up so that I can enter in multiples... For example, I would like to find all employees that there org is either; AL001 OR AL010 OR AL100 - The wildcard will 'Almost' do it in some circumstances, but it may gather some orgs that I don't want in this list. I hope I am explaining this in an easily understood fashion?
Thanks, ---roystreet P.S. Now I know I could just make multiple fields in the form (ie; txtCode2, txtCode3,etc) and then just add those as criteria in the query, but I was hoping that I wouldn't have to do that.
I have a patient form that has a subform showing the care plans. At the side of each care plan for that patient i have a button that if clicked will take you to the specific care plan. I need this to open the care plan form based on multiple criteria which is
Date (Date Field) Care Plan Number (Numeric Field) Patient number. (Text field)
These fields are all on the subform and will be populated.
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
Hello all, I have edited my question to be clearer as I realize some might not understand what I posted earlier.
I have a form with the source from a query. I have about 15 fields in my query, but users of the query regulary use about 5 of the fields.
How can I design my form such that on clicking the form, a set of text boxes comes up with fields from the query. I found a post that I could use something like this in my query but that I needed to create a button on the form that would run the query. Here is an example of the criteria I put in one of the query fields:
[Forms]![Manager]![Vendor] or Forms![Manager]![Vendor] Is Null
The problem is, on opening the form, I get a Name# error on each of the fields that have criteria in them.
Also, is there a way that I don't need to use a comman button on the form form, just by doubleclicking it will bring up text boxes and then I can put in the criteria needed.
What happens if I don't fill in one of the text boxes, would it only return results when that field is blank?
