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 RepliesHi,
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
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.
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
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?
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.
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
Have a query which looks up an address using the text input into a textbox by the user.
What I'm now struggling with is getting the query result into the textbox.
Am still new to access and am hoping this is something fairly simple.
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.
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.
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.
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.
Basically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).
The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.
Hi,
I have a form with a textbox and I want to fill that text box with the calculations from values of other text boxes. However when I put the equation
= Sum (box1 + box2 + box3) into the textbox's control source I don't get any results even though box1, box2 and box3 have values in there.
I also need the result to be saved in a field on my table, but I got no idea how to do it.
Please help!
Thank you
dfuas
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
: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!!
I am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham
First Name: John
Last Name: Twain
First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.
Attached is a graphic of the form.
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.
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:No Resignation Date
A Resignation Date in the future
A Resignation Date in the Past
See attached PDF
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
Code:
If [Resignation Date] = "" Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
[Code] ....
MIS = The name of the textbox in question.
I'm simply trying to display the data from an open form named frmEventInput fields: [EventName] and [Description] on a new form that includes 2 text boxes with the control sources and names: [txtEventName] and [txtDescription]
It says Error 2448, You can't assign a value to this object!
Private Sub Form_Load()
Dim LResponse As Integer
LResponse = MsgBox("Do you wish to open 'Event Participant Registration Form'?", vbYesNo, "Continue")
If LResponse = vbYes Then
DoCmd.GoToRecord , , acNewRec
[Code] ....
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
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
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs
10
10
30
15
10
15
25
===
115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
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.
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?
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