Does anybody know how to have a text box on a form "satisfy" a parameter in a query? I want to enter two dates and have a subreport show information from them? (The records shown will only be between those two dates) How do I do this? (I don't want the parameter to pop up on form open) Something like (Date1) = Parameter1 (Date2) = Parameter2
View RepliesHi. I have a parameter query viewed in a form.
How do I show the results in a list rather than singular?
Other than a report...
Thanks!
Hi, I have a combo box based on a parameter query, which is on a sub form. I want the query to use the existing value from a field on the main form as the parameter, without getting the "Enter parameter" box. please help.
View 3 Replies View Relatedhow to get a Parameter from a form into a query.I have a form with a subform and a table. However I want to enter a number in the Form and then the subform shall display all entries from the table with that number as a specific field. So to speak i want to apply a filter. Can i do that somehow without using VBA ? And without entering the number in that tiny dialog window that pops up when i use "[ ... ]" brackets in my query ? This is may query so far .... and it is the datasrc for my subform ...
Code:
SELECT tbl_autos.ID, tbl_autos.Typ, tbl_autos.Alter, tbl_autos.BesitzerID
FROM tbl_autos
WHERE tbl_autos.GaragenID = <Param>;
i want to replace <Param> with the content of my form.
I know this is probably a basic question- but Im not finding a clear answer here.
Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.
How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!
I have a report query that uses a combo box on a form to collect the parameters. I would like to be able to include an option that would be like not having any criteria at all, to show all the records. I have tried several combinations in the criteria to get it to work and haven't found a way yet.
IIf([Forms]![MyForm]![Mycbo]<0,Like"*",[Forms]![MyForm]![Mycbo])
I have tried various different versions of what you see above and none have worked. The true part and the false part both work if separated and tested. Is it possible to make this work or is there better way to do this?
Hi
I am struggling with what seems like should be a straightforward task. Unexpectedly however it has become an infuriatingly difficult one (no doubt due to my complete novice status).
I have a very basic d/base (3 tables) that I have been searching using basic SQL queries. I want to create a simple 'search' form that produces the results of my various queries without the need to work in SQL. Enter one or more search criteria, hit ENTER, results presented in datasheet perhaps?
Thought this would have been easy but I've had no luck. Can you please explain how I can pass a parameter from a form to a query?
Many thanks
Stuck21
I am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
I have always used parameter forms for inputing query/report criteria. However, they have always been for one query or report. However, all searches I have done for trying to use one form for several queries all I find is a simple response of "Create a form and then direct all the queries to grab the parameter from the form.". Sounds simple enough but I can never get it to work.
I have created a macro that runs 12 Append queries. I have also created a parameter form that has an unbound combo box. The combo box is fed by a query that gathers all the Data Dates for the user to select from. So if there are 24 different data dates then the combo box displays them all.
I tried creating a macro that first opens the form then opens the query, however, it runs the query before I can finish entering the criteria in the form. Then I just get a pop up of "You are about to append 0 records..."I have found several walk-throughs for having a query use a form to grab parameter, but have not been able to find one that shows you how to set up one form to run several queries.
I keep trying to find an answer, then give up and continue manually entering 12 dates for 12 queries when they pop up after running the macro. Now I need to create a macro that runs 24 queries and I don't want to have to go through 24 parameter pop-ups.
i have a question concerning setting of parameters in a query:
I have a query with a parameter and a form with a control ... however, i want the value of the control to be used as parameter in the query.
For example a checkbox "chkb". Now i specify a query as RecordSource (or ...set ... i'm not sure) for a subform and i want it to change the parameter-value with the value of "chkb".
Is it just necessary to assign the name of the parameter correct (in the query-definition, put smth like [Me].[chkb].[Value]), or do i have to set the parameter in VBA ? And anyway, is it clear what i mean ?
I have a pop up form that is run by a query that needs to grab the parameter value from a field on the open form. It grabs the value for "QuestIDfrm" from the form "frmQuestionDetail" using the statement below pasted into the query parameter.
Code:
[Forms]![frmQuestionDetail]![QuestIDfrm]
This works fine when I call the pop up from the form "frmQuestionDetail". What if I want to call this same pop up form from another form that has the same control (QuestIDfrm).
Is there a generic syntax that would grab QuestIDfrm from the current open form? Something like
Code:
[Forms]![CurrentOpenForm]![QuestIDfrm]
Hi-
I have an Access DB with several different forms all relating to one master table.
Each record in the table can be resolved=yes or resolved=no.
Right now each user must click the resolved box to resolve the entry and then enter the date.
I have created a button that will run the update query below.
UPDATE [TBL Master] SET [TBL Master].[Resolved] = Yes, [TBL Master].[Date Completed] = Now()
WHERE ((([TBL Master].[Primary key])=[forms]![frm name]![primary key]))
WITH OWNERACCESS OPTION;
The query allows me to update the record on that form efficiently. However, I do not want to create one query for each form (30 plus) for this.
Is there a way to use the form that was used when the button was clicked without naming the form specifically?
I have a form with a main form and a subform, main form's function is to filter the data's inside the sub form using a combo box, right now I have one combo box which filters the data according to their control no so the output if I use say CtrlNo 1 in the combo box the data that will reflect in the subform would be the data's of CtrlNo 1 but now I'm planning to add another combo box that will filter who supplied that CtrlNo.
What I did is I used the same method from my first form that filters the CtrlNo but in this form I replaced the CtrlNo combo box to Supplier Combo box it worked great but now what it made me think is it possible to put this two filters in just one form?combo box 1 is for CtrlNo combo box 2 is for Supplier.
When I tried to create this it failed, when I filtered only the CtrlNo subform doesn't reflect any data this goes the same for the Supplier combo box but when I tried to choose a filter for both combo box say CtrlNo 1 and its Supplier this works fine but what I want is to have both filters to work even if I did not use both parameters is there any way to achieve this?
The items in my database:
Form - Customer_Data
Form - ConsignmentGuide
Query - Customer_Data Query
Text Field - GBL
Text Field - Text89
Text89 is using an Expr from query
Expr6: Left([GBL],4)
What happens is Expr6 will take the first 4 Letters of GBL. I want to double click GBL an bring up the ConsignmentGuide based on Text89.
Code:
DoCmd.OpenForm "ConsignmentGuide", , , "Text89='" & Me.Expr6 & "'"
My problem is when I do this I get a pop up asking for a Parameter Value. but there is text in Text89. How to I get a round the Parameter Value box coming up an automatically do this. If I type the first four letters in to the Parameter Value it finds it on Consignment Guide.
I have a parameter query using a form to collect the data. I put LIKE and wildcard enclosing the parameter to bypass the criteria if the data box is empty. It works well. I have another parameter of date type that uses between two dates. How can I bypass the criteria if I leave the data boxes blank?
View 8 Replies View RelatedI want to set parameter in my select query with or function as in detail.i have two form with same field
1-with field A
2-With Field A
now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it
I want to build a form that allows a user to book an appointment.
I want them to be able to see a choice of their own or other people's appointments for the day so they can choose a time.
I'm told i can build a query to show the appointments and put that in a subform on the form.
My question is this: to select which person's appointment to display and which day, i need to give the query parameters. I don't really want to do it with the parameter query popups. What i'd like is a couple of pull down lists at the top of the timetable to select and display the person and date.
Is this possible? Should i put the pull down boxes on the form or in the query?
(I'm using a2007)
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
I had created a query that I was attempting to reference a text field in a form that was open when the query is run. It wasn't working and whenever I opened the form it would prompt for the parameter before opening the form.
I ended up deleting the query to try again from scratch. But the form still asks for the parameters whenever I open the form. Why is Access still prompting me for the parameters?
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies View RelatedI have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
Hi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
Hi all,
stuck on this, not sure if it can be resolved....
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
Can this be resolved?
Help most appreciated!
regards to all
K