f



MS Access - how-to set control source for text box to query

I've got a bound report with a query as the record source.  I'm adding
a total to the footer and have inserted a text box to display it.  I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder.  When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.

=[Qrydetsum_offtotal]![Office_total]

I've tested the query ( Qrydetsum_offtotal ) independently and it runs
and returns a single value each time for the calculated field,
Office_total.  But when the report runs it simply displays #error.
As an alternative I've tried to load the result of the query into a
variable and then reference the variable in the control source of the
text box, but when I do that, nothing seems to happen.

I'm sort of new to VBA, so if anyone has suggestions about how to do
this or a simpler way, I'd appreciate the input.

       Thanks,  Bob

0
rmcintire (2)
6/2/2005 3:42:42 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
963 Views

Similar Articles

[PageSpeed] 25

Bob:

If you are looking up a single value then the DLookup function will probably
work.  There is also a DSum function when you need to calculate a sum from a
table or query different from the record source.  Information on both of
these can be found in Access Help.

-- 
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Bob" <rmcintire@network-spectrum.com> wrote in message
news:1117683762.129887.189060@g44g2000cwa.googlegroups.com...
I've got a bound report with a query as the record source.  I'm adding
a total to the footer and have inserted a text box to display it.  I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder.  When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.

=[Qrydetsum_offtotal]![Office_total]

I've tested the query ( Qrydetsum_offtotal ) independently and it runs
and returns a single value each time for the calculated field,
Office_total.  But when the report runs it simply displays #error.
As an alternative I've tried to load the result of the query into a
variable and then reference the variable in the control source of the
text box, but when I do that, nothing seems to happen.

I'm sort of new to VBA, so if anyone has suggestions about how to do
this or a simpler way, I'd appreciate the input.

       Thanks,  Bob


0
David5466 (95)
6/2/2005 9:54:43 AM
On 1 Jun 2005 20:42:42 -0700, Bob wrote:

> I've got a bound report with a query as the record source.  I'm adding
> a total to the footer and have inserted a text box to display it.  I'm
> attempting to set the control source of the text box to a different sum
> aggregate query using the expression builder.  When I select the query
> and it's single field, single row field it inserts the following in the
> control source box for the text box.
> 
> =[Qrydetsum_offtotal]![Office_total]
> 
> I've tested the query ( Qrydetsum_offtotal ) independently and it runs
> and returns a single value each time for the calculated field,
> Office_total.  But when the report runs it simply displays #error.
> As an alternative I've tried to load the result of the query into a
> variable and then reference the variable in the control source of the
> text box, but when I do that, nothing seems to happen.
> 
> I'm sort of new to VBA, so if anyone has suggestions about how to do
> this or a simpler way, I'd appreciate the input.
> 
>        Thanks,  Bob

As long as the query returns just one value:

=DlookUp("[Office_total]","[Qrydetsum_offtotal]")
-- 
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
0
fgutkind1 (959)
6/2/2005 3:14:58 PM
Reply:

Similar Artilces:

How to set Data source in MS Word as MS Access Databases via DDE(*.mdb;*.mde).
How to set Data source in MS Word as MS Access Databases via DDE(*.mdb;*.mde). Everytime i click the command button to merge the data, it gives me item not found and i know that its a problem with my datasource. when i click ok then it opens the word doucment prompting me which datasource you want to use, when i click DDE then it gives me the merged data. Is there anyway i can set it permanantely in my codes below. Private Sub SetQuery(strQueryName As String, strSQL As String) On Error GoTo ErrorHandler 'set the query from which the merge ' document will pull its i...

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Any MS Access that is NOT MS Access in WinXP?
Hi all, I have a small application for bookkeeping, actually working in Access with small routines in VBA. I want to rewrite it in Java, but I don't want to use Access anymore. I started looking for another database, but I found none. I already know (and used) MySQL and PostgreSQL (waiting 7.4), but as far as I know they are only daemon, and under windows they use a lot of memory and CPU time. It's like installing a Ferrari only to cross the road. Maybe an approach with loading the daemon at the start of the program, and quitting it at the end, but I don't know how ...

Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in fr...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

Should I upgrade from MS Access 97 to MS Access 2002
Hi guys My company has in one of their products used Access 97 and MS JET 3.5 for several years now. Should we move the database to Access 2002 -which we use now in other parts of the company and also upgrade the MS JET to the latest version? And what does MS JET do? Kjell Arne Johansen MS Jet is the database engine that comes with, and is, by default, used by Microsoft Access. If you aren't having any troubles with the Access 97 database, then there isn't any technical reason to change. There may be company political reasons if, say, the IT department only wants to have one ve...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net////...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end ...

access to MS Access password-protected database?
Can anyone direct me towards a code snippet showing how to use Python to insert data into a password-protected MS Access database? My google searches have been uninformative for dbs that are password-protected. Thanks, Ken On Feb 11, 10:43=A0am, Ken McDonald <kmmcdon...@medicine.wisc.edu> wrote: > Can anyone direct me towards a code snippet showing how to use Python =A0 > to insert data into a password-protected MS Access database? My google = =A0 > searches have been uninformative for dbs that are password-protected. > > Thanks, > Ken You post is a little vague...

Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how can i make client access database located on the server? "HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message news:d71ce567-0434-48b8-b1d2-bb191706ab45@w8g2000prd.googlegroups.com... >I m currently developing a java based application and want to know how > can i make client access database located on the server? I think you're going to have to expand a little bit more in your question here, as is not 100% quite clear what you're trying to do. You might want to make a little bit of a d...

MS Access Database Query
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys and search for existing entrys. But the search function that Access generates as I created the proper button is way to simple, I need a function that enables to search in several columns. I saw a few quite simple examples where this was done by DAO, but this doesn't work with a .mdb database, does it? (Access allways replies 'Unknown user datatype' once it gets to the line: 'Dim...

Date picker from MS-Access 2000 to MS-Access 2003
Hello. How do I get a date picker (References: Microsoft Calendar Control 8.0 Location C:\Windows\System 32\mscal.ocx) inset in MS-Access 2000, to work in MS-Access 2003. TIA J�rn Why not scrap it and use the all-API approach Stephen Lebans has at http://www.lebans.com/monthcalendar.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "JHB" <joern_bea@removeThis_tdcadsl.dk> wrote in message news:42190d18$0$48635$edfadb0f@dread15.news.tele.dk... > Hello. > > > > How do I get a date picker (References: Microsoft Cal...

conversion of ms access 97 application to ms access 2000
hi access gurus would appreciate if u can give me pointers regarding conversion of ms access 97 application to ms access 2000, like what are the problems to be expected and how to handle it. also would appreciate if anyone can give pointers on using VSS with ms access 2000, how to handle multiuser issues when it comes to insert, update and delete. thanx in advance, regards bala balkiicbe@yahoo.com (bala) wrote in message news:<b62a781b.0310080544.6d2a08fc@posting.google.com>... > hi access gurus > > would appreciate if u can give me pointers regarding conversion of ms >...

Unbound text box, text>255, MS Access/PostgreSQL
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as Memo fields in Access. The intention was to be able to enter unlimited length commentary into the field. But, although I can enter unlimited text by using...

Redhat Apache to access MS-Access Database through PHP
Hi, I am wondering if anyone knows how to make this work. I have Redhat Liux ES v2.1 Server Apache 1.3 PHP 4.0 a MS-Access MDB file (just the file, already created) The MS-Access file is already in the Redhat Linux server. Is there a way I can make Apache & PHP to access this MS-Access MDB file? Thank you. ...

Running a ms access database with access 2003 in Vista
I have a Database that I am trying to run on a new vista computer. Up to now I was running it on a win 98 computer and using access 2003! I installed access 2003 and I can make it run and view the data but I can not save any new records. Has any one seen this problem yet? Thanks Clarence Wollman On Wed, 22 Aug 2007 13:17:13 -0700, cwoll <clarencewollman@gmail.com> wrote: Do you have the same problem with the Northwind sample application? (search your computer for northwind.mdb) -Tom. >I have a Database that I am trying to run on a new vista computer. Up >to now I was running...

Database connection to MS Access & MS Foxpro
Hi, When we select new project ,can "MFC AppWizard(exe)" make connection to database ? or only "database project" can make connection to database ? How do i make connection to database(MS Access/Foxpro) and perform sql using MFC Appwizard(exe) with ODBC --CDatabase? Any sample code ? Thanks. CBTan wrote: > Hi, > > When we select new project ,can "MFC AppWizard(exe)" make connection > to database ? or only "database project" can make connection to > database ? > > How do i make connection to database(MS Acce...

Accessing MS ACCESS database using Data Environment
HI, I have developped a program in Visual Basic 6.0 using MS Access database. I have created all my reports through Data Environment where I have entered the location of my database file (for e.g c:\project\app\test.mdb) in the Connection properties. I have installed my application on another PC using Package & Deployment wizard. When viewing the reports on the other PC, I have to enter everytime the new location of the database. How can I solve this problem, please? nadia wrote: > HI, > I have developped a program in Visual Basic 6.0 using MS Access database. >...

access MS-Access database in C# Windows Application
Hi, I am trying to access the MS Access database on my local machine in C# Windows Application in Visual Studio.NET. But it seems that it does not work. I use the Microsoft Application Blocks to access the database. I have tried 2 possible connection strings. ----------------------------------------- str_1 = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + DBFilePath; str_2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBFilePath; //SqlHelper is the class in the Microsoft Application Blocks SqlHelper.ExecuteNonQuery( str_1, CommandType.Text, SQL_str); --------...

Best way to access a remote MS ACCESS database
What is the best way, via the internet, to access a remote Microsoft Access database? I was thinking of using VPN, but I thought that I would check here to see if there is a better way. Any suggestions? Thanks Do you mean remotely controlling an Access database, or linking tables over the internet? The first is doable, the second is a recipe for disaster. Remote Control: If your remote OS is Windows XP, you can use Remote Desktop (although only 1 person can access the PC at a time; this will change in the upcoming Service Pack for XP, but that may not help you now). Otherw...

Accessing a MS Access database across two servers
Hi The technical support guys at my company have set up my system so that the server containing the MS Access database is on one server, while the pages that should access the database are on another server. When the pages and database are on the same server, I usually just connect through ODBC. The problem is that I don't know how to set up an ODBC connection to link from one server to a database in another. Anyone know what I am getting at and know of a possible solution, or a link to a site which may help. thanks in advance Brendan "Singularity" <Brendan.Collins@Sin...

Wanted
I am wanting to be able to write reports against an ms-access database that is part of another package. I only want to report from the database. What I need is a package that will allow me to create the reports and then be able to distribute a run-time version. The client may not have Office so the package must run standalone. It could get to the Access database through an ODBC driver. Does anyone know what packages available? It looks like Crystal Reports has taken out their runtime version with their latest software but that was only a footnote in a book that I was reading. Please provide...

query ms access database from the user
At college i have been given this piece of untidy code because it mixes a gui interface with the console, i prefare one or the other, but its not my code, i understand what it does. It opens the database CarDB by using the java odbc (Open Database Connectivity ) driver and searches for the row make with nissan and displays to the screen as text in a gui text box the columns Registration, Model, Year, Price in the same row as the nissan . I would like the programme to ask the user which car their want to search for . Unfortunately i do not learn at college or have taught myself sql. If i am un...

Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat......

Web resources about - MS Access - how-to set control source for text box to query - comp.databases.ms-access

Control - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Control Alexa and your entire smart home from your car with HeadsUp
HeadsUp is a San Francisco startup that is aiming to be the end-all, beat-all when it comes to in-transit control. Founder Arnab Raychaudhuri, ...

Through Google Glass — Mission Control at boj World Headquarters on a snowy Monday morning
YouTube caption: "CEO Gray Cat considers our larger strategy going forward while I do all the little stuff. Works out well for us."

How to control your Mac with an iPhone or iPad
... We’ve all done it. This is why I always have Screens VNC running on all my devices. With the help of a single app, you can access and control ...

Leading Tobacco Control Expert Says Anti-E-Cig Lobby Is Like ‘Abstinence Only’ Movement
Leading Tobacco Control Expert Says Anti-E-Cig Lobby Is Like ‘Abstinence Only’ Movement

Air traffic control privatization takes a step forward
A troubling plan to privatize air traffic control has passed through a House committee , paving the way for a vote in the full House and then ...

One weird trick is cruise control to shorter XCOM 2 load times
XCOM 2 is pretty hitchy and janky, though I've not encountered any game-breaking issues. Still, worse than the lazy virtual camera man too busy ...

Passengers: If Air Traffic Control Is Privatized, Do You Trust The Airlines To Speak For You?
... looming fight over the sudden vacancy on the Supreme Court, a bill is making its way through Congress that would privatize air traffic control. ...

Kanye out of control?
Kanye out of control?

Cybathlon turns Olympic-style spotlight on Iron Man tech: brain control, exoskeletons and more
WASHINGTON, D.C. – Imagine a contest for the kinds of technologies that would set Tony Stark’s nuclear-powered heart all aflutter in the “Iron ...

Resources last updated: 2/16/2016 12:03:33 PM