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?

View Replies


ADVERTISEMENT

Reports :: Record Source Of REPORT Other Than Query

Aug 27, 2013

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.

View 2 Replies View Related

Reports :: Record Source From Report Based On Nested Query?

Jul 24, 2015

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."

View 3 Replies View Related

Display Field From A Query On A Form Not In The Forms Record Source

Jul 27, 2005

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.

View 6 Replies View Related

Reports :: TempVars In Reports Record Source?

Mar 12, 2015

Access 2007 Sub-Report "rptSubEmployeeProject" inside report "rptProgressReportDay".

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?

View 9 Replies View Related

Reports :: Text Box Control Source With Conditions

Dec 24, 2013

I built an expression for the control source in Access 2007 report as follows:

=Sum([Weighted Value])/(Sum([CourseInfo.Credit])-Sum(IIf([StudentClassRecord].[Grade]="P",[CourseInfo].[Credit],0)))

This is to calculate the GPA. However, sometimes the denominator can become 0 and an error us returned. I would like to get "NA" in return instead.

All the field names are derived from table or query. I tried to aggregate the IIF function but ran into error message.

View 3 Replies View Related

Reports :: Passing Variable To Report Record Source?

May 7, 2013

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'.

View 6 Replies View Related

Reports :: Set Record Source Of Report From Button On Form

Jun 13, 2014

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)

DoCmd.OpenReport "SellRPT", acViewReport, , , acNormal

View 2 Replies View Related

Populating A Text Field From A Combo Box Selection.

Apr 28, 2005

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]

What am I doing wrong?

Thx
Kev.

View 5 Replies View Related

Populating Multi-select Items To A Text Field

Nov 27, 2005

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?

delamerbleu

View 2 Replies View Related

Queries :: DLookup In Reports Control Source For Each Field

Jun 13, 2013

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.

View 14 Replies View Related

Control Source And Populating Table???

Mar 25, 2008

Hi Guys

Im really confused on what to do

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:

=[Forms]![frm_Transaction]![frm_ProductReceipt].[Form]![Cost]

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?

View 2 Replies View Related

Reports :: Training Database - Group Report By Field List Row Source

Oct 30, 2014

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:

------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling

etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.

View 2 Replies View Related

Forms :: Can A Query Be Used As Control Source For Text Box On A Form

Jun 7, 2013

Can a query be used as a control source for a text box on a form?

=[QueryLibrary]![Kit] is the control source do I need to change anything else?

View 3 Replies View Related

Tables :: Budget Database - Autonumber Field Is Not In Record Source Dropdown

Sep 20, 2012

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.

View 5 Replies View Related

Reports :: Splitting Single Field Into Multiple Text Boxes Or Multiline Text Box

Jun 4, 2013

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.

View 1 Replies View Related

Forms :: Select Query Error In Control Source Of A Text Box

Jul 16, 2015

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"));

View 7 Replies View Related

Forms :: Adding Field To A Form Breaks Entire Database Record Source?

Sep 26, 2014

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?

View 4 Replies View Related

Remove Record Source/Control Source

Sep 19, 2005

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'm trying to do a

Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""


but this does not seem to work in actualy removeing the record source and control source.

View 14 Replies View Related

Populating Field With Query Result

Oct 17, 2006

I have got 2 tables:

TAT table with fields: ID, SPB, SET, DAYSIN, HRSIN
SETS table with fields: ID, SPB, SET, DAYSIN, HRSIN, plus a few other fields.

Is it possible to do the following:

If (SPB.TAT = SPB.SETS) AND (SET.TAT = SET.SETS)
then store
(DAYSIN.TAT in DAYSIN.SETS) AND (HRSIN.TAT in HRSIN.SETS)

Thanks for any help with this.

View 4 Replies View Related

Dynamic Query And Record Source In A Form

Feb 23, 2005

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.

Thanks in Advance.

View 1 Replies View Related

Forms :: Using A Query As Record Source Of Subform

Apr 4, 2013

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?

View 9 Replies View Related

Forms :: Record Source Query Is Empty?

Oct 18, 2014

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.

Here is the SQL:

SELECT [Quote_ID] & "-" & [Version] AS QuoteIDv, tblQuoteID.Quote_Type,
tblQuoteID.Quoted_By, tblQuoteID.Agency, DLookUp("[Contact]",
"tblContacts","[QuoteID] = '" & [Quote_ID] & "' And [Version] = '" & [Version] & "'") AS contact,
tblQuoteID.Quote_Date, tblQuoteID.FileName, tblQuoteID.FileLocation, tblQuoteID.Amount,
tblQuoteID.Active, tblQuoteID.Quote_ID, tblQuoteID.Version, tblQuoteID.Selected

[Code] .....

View 2 Replies View Related

Forms :: Populating A List Box With Field From Query

May 20, 2015

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.

View 14 Replies View Related

Reference A Fields Values In The Record Source Query

Dec 30, 2007

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.

Thanks.

View 6 Replies View Related

Forms :: Changing Record Source Of Query Profile

Nov 4, 2013

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved