Problem with VBA and assignment of a value

I have a form w/subform and need to display the average of one field 
(AverageAge) and the count of records in the subform on the form somewhere 
else than in the subform.  I calculate AverageAge as Average([Age] in the 
form footer.  I get the record count using the recordCount property o the 
subform.  

The problem is that I can't sem to get the value 'AverageAge' into an 
unbound textbox.

Me.AvgAge = Me.Team_Members_subform.Form.AverageAge   produces the error
Application-Defined or Object-Defined error.

Me.AvgAge = Me.Team_Members_subform.Form.Age  gives me the correct age for 
the first record in the subform which is what I expected to get.

Me.AvgAge = Me.Team_Members_subform.Form.[AverageAge]  gives the error
Microsoft access can't find the field '|' referred to in your expression.

Me.AvgAge = Me.Team_Members_subform.Form!AverageAge   AND
Me.AvgAge = Me.Team_Members_subform.Form![AverageAge]  both 
give a blank or null in the field 'AvgAge'.  If I type in a number they 
delete it so I know something is happening but I am not getting the value I 
expect.

HELP   
-- 
Steve S
0
Utf
3/18/2008 8:02:00 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1082 Views

Similar Articles

[PageSpeed] 25

As a followup to the first post I can get the value of AverageAge from the 
footer of the subform by using the following in the ControlSource property of 
the testbox.

=[Team Members subform].[Form]![AverageAge]

this has worked for sometime but nowI need to control when the field gets 
updated so I need to get the VBA code to work

thanks for any and all help.





0
Utf
3/18/2008 8:16:01 PM
Steve,

Is the control where you are storing the average age actually named 
'AverageAge'? If not you should use the actual name of the control.

Me.Team_Members_subform.Form![name of control here]

HTH,

Chris

"Steve S" wrote:

> As a followup to the first post I can get the value of AverageAge from the 
> footer of the subform by using the following in the ControlSource property of 
> the testbox.
> 
> =[Team Members subform].[Form]![AverageAge]
> 
> this has worked for sometime but nowI need to control when the field gets 
> updated so I need to get the VBA code to work
> 
> thanks for any and all help.
> 
> 
> 
> 
> 
0
Utf
3/19/2008 12:15:01 PM
Reply:

Similar Artilces:

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Problem with message routing between EXCH2K and EX2K3
We are running a ver simply Exchange topology, with an Exchange 2000 single server (ServerA) acting as "dumb" Information Store. Incoming and outbound SMTP email is relayed through a Linux based SMTP host relay on the same subnet, which handles all domain lookups for mail routing. ServerA is configured to use to the Linux SMTP server as it's smart host. The domain is a native Windows 2003 AD topology. My problem is the following: I have recently added a new Exchange 2003 Server (ServerB) into our Exchange site topology. If I create a user (UserA) on ServerA and then do a...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location. The code looks like this: Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut Range("D5").Insert(xlShiftToRight) However, when it reaches this point, I get an error saying "Cut method of Range class failed". I've tried changing the destination range so that it reads similar to the origin range, but that didn't help. Can someone help me get the syntax on this line correct? I've got several similar lines further down in the code. If I can fix thi...

Visio Printing Problem
I am trying to print a flowchart. The print view displays the complet flowchart. However, when I print the flowchart, the top half of th flowchart is omitted and the bottom half prints. Any problem solvin recommendations would be appreciated -- jqsandma ----------------------------------------------------------------------- jqsandman's Profile: http://www.officehelp.in/member.php?userid=661 View this thread: http://www.officehelp.in/showthread.php?t=136719 Posted from - http://www.officehelp.i this usually ends up as being "apply available software patches to visio" or &quo...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

Attachment problems
Hi, Is there anyway to unlock outlook 2002's block on ".exe" files. I know these are Level1 blocks, but can't find a way to configure it. I have recieved an important program and need to run it. Thanks in advance Scott Try here: Opening .exe Attachments with the Outlook E-mail Security Update and Outlook 2002 http://www.slipstick.com/outlook/esecup/getexe.htm -- Nikki Peterson [MVP - Outlook] "Scott White" <ddltrade@hotmail.com> wrote in message news:OmVN4JVnDHA.1672@TK2MSFTNGP09.phx.gbl... Hi, Is there anyway to unlock outlook 2002's block on...

A Strange Printing Problem
Our accounting department maintains an Excel file, updated in an onging process, converted to a PDF at month close, and distributed to our senior management. There are nine worksheets, seven behave normally, but two worksheets default to printing 2 copies. No macros, viruses, etc. Any suggestions on a fix will be appreciated. Paul I would try a different PDF converter. There are a # of free ones out there. It would be a simple and quick test to see if that was the problem. "PA" wrote: > Our accounting department maintains an Excel file, updated in an onging > process,...

Ipaq
A user where I work has had problems with his Ipaq serial connection 'going to sleep' over the last couple of weeks. Here are some further details: It's an Ipaq 3890 using ActiveSync 3.7 on a Windows NT4 workstation running Office 97. He synchronises mail of approx 1.5g , which includes Office attachments. The PDA has been working fine for over a year on ActiveSync 3.5, and there has been no software upgrades or changes on the users PC. There are no error reports in any way, and nothing is shown in the event viewer. I have already tried the following: Reinstalled ActiveSync 3.5 R...

MFC #define Problem
We've been using Visual C++ for at least two versions (VC++ 6.0, VC++ 7.0 .NET), but now we've upgraded to .NET 7.1.3088 and found that a macro define in shlobj.h includes the symbol NUM_POINTS. This generic name conflicts with a const declaration within the scope of a source file. Has anybody encountered this error? ...