Microsoft Access - Changing the recordsource or dynamically hiding columns of a query
Asked By Jason on 26-Jun-12 01:34 PM
So my problem is that I have a query that has fields based on what month we are in. For instance, since it's June right now I would need 6 fields in this query. When it turns to July, I would want 7 fields to show. I'm not sure how to accomplish this however. I should mention this query will be used in a graph in a report. My ideas so far have to either hide the fields based on what month it is in vba or to change the recordsource of the graph in vba and make 12 queries. Anyone have any other ideas or know how to make the ideas I have become possible?
wally eye replied to Jason on 26-Jun-12 06:34 PM
You could build the query in VBA, it would be a bit slower.
Would a cross-tab query work? Just put your selection criteria in and use the month for column headers.
Jason replied to wally eye on 27-Jun-12 10:09 AM
No unfortunately the dates span the entire year. So a crosstab query would show the full year.
Pat Hartman replied to Jason on 28-Jun-12 08:29 PM
Selection criteria would limit the rows selected so if your data is normalized, the crosstab solution will work. It sounds like you have 12 columns in the same row rather than 12 rows so that means more work as is usual for a non-normalized schema. You either need 12 queries and decide which to run based on the month or you build the SQL string with VBA depending on the month.
Good Morning, I have a database that has 2 in the first subform both these forms use the same table tblServiceInfo. I am using Access 2007. Does anyone have a clue on how to do this? Thank you in advance. Change the query of the RecordSource of the first subform so that its selection criteria refers to th current record in on the same main form. You could then use the Master / Child link fields and Access would take care of syncing the two forms. Also, note that this will not work at the same time. You cannot refer to a form that is not open. keywords: Access 2007, Microsoft Access, database, subform description: Access 2007 Good Morning, I have a database that has 2
I am using DLookup in a subform. I have you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control". Anybody able to help? I am using this in a network environment, but do not have access as administrator, if that makes a difference. Thanks!! Karen If you are looking to display in the combobox but store the key value, it might be better to setup the recordsource in a two column combobox, column 1 with a width of 0, column 2 with 1 would be your key value, column 2 the description. Bound Column = 1. keywords: Control, Microsoft Access, DLookup, Update, description, part number, combo box description: Access and Dlookup I am using
Hi. I'm only an access novice, I'm using the ColumnHistory function in Access 2007: = ColumnHistory([RecordSource], "Comments", "[ID] = " & Nz([ID], 0)) and would like to show in a form and on out the last comment rather than all version history? Thank you in advance. V keywords: Access 2007, date, Microsoft Access, report, Copy description: ColumnHistory Hi. I'm only an access novice, I'm using the
I am trying to pull the highest, lowest and and [end] into the parameters but the second set still pops up. Reports run their RecordSource query twice if you use page x of y since the report needs to calculate will just get the date from the form and not prompt you. keywords: exception, date, Microsoft Access, report, Max, Min description: Access Report I am trying to pull the highest, lowest and average from a report I
I have a report that calculates the precentage of the top of my head here. Another option may be to build your main reports recordsource to include the training totals, but that may slow down your loading time. keywords: Microsoft Access, Percentage, report, training description: Duplicating Calculated Fields in Access Reports I have a report that calculates the precentage of training completed against what is
Hello everyone, I have a simple report in Access that lists an estimate number numerous times (in this example, 5 times). Next to each please expand your request with more details. Note, BETWEEN needs to be applied as per Access Syntax in the Query. Hope this assists. Your form needs two unbound date fields and ways to do this. Method 1. Create a query to use as the report's RecordSource and have it reference the form for its criteria. Select . . . . From. . . Where YourDate Between Forms you would just use two separate OpenReport methods. One for each criteria. keywords: date, Megan Microsoft Access, Microsoft Access, Access Syntax, report, Local, report in Access description: Create a form in Access
Open a form on report open. . DataBase
report open. . DataBase How do I open a form when a report is run. BOb Access Reports Discussions Microsoft Access (1) RecordSource (1) MVP (1) BObYou (1) MailI (1) CAD (1) CAE (1) DAB (1) You can of report and never needed to open a form when a report opens. - - Duane Hookom Microsoft Access MVP The report depends on a query and I am inputing critiria from a form less time will be spent on duplicate answers. See Netiquette at http: / / www.mvps.org / access It's a great site to visit anyway. - - Fred Please respond only to this newsgroup suitable for inclusion in an application. But, as Duane pointed out, you can modify the RecordSource of the Report in its Open event. You will need to make your Form Modal
I have several reports with specific layouts that can different queries. Rather than have the user go into the design view and change the recordsource in the report properties, I would like to be able to prompt the user when to have one report for each distinct layout (fields, grouping, sorting), and switch out the recordsource. I currently have the title display the recordsource, so it changes to the name of the query for easier identification, as well as date references. I am trying not to get too complicated as I only use MS Access to produce reports from imported data, as ARS Remedy has a crappy reporting tool. If to keep it basic for the user. Open database, open a report, be prompted for recordsource from a list, be prompted for query parameters, display and print report. If forms is
Referencing Subform Controls / Properties 2007 DataBase
Access Discussions Tony Tony Toews (1) Microsoft Access (1) Main (1) Form properties (1) Open event (1) Subform (1) If the code you are executing is in the subform you could try the Parent syntax. Tony - - Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http: / / www.granite.ab.ca / accsmstr.htm Tony's Microsoft Access Blog - http: / / msmvps.com / blogs / access / For a convenient utility to keep your users FEs
quick question: I have read that the size limit for an access 2007 database is 2GB- -does this change when the backend is on sharepoint? The reason play with. Thanks James No. It's a factor of the MSAccess database engine. No Access database can be larger than 2gb. Its' not the memory consumption, the 2gb is a database file. A database that can contain 4 million worth rows spread across tables in Access and it consumes only 220 MB. The following list of tables is specific to Microsoft Office Access 2007 databases: GENERAL ATTRIBUTE MAXIMUM Access database (.accdb) file size 2 gigabytes, minus the space