Displaying The Results Of A Dynamic Query
Feb 11, 2007
Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:
DoCmd.OpenQuery <name of the query>
But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).
Any help gratefully appreciated.
Sim Bamford
View Replies
ADVERTISEMENT
Apr 29, 2006
Hi, hope this is a reasonable thing for semi-beginner to intermediate person to do.
Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that).
Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer.
Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning.
Is it possible for a form "Offers" to be based on a query that keeps updating like that? For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime)?
Would you need to have a separate table storing only the most recent offer for each company? If so, how would you get that updated all the time?
Anything you could offer to point me in the right direction would be very much appreciated!!
Thanks,
View 1 Replies
View Related
Jan 22, 2008
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
View 4 Replies
View Related
Sep 14, 2005
Hi,
This is a Query / Forms question...
I have 2 tables setup with a 1 to many relationship, simialr to 1 Customer having 1 or more orders. I created a query to prompt for all orders for a particular customer. I then created a form based on this query - i.e. linked the query to a form.
Now when I open the form I am prompted for the customer id and when I enter this all of the results of the query are displayed in 1 form. However, this is not what I want. I would like the orders for the customer to be displayed within a subform on the actual form. In this way the customer details will static and I can cycle through the customer orders in the subform separately.
Is there any way I can setup my query to display the results in this way? I need the prompt for the customer ID. Or is there any way I can setup the form to display the results in the above way?
Any help would be greatly appreciated.
Thanks
View 2 Replies
View Related
Jun 17, 2006
I have created a Query to search for clients in my database, the query and the search all work fine, however it would be nice to have the query post the results into a form I have created to display clients data rather than posting the results in a tables view.
Is there anyway todo this?
Thanks in advance.
View 1 Replies
View Related
Sep 13, 2006
Forgive me for what may sound like a dum question -- Access is completely new to me.
I have my table. I have this displayed as a form.
At the bottom of the form, I have a text box and a command button.
The user enters in a key value. The command button triggers a macro that runs a query.
Now, I'd like the form to display the data returned from the query. How?
I tried creating a new form with its Record Source set to the query. I had the macro close the original form, run the query and open the new form. But before the new form could be opened, Access prompted me for a 'parameter value' (I assume so that the query could be run again).
What am I doing wrong?
View 3 Replies
View Related
Dec 15, 2006
I searched the archives and Access Help and couldn't quite find the answer to this question, which is no doubt simple.
I have a form showing Projects with an embedded subform showing all the Buildings associated with a given project. In the form footer section of the subform, I would like to display summary information about the buildings in the project being displayed, such as count of buildings, count of residential buildings, count of commercial buildings, and so on, and I would like these summary values to update as users add or change building information.
I know how to write the SQL query to pull this information, but do I use a text box or some other vehicle to do this? Using a text box as a calculated control, I was able to produce the count of buildings using:
= COUNT([UnitID])
To get the count of residential buildings, I need to be able to specify "WHERE bldgtypeID = 'R'"...I can't get the text box to display results of an SQL statement. Can text boxes handle SQL statements?
I really appreciate any suggestions you have.
View 10 Replies
View Related
Feb 26, 2008
Hi everyone,
I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed.
Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #)
I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form.
ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
Thanks for all the help
View 1 Replies
View Related
May 8, 2013
I currently have a search form that when criteria is entered it then opens up a query with matching results. What i would like to have it do is have a macro that runs the query so that it gathers the results and then displays the results into a form with a textbox called search results.
View 2 Replies
View Related
May 9, 2013
So i have a combo box that has a criteria set for the query. I have a search button that runs the query that users click.
What i need to happened is i want the results displayed in the sub form the when query is ran.
View 7 Replies
View Related
Aug 7, 2014
I have a Query, which is running on 2 tables and when I run the query I am getting the results like this.
Name----------Position
Rock----------1
Rock----------2
I want the Results like
Name----------Position----------Position
Rock----------1 ---------- 2
Is there any way to do this.
View 1 Replies
View Related
Jun 24, 2014
I have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.
View 14 Replies
View Related
Aug 7, 2015
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
View 8 Replies
View Related
Dec 13, 2006
Hi All
Is it possible to display two result in one field.
In essence i have a table of tasks with a due date.
I would like to display a count in red of overdue tasks in reference to the current date and a count in green of the tasks which are after the current date. The date is based on month year.
I think I may have to have two seperate fields and make them look like one by having no outlining but if its possible i would rather do it in one field.
My knowledge of VB etc is very limited but I can follow code and sometimes adapt it successfully.
Any help much appreciated.
ChrisD:D
View 1 Replies
View Related
Dec 15, 2006
:confused: I am setting up a referral system with a datebase on access. I want to have a form where I can enter a date of referral and the results are displayed in a form style? How do I do it?
I have set up a form to enter the date, with a submit button, and this return the results in a dataview/spreadsheet style. But I want to display in a form so that I can edit later.
I am a novice in Access - Any help!!
View 1 Replies
View Related
Sep 19, 2013
I have created a table with name "Main". It consists of fields ID, Author, Title, Abstract, Page and Published.
I have created a form named "MFORM" with text boxes Author, Title, Abstract, Page and Published,and then I created a search and clear parameters buttons below the text box.
Then I dragged my Search Query into the bottom of my form MFORM and named it as "All Results".
When I click on search then the results are displayed in the new tab instead of bottom of my main form in Search query.
If I enter author name as Mike and then click on search button then results should display all the details related to Mike on the bottom of my form.
All Results [At the Bottom of my page]
Should display all the details related to Mike.
I had attached my database. I just need the search results at the bottom of my main form in stead of displaying in new tab.
View 11 Replies
View Related
Feb 23, 2006
Hi, I'm just wondering if it's possible to display the result of a query on a subform which does not have a relationship with any other table.
What I'm doing is creating a search form where a query is built from the users input (through combo boxes and text boxes) of what table, attribute and condition they want to search from. When they press the search button, I want the result to be displayed in a datasheet below the selection criteria
The first thing I thought of using to display information with was a subform, but I think the purpose of having the subform is so that it can be linked with a form. So, is there another control or way of displaying results. The results can come from any table.
Thanks.
sugoi_kat
View 1 Replies
View Related
Oct 7, 2014
I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:
One Lender to Many Relationship IDs
One Relationship ID to Many Tax IDs
One Tax ID to Many DocumentsRequired
My forms are nested as follows:
Relationship ID form (contains info for Relationship ID and Lender)
-> Tax ID
-> DocumentsRequired
I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.
Dim strtext As String
Dim strsearch As String
strtext = Me.SearchBoxTxt.Value
strsearch = "SELECT [Relationship ID] " & _
"FROM CustomerNormQuery " & _
"WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _
"OR [EIN/SSN] Like ""*" & strtext & "*"" " & _
"GROUP BY [Relationship ID]"
Me.RecordSource = strsearch
View 4 Replies
View Related
Dec 8, 2013
I'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.
As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.
View 9 Replies
View Related
Sep 24, 2013
I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.
My Requirement :
The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.
But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.
For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?
View 6 Replies
View Related
Apr 17, 2007
Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?
Thank you
Dan
View 7 Replies
View Related
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Jun 30, 2006
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies
View Related
Feb 7, 2007
I have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.
This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.
I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?
SELECT [(Table) Denton Routing].LOCATION_ID
, [(Table) Location].NAME
, [(Table) Location].CITY
, [(Table) Location].STATE
, [(Table) Location].REGION
, [(Table) Denton Routing].UNIQUE_LANE_ID
, [(Table) Denton Routing].CARRIER_ID
, [(Table) Denton Routing].[SHIP DAY]
, [(Table) Denton Routing].[DELIVERY DAY]
, [(Table) Denton Routing].[TIME AT LOCATION]
, [(Table) Denton Routing].STOP_NUM
, [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID
FROM [(Table) Denton Routing]
Where [(Table) Denton Routing].Location_ID = "13176AA"))
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY"
AND [(Table) Denton Routing].Final_Dest = "DENTON"));
Here's the code:
Private Sub cmdRunQuery_Click()
Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set Db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0
where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"
MsgBox (where)
Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
View 5 Replies
View Related
Dec 16, 2005
As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?
View 6 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
