Reports :: Populating Text Box With Field From Query That Is Not Record Source
Aug 3, 2015
I have a report based on a query. I want to populate 6 Text Boxes with Dates from fields in another query. The date fields I want to add will be headings for columns that represent weeks (they change all the time so can�t be hard figures). The two queries are not really related by any common field. I am not able to get this working because the fields I want are not part of the query that is the Record Source for the Report.
Is there any way that I can do this? Can I change the record source of just the text boxes?
Everytime i make a report in Access, first thing i do is build a query and then use it as a record source. I try the other way, I go to create report design directly and do the drag and drop of fields.
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database ' Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset) ' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source: [qry_op500_entry_delta]![countofphone model] I get an error stating that this is not in my field list for the form. If the answer is No or if needed I can give more information on what I am trying to do.
I need to dynamically change the table in the sub-report's record source. I tried (line wrapped in code tags below for reading purposes)
Code: SELECT tblProjectHistory_fldProjectID, FirstOfHistory, [History Date], [Time Spent], Employee, fldAssigned, TheFieldPriority, fldTitle, employeeID, fldTimeSpent, fldStatus, fldHistoryID, fldOrder FROM " & [TempVars]![TempEmpTempTable] & " ORDER BY fldOrder;
And I get the error of invalid bracketing of name and it refers to the [TempVars]![Temp part. Makes me believe that I cannot use TempVars in a Reports RecordSource, is that accurate? If So that leaves me trying to set a sub-reports recordsource via vba right?
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct - SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click() If Right(strsql, 1) <> "'" Then 'check if statement was built Else strsql = strsql & ";" 'add trailing ; to statement MsgBox strsql DoCmd.OpenReport "tblincidents", acViewNormal, , strsql End If End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
How can I set the record source of a report to a saved query through VBA. I am trying to use the same report for a number of uses, all of the info on the report is the same, but the only difference is the query that the information is based on. I have this simple code below, how do I add a record source to it (if it can be done)
I have a combo where the list is populated with a bit of VB code to create a two field list. When the user selects the item they want from the list it displays the item in the combo box and should populate a text box in the same row with the value from the second column.
This combo is part of an orders subform and the user is selecting a product from the list which displays the product_name and the product_id.
The problem is that the product_id is not filled in and access returns the following error: Run-time error ‘3101’: The Microsoft Jet database cannot find a record in the table ‘product_table’ with key matching field(s) product_id.
This is the VB code that populates the list : [code] Private Sub comboCatagory_ID_AfterUpdate() Dim sProd_description As String sProd_description = "SELECT products_table.product_id, products_table.prod_description " & _ "FROM products_table " & _ "WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0) Me.comboProd_description.RowSource = sProd_description Me.comboProd_description.Requery End Sub [end code]
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
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.
Basically I have a form, and inside that form there is the "Total Cost" field which is a field in the "tbl_Transactions" table; to display some values from a subform inside of the "Total Cost" field i changed the control source of the "Total Cost" text box to the fowllowing expression:
But obviously because of the fact that the control source has been changed these values brought up by the expression are not put into the table so the "Total Cost" column in the transactions table (tbl_Transactions) is just blank.
Is there any way I can make it so that the values drawn up by the expression will also be entered into the " Total Cost" field in the transactions table as well as being displayed on the form?
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now i want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record source dropdown.
I have a single field in a table called "Client Contact", where users enter a semicolon between the name, address, and city state & zip. My reason for this was so we could copy client info with a single copy and paste (like from an email). But, on the final report, it needs to have these three parts split up into different lines, or even different textboxes. I can't find a way to do that.
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table approvalNosys = Field in the table status = Field in the table score = Field in the table cmrOverview = Form [approvalNoSys] = field in form 1 is the criteria
Code: SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore FROM R_P_Data_P GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
I am building a database where one Form displays records from 14 different tables. For some reason, when I recently try to add a field on to a form from a new table, the ENTIRE form loses the record source, and every single field that is already on the form gets that green dot in the corner with errors surrounding a record source that cannot be found. What am I doing wrong? Am I exceeding some limitations with forms?
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 have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
I want to use a query as the record source of a subform. I want to be able to actually write the query out, not refer to a query object, because I want to change the query on the fly using VB.
This subform displays records when I have it opened by itself, but will not return records when it's displayed as a subform. Does writing the query out not work for subforms?
I have a main form with two unbound text boxes that filter a subform with a select query in the record source. The problem is that it only works sometimes.when I copy and paste the SQL into a query instead of the form it works all the time. for example when I open the query and the form side by side with the same SQL the form does not show results but the Query does, and yes I have "requeried" both of them after entering criteria into the unbound text boxes.
I have a Pupils database where I have a form with Pupil name and ID.I have a query with a calculated field and the ID and this works great.
I can display the associated calc on the form using a sub-form (I link the ID in the calc to the ID on the form) but I dont know how to get this value to display in a listbox.
I have a query on a form that is providing all of the information I need for everything on my form.
The problem is I don't know how to refer to a field's value in the query in VBA without having a hidden text field on the form.
I know I can reference a combo box's query to include criteria such as [Forms]![cboSelectStudent] if the control is actually on the form, but how would I refer to a value in the form's query that doesn't have a control on the form.
I've been searching for everything I can think to call this but haven't been able to come up with anything.
My form has a Record Source of qry_Profile, it is a query that shows the user the records of the dogs that they currently own. I want to put a button in the form footer to allow the user to show the records of all the dogs that they have ever owned.
So my question is how do I change the record source with vb.
