Displaying query results in a form


Hi ,

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.

Posted On: Monday 22nd of October 2012 05:10:46 AM Total Views:  197
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




After displaying a window...

all you experts and thanks in advance for your help. I want to issue a message box after a window (a form) is displayed. I have attached it (in VBA code) to the onOpen event but the message displays before the window is displayed. So I tried, OnCurrent, then onLoad, but in each case the event triggers the message box before the window is displayed. Is there an event which triggers AFTER the display of the window. Note that I don't want the message box displayed several times so onGotFocus is out. Many thanks in advance, Phillip
VIEWS ON THIS POST

72

Posted on:

Thursday 18th October 2012
View Replies!

Requery subform synchronized list box

I have tried to find the solution to this problem on multiple forums but have not been successful. Here is what I am trying to do: Mainform: Test1 Subform on main form: Test1subform The forms are linked through ID (ID text box on both Test1 and Test1subform). I have a list box (dateofbirth) on Test1subform which has a query for the rowsource which returns the Date of Birth for the ID (SELECT DOB FROM Test1 WHERE Test1.ID=Forms!Test1!Test1subform.form.ID). This works when I initially open the form but does not update when new IDs are selected on the mainform even though the ID on the subform updates to the new ID. I figured I simply needed to run a requery when the ID text box on the subform was updated so I created an After Update event for the ID text box on Test1subform. When I now open the form I get the message Enter Parameter Value: Forms!Test1!Test1subform.form.ID. I am not sure what I am doing wrong but any help would be greatly appreciated.
VIEWS ON THIS POST

141

Posted on:

Monday 22nd October 2012
View Replies!

SQL Update query asking for parameter value

I have the following code which is supposed to update the User_Modified field with the system user upon the before update event of the form. I have similar code updating fields with Dates and times (which is working perfectly) but when it comes to this field it asks me to "Enter Parameter value" then whatever value I type will end up in the User_Modified field..... Not sure why it is doing this because the syntax is the almost identical.... Code: strUM = "UPDATE tblJTA SET tblJTA.User_Modified =" & Environ("UserName") & " WHERE tblJTA.JTA_ID =" & Me.JTA_ID & ";" DoCmd.RunSQL (strUM) This code works perfectly tho and is ran just before the Username Code.... Code: strDM = "UPDATE tblJTA SET tblJTA.Date_Modified =#" & Date & "# WHERE tblJTA.JTA_ID =" & Me.JTA_ID & ";" DoCmd.RunSQL (strDM)
VIEWS ON THIS POST

119

Posted on:

Monday 22nd October 2012
View Replies!

Disable a form behind an open query!!!!

I have a form which has a button on it. The button simply opens a query. How can I make the form become disabled or locked so that when the query is open in front of the form the user cannot click back onto the form (which is behind the query) Thus the only way for the user to return to the form is to close the query first! -
VIEWS ON THIS POST

169

Posted on:

Monday 22nd October 2012
View Replies!

Use a form for query critera (yes/no boxes)

I have a query with teacher contact info: name/address -- plus about 7 different yes/no fields (classes taught - each field is a class, checked yes or no). I want the user to be able to "check" which class and view the teachers teaching that class. Trying to get it to work in query form, but ultimately - a button from the main interface will take them to the critera form and produce the report, based on what they select. Is this the best way to do this -- and HOW I've read about QBF with text boxes -- but what about yes/no I always appreciate the help I get here - thanks in advance! -Heather ps -- I can't even get the [enter parameter box] to work for yes/no fields -- how does that work -- I guess you have to enter 0/1 or 0/-1 for yes no. That doesn't make sense for an end user. I need it easy-peasy.
VIEWS ON THIS POST

105

Posted on:

Monday 22nd October 2012
View Replies!

User is forced to first record upon requery after deleting a record

I have the following code in a form which will delete a record from a table; however, upon the requery it takes the user to the first record of the form. How can I capture and move the user to the next record if one exists (or last record if one doesn't) instead of making them move to the first record Code: DoCmd.RunSQL "DELETE FROM [mytable] WHERE [recordID] = " & Me.recordID Me.Requery
VIEWS ON THIS POST

167

Posted on:

Monday 22nd October 2012
View Replies!

Export query to excel

I'm using Access 2000. I've done some searching around and found similar posts online but I'm having a tough time getting my head round this one... I have a query called "QryMonthlySales" with about 10 fields and 400 rows of data. I want to export the query to excel, saving the excel file to a folder location chosen by the user via a form. Before exporting the query to excel, I want to give the user the option to specify the time period. I have a form which allows the user to select a start and end date. Before exporting the query, I will need to select the data via a where clause, something like Period >= txtStartDate and Period
VIEWS ON THIS POST

200

Posted on:

Monday 22nd October 2012
View Replies!

Want query with running count based on group

Here's my scenario. A table with applications, a table with developers. One-To-Many relationship. One application could have 1 or 20 developers (no limit, twenty is the current max). Client requires a report with all developers in their own column. My first thought was to use Access's cross-tab, but it creates too many columns and errors out. Next thought was to create a query that listed the application, developer, and a count. Then to create a query with a case statement and 30 columns across (since 20 is the current max after 6 years of running think I'm "fairly safe" with increasing it by 50%). so something like this: App Developer Position 1 bob 1 1 Sue 2 1 Rick 3 2 Rick 1 3 Frank 1 3 Maria 2 ... and so on Then use a bunch of case statements based on the position. I can't find a way to do the running count based on app. So I'm looking for suggestions on how to do this or a better way to write this miserable query.
VIEWS ON THIS POST

172

Posted on:

Monday 22nd October 2012
View Replies!

Showing query results on a report more than once

I want to make a report that takes information from a query. It will display this information, then in the middle it will be a check that some of the information is printed on, and then on the last part of the report it will display all the information it did at the top. I can get the top part to work and the check, but I can't get the bottom part to work, it will only display the last record. If anyone had any advice on how to do this I would apprecate it: Example: Query Result 1 Query Result 2 Query Result 3 ------- Infomation ------- Query Result 1 Query Result 2 Query Result 3 (I can't get the bottom one to work)
VIEWS ON THIS POST

170

Posted on:

Monday 22nd October 2012
View Replies!

How do i create a subform which displays query results?

hi im currently trying to complete an access project involving a music player creation. i've created a form to search through the database for specific songs (e.g by title, artiste etc.). how do i create a subform in the form so that users can type in their search in the form and see the results in the subform just below
VIEWS ON THIS POST

216

Posted on:

Monday 22nd October 2012
View Replies!

Cannot enter data on form created from query

I created a query with 2 tables. Then I used the menu to automatically create a form for the query data. It will not allow me to change any data on the form. Of course, I can change the data directly in the query results, but I need a form for another user.
VIEWS ON THIS POST

210

Posted on:

Monday 22nd October 2012
View Replies!

Filtering recs from query ...

Form layout is: Form Header -- Has detail information, i.e., Name & RoomNumber Detail Section -- 1 Row is defined. Has 3 fields form a query: RoomNumber, Asset#,Desc. plus a command button to mark it as inventoried. The three fields are defined as text with the CONTROL SOURCE pointing to the fields of the query. Section is setup to GROW. Form Footer -- Command button to exit. When I display the form, everyting comes up great. I get all 3 detail records showing in the Detail Section. What I am trying to do is apply a filter so that I only see the records for the room number displayed in the Form Header. I have applied a filter (and turned it on) during FORM LOAD, but it does not seem to work. Since it is giving me all the detail records instead of none, it appears that the filter is not being accepted at all. Or is it in the wrong place Since I need to have a command button displayed on each line, I think I need to stay with the text box process. I have tested successfully using a LIST BOX, but no ability to use the much needed command button. Any suggestions on how to properly apply the filter \t Thank You. Bob
VIEWS ON THIS POST

160

Posted on:

Monday 22nd October 2012
View Replies!

Distinct records from multiple columns in SQL query

I am using ASP with MS Access Database and have the created the following query for my recordset Code: SELECT galleryID, picCat, galleryName, galleryDate, picID, picName FROM tblGallery INNER JOIN tblPics ON tblPics.picCat = tblGallery.galleryID I now wish to display the recordset in a repeating table but want to remove any records returned with the same Code: galleryID I have been searching for two days and although I have found a lot of similar issues which suggest using DISTNCT, GROUP BY etc... I have not yet been able to find a solution that works. I am now running out of time for my deadline so any feedback/help would be highly appreciated Cheers Kaden712
VIEWS ON THIS POST

194

Posted on:

Monday 22nd October 2012
View Replies!

Non-updatable query issue

I've got a small DB that's been developed for materials management duties at an electronics manufacturing business. We're a contract manufacturer, who builds circuit boards of our customer's designs, using their Bill of Materials. My problem is, I need to pull a new piece of data into a form, and that requires aggregation... and I'll explain why below. (the form is built on an updatable query over a star-schema design) That aggregation (I think) is causing the entire query to become non updatable - I need a solution! About the query: It's built on a star schema, the dimension tables are used to populate drop-down selections in the form, and the only table being updated is the central fact/matrix table. The sourcing of the parts for these electronic assemblies throws an interesting twist- Essentially I have three potential part numbers for any given part that goes into these assemblies: BOM PN - the part number specified by the customer. Used PN - sometimes, purchasing has to buy an alternative part that has the same characteristics. Customer PN - Customers sometimes assign their own part number for reference, particularly when they don't require a specific vendor's part. So, my Parts table (a dimension table) contains data on parts tht we've actually purchased: Mfg PN - which ties to Used PN - parts we've actually bought, and are therefore in our inventory system. Manufacturer - The manufacturer of that part number. Description - straight off the customer's BOM. The Form: Right now it's nearly as simple as my Parts table - it's got controls for MfgPN, Manufacturer, and Quantity. To pull in the Customer PN for reference, I have to pull it from the Assembly Details table, and that means I need to basically do a "select distinct", since the corresponding Mfg PN's are in there many times. How can I include the Customer PN, so that it corresponds accurately with the existing data on the inventory management form - without making that query non-updatable
VIEWS ON THIS POST

320

Posted on:

Monday 22nd October 2012
View Replies!

Howto Stop query from asking for value?

I have a query Ive made that does some math with current values. Fields: Month, Delay Min Before, Delay Min After, Delay Min Saved, Percent: Round((([SumOfDelay Min Before]-[SumOfDelay Min After])/[SumOfDelay Min Before])*100,1) The last one is to get a percent saved from Before and After. My issus is when I run this query it will ask for "SumofDelay Min Before" and again for "SumOfDelay Min After". If I just click thru both and the query will output the correct numbers for me. From this query I made a sub from. The trouble is when I open the form for use it asks me every time for both values when the refresh button is hit. Im thinking Ill have to make a 2nd query with the last field to get this to work correctly. Sound right Edit: more info The query has "totals" clicked. Month is Group by, Before/After/Saved are all SUM, Percent is Group by.
VIEWS ON THIS POST

181

Posted on:

Monday 22nd October 2012
View Replies!

Unequal query relationship

Hi i need some help pls! I have a local table (Table A) with some data in and i have a link to a table (Table B) which is populated from sql/odbc. I set the join to include all records from A and only those from B that are equal, when I try to run this i get an error stating: "that it could not be executed as it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that in your sql statement" I have no idea how to do this, can you help pls
VIEWS ON THIS POST

163

Posted on:

Monday 22nd October 2012
View Replies!

Select query access 2000

hello there I have a problem. I have a really simple table here: table1 with two fields A and B. The content of the table is: a1, b11 a1, b12 a1, b13 a2, b21 a2, b22 a2, b23 A is primary key. My question is: I need to build a query which will get these rows a1, b11/nb12b13/n a2, b21/nb22/nb23 from the content of the table1 that I mentioned. Would you please suggest some way to build a select which would get these two rows I could think of a query which would look like something like this: select A, T1.B,"/n",T2.B from table1 as T1, table1 as T2 where T1.A = T2.A but this not only does not solve my problem but it is my closest approach to a possible solution.
VIEWS ON THIS POST

274

Posted on:

Monday 22nd October 2012
View Replies!

How to query for query text?

Hi all. This is probably a pretty easy one, I've just never done it so I'm not sure how to get at the information. I need to get my hands on some code which will query for the text of all my queries. I basically need to search through all my queries (this would include ones behind forms, but if I can just get at the text in the standalone queries, it would be a start) in order to search for a few tables/queries being used in those queries, so I can alter them to a new approach. What I want to do is spit all the query strings out into a text file or something and search through to find out which ones are utilizing the queries/tables in question, so I can start working on changing them. Is there a way to do this with the querydefs collection or with the msysqueries object
VIEWS ON THIS POST

206

Posted on:

Monday 22nd October 2012
View Replies!

Requery Question

I have a list box on a main form. When the list box is updated it should requery within per the after update procedure two subforms [sfrmAgency] and [sfrmEmployment]. The after update procedure is returning an error stating that it "can't find the field 'sfrmAgency'...", however [sfrmEmployment] works okay. The same thing happens on my OnCurrent procedure for the main form. The after update procedure includes: [Forms]![frmPerson]![sfrmEmployment].Requery [Forms]![frmPerson]![sfrmAgency].Requery
VIEWS ON THIS POST

200

Posted on:

Monday 22nd October 2012
View Replies!

can anyone modify this query statement

hi im a first time user of access, can anyone modify this query statement... select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed = '2008/03/01' and io.DBorrowed
VIEWS ON THIS POST

314

Posted on:

Monday 22nd October 2012
View Replies!