Queries :: Control Source - Parameter Prompt Multiple Times
Jul 15, 2014
I have a form using a query as a control source so that is prompts the user for a parameter when they open the form.
I also have a listbox that is calling the same query.
The issue I am having is when I open the form it will prompt me twice to enter the same parameter.
Tables:
tblship
tblconsignee
tblbill
tblinvoicedetails
Query
qinv
Form
frminvoice
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
View Replies
ADVERTISEMENT
Mar 30, 2015
I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.
I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format
The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)
So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.
How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)
The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.
View 3 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Aug 15, 2011
I have a report with a subreport. I have added a parameter to the query to prompt for [manager] or is null to get all managers. However, on the report when I enter a manager my count on the subreport still returns the count for all. So, I added the same prompt to the query for the subreport. It returns the right count, however, it prompts for the manager multiple times. How do I get it to prompt only once yet get the right total?
View 1 Replies
View Related
May 29, 2014
I have created a query with parameters and a report from the query. The report is requesting the paramater multiple times but the query is running fine. How to resolve? Again, the query requests the parameter once but the report multiple times (6x).
View 3 Replies
View Related
Apr 28, 2007
I have a text box in a report, with it's control source property set to =[BegDt].
Upon loading the report a message box pops up and asks the user for the value of [begDt]. This occurs 3 more times for 3 other parameters.
Now I want to change the design of the report so I decided to put these 4 parameters on a form so the user can fill in the 4 values all at once rather than responding to 4 message boxes that pop up when you run the report.
The problem is now the control source properties in all the different text boxes need to be changed to [forms]![frmABC]![txtBegDt] etc.etc. There are over 100 text boxes in this report that make reference to the 4 different parameters.
Is there a replace command I can use to change all the occurrences in the text boxes or perhaps a simpler way would be to pass the values to the report through code.
I tried placing this in the report header's format event and it doesn't seem to have any effect.
Code:Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)begytd = [Forms]![frmABC]![txtStDt]endytd = [Forms]![frmABC]![txtEndDt]curmo = [Forms]![frmABC]![cboMo]curyr = [Forms]![frmABC]![cboYr]End Sub
I should mention that the report uses a table (tblRecap) as it's record source. I tried using a query that refers back to the form for the parameter values but that means I have to use the query as the reports record source and that creates a problem because the report needs fields from the table to perform calculations.
Can someone help me with a solution please.
Thanks for your time.
View 2 Replies
View Related
Aug 27, 2014
it also prompts twice when i try printing or try to save the current page as a pdf and also when i switch record? i think its a problem with the subform as it always is trying to fetch data. would there be a way to make it so that the subform and the form are linked so that the parameter query prompts once and the information stays there rather then having to prompt again when printing or saving as pdf?Two more things - on my total value box i wish to be able to always show 2 decimal places
The code already in place is :
Net : =Sum([TotalValue])
VAT: =Sum([TotalValue])*0.2
Gross: =[Text8]+[Text10]
(Net is Text8 and VAT is Text10)
i have already specified that the decimal place value is 2 instead of auto but it still doesnt work - if the number is a whole number i still wish to see .00 at the end of the field.One last thing - Would their be a way to carry over the information from my delivery note to my invoice note? which would save a lot of time retyping the parameter query.
View 6 Replies
View Related
Mar 29, 2015
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code:
SELECT tblPeople.name, tblPeople.num
FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name)
INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
View 8 Replies
View Related
Oct 18, 2013
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 Replies
View Related
Feb 26, 2014
On the form I need to give the user the option to select a 'from date' and 'to date' and for this I have put in 2 calender controls which have the same control source(same table column). The problem I am facing is when I select a date from one calendar, the other calendar control automatically populates with the same date and vice versa.
I need to have a functionality where 2 different date can be selected and then when the Search button is clicked, then data within the date ranges should be displayed.
what changes I need to make to acomplish this ? find a screenshot of the form attached.
View 5 Replies
View Related
Dec 10, 2014
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
View 4 Replies
View Related
May 5, 2014
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1"
Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
View 1 Replies
View Related
Jun 13, 2013
I am trying to create a form with multiple combo boxes where users can select fields from my main database and click a button to generate a report based on their selections. I think I am supposed to create a generic report with perhaps 4 fields where I would link the four combo boxes to. The issue is I cant seem to get the selection of the combo box to change the field that the report should pull from the database. I think I am supposed to use dlookup in the reports control source for each field, but I cant seem to get it to work.
View 14 Replies
View Related
Dec 30, 2014
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
View 3 Replies
View Related
May 8, 2014
I am new to access, after learning basics I am trying to build my first DB.
Having some troubles with the IIF syntax. I am placing the formula
=IIf([Total]<=(200),[Total]*(0.7)) & IIf([Total]>(201)&[Total]<=(500),[Total]-(200)*(1)+(140)) & IIf([Total]>(501),[Total]-(500)*(1.2)+(300)+(140))
In the form data control source of a field. The first 2 statement seems to work, but the last statement results in some crazzy figures.
View 3 Replies
View Related
Nov 25, 2013
I'm preparing a query as the control source for an unbound listbox. The following code gives the desired results:
Code:
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK
(My.control will be a control on the form. For the time being, I let the query prompt me for a value.)
It produces two columns like so:
CameraNum Camera ID
1 2
2 3
3 4
4 5
5 6
6 7
8 9
11 12
CameraNum is text; CameraID is numeric.
Now, I'm trying to use a trick I read about that should add a single textual entry to the top of the list like so:
Code:
SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]))
ORDER BY QrySbfShotList.CamerasFK
union
SELECT "(ALL)", "Dummy"
FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID
WHERE (((QrySbfShotList.shootsFK)=[my].[control]));
This produces
CameraNum Camera ID
ALL Dummy
1 2
11 12
2 3
3 4
4 5
5 6
6 7
8 9
The second (numeric) column is now out of order. This is reproducible for other values of my.control. If there are double digit entries they get inserted at the third row.
Why? What am I not understanding about how UNION works?
(BTW, I know I could put the "ALL" entry into tblCameras, thereby avoiding the need for a union, but I'd still like to know why the unexpected result.)
View 6 Replies
View Related
Jul 18, 2013
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
View 4 Replies
View Related
Jun 2, 2013
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
View 3 Replies
View Related
Sep 19, 2005
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
View 14 Replies
View Related
Jan 4, 2013
I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.
Here is a bit of background. The source Table is formatted in a way similar to this:
Code--Account#--Expense Description--Jan--Feb...
When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?
Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.
View 14 Replies
View Related
Sep 21, 2004
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
Thanks,
JGM
onesweetdude@hotmail.com
View 1 Replies
View Related
Jun 29, 2005
I this is a simplifed version a saved query called "qryTest"
PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');
UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');
how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?
like
Me.ListNew.RowSource = "qryTest " & gUserID &";"
or
Select * from qryTest , 31 - where 31 is the value fed to the parameter
??? does anyone know? I know it can be called in APS like this:
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.qryTest 31
.....
I can't believe I can't do it from the query string!
Heeeellpppp!
View 3 Replies
View Related
May 8, 2013
On a pop up form I have a list box. The row source for the list box is
Code:
SELECT tblWebOrder.WebOrderId AS [Web Order No], tblWebOrder.CustomerWebOrderNumber, Format([WebOrderDate],"dd/mm/yyyy") AS [Order Date], tblCustomer.CustomerName AS [Customer Name], tblArea.Area FROM (tblCustomer LEFT JOIN tblArea ON tblCustomer.[PhysicalAreaId] = tblArea.[AreaId]) INNER JOIN tblWebOrder ON tblCustomer.CustomerID = tblWebOrder.CustomerId WHERE (((tblWebOrder.Processed) Like [Forms]![frmSelectWebOrderToOpen]![txtOrderStatus]) AND ((tblWebOrder.WebOrderDate) Between [Forms]![frmSelectWebOrderToOpen]![dteStartDate] And [Forms]![frmSelectWebOrderToOpen]![dteEndDate]+1)) ORDER BY tblWebOrder.WebOrderId DESC;
I have the following "On Click"event on the list box
Code:
Private Sub lstWebOrder_Click()
lngWebOrderId = Me.lstWebOrder.Column(0)
DoCmd.Echo False
DoCmd.Close
DoCmd.Echo True
End Sub
When I click on some of the records on the list box the form closes. On some of the records I get a parameter prompt to enter:
Forms!frmSelectWebOrderToOpen!txtOrderStatus
Forms!frmSelectWebOrderToOpen!dteStartDate
Forms!frmSelectWebOrderToOpen!dteEndDate
I inserted the echo commands to prevent this, but this has not worked!
Why am I getting the prompts? How do I prevent them?
View 4 Replies
View Related
Sep 24, 2013
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
View 1 Replies
View Related
May 28, 2015
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
View 2 Replies
View Related
Jun 19, 2015
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
View 11 Replies
View Related
