 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2005"Time and Billing" database question -- access
Hi, I downloaded the “Time and Billing” database for access from the “templates” section of your website yesterday. The link is: http://office.microsoft.com/en-us/templates/TC010184611033.aspx. I have office 2003 and Windows XP. Everything works fine until I click on “Projects.” When I click on “Projects,” instead of seeing numbers in the fields for “Total Hours,” “Hourly Billings” and “Total Expenses;” the word “#Name?” appears in each field. Also, the word “#Error” appears in the “Total Billing” and “Amount Due” fields. I know the program is adding up all of my hours because the reports print correctly. Any idea how I can fix the error messages? Please, please, please help! Best, Susanna this is not Microsoft Support, or Microsoft Customer Service. all of the microsoft.public.access... newsgroups are public user forums where anyone can ask and/or answer a posted question. i downloaded the Time and Billing template from the MS website just now to take a look. from the Main Switchboard, i clicked on Enter/View Clients. then i clicked on the Projects... command button in the form's header section. on my pc, the Projects form is working normally, showing all totals including in the controls you mentioned. did you change *anything* in your copy of the database besides the actual data? such as the name of a form, control, table, field - anything? > Hi, I downloaded the "Time and Billing" database for access from the > "templates" section of your website yesterday. The link is: [quoted text clipped - 13 lines] > Best, > Susanna Hi Tina, I didn't change anything, I swear! Any ideas? > this is not Microsoft Support, or Microsoft Customer Service. all of the > microsoft.public.access... newsgroups are public user forums where anyone [quoted text clipped - 26 lines] > > Best, > > Susanna hi Susanna. there's nothing more frustrating than an application that works for other people, but not for you, i know. i see that Albert has entered the thread; his comments are correct, and as an MVP he's better equipped to help you solve this problem than i am, so i'll just watch the thread from now on - and you and i will both learn something! :) > Hi Tina, > [quoted text clipped - 30 lines] > > > Best, > > > Susanna Hi Tina, I didn't change anything, I swear! Any ideas? > this is not Microsoft Support, or Microsoft Customer Service. all of the > microsoft.public.access... newsgroups are public user forums where anyone [quoted text clipped - 26 lines] > > Best, > > Susanna #Name appears because a reference to a field in the formulas is either misspelled or does not exist. Look at the formulas. Did you change the name of a field or delete the field? Since #Name appears three times, it would seem that the problem in all three formulas is the same field. #Error is occurring because of the first problem. Fix it and #Error will go away. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com > Hi, I downloaded the "Time and Billing" database for access from the > "templates" section of your website yesterday. The link is: [quoted text clipped - 13 lines] > Best, > Susanna Hi PC Datasheet, I'm brand new to access, and I didn't touch anything. These are the formulas -- can you tell what's wrong? =IIf([Projects Subform].Form.RecordsetClone.RecordCount=0,0,[Projects Subform].Form![TotalHoursWorked]) =IIf([Projects Subform].Form.RecordsetClone.RecordCount=0,0,[Projects Subform].Form![Total Billed]) =IIf([Project Expenses Subform].Form.RecordsetClone.RecordCount=0,0,[Project Expenses Subform].Form![Total Expenses]) =[Total Hourly Billings]+[Total Expenses] =IIf(IsNull([ProjectID]),0,DSum("[PaymentAmount]","Payments","[ProjectID]=" & [ProjectID])) > #Name appears because a reference to a field in the formulas is either > misspelled or does not exist. Look at the formulas. Did you change the name [quoted text clipped - 25 lines] > > Best, > > Susanna Your reference to the subform's recordsetclone and field on the subform seem to be wrong. The general syntax is: Forms!NameOfMainForm!NameOfSubformControl.Form.RecordsetClone Forms!NameOfMainForm!NameOfSubformControl.Form!NameOfFieldOnSubform To emphasize, you use the name of the subform control and not the name of the subform form!!!! -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com www.pcdatasheet.com > Hi PC Datasheet, > [quoted text clipped - 11 lines] > > =[Total Hourly Billings]+[Total Expenses] =IIf(IsNull([ProjectID]),0,DSum("[PaymentAmount]","Payments","[ProjectID]=" > & [ProjectID])) > [quoted text clipped - 27 lines] > > > Best, > > > Susanna Ok, so at the risk of sounding like a moron, how should i change this: =IIf([Projects Subform].[Form].[RecordsetClone].[RecordCount]=0,0,[Projects Subform].[Form]![TotalHoursWorked]) to make it look like what you said needs to be done? > Your reference to the subform's recordsetclone and field on the subform seem > to be wrong. The general syntax is: [quoted text clipped - 66 lines] > > > > Best, > > > > Susanna I tried this: =IIf([Forms]![Projects Subform]![TotalHoursWorked].[Form].[RecordsetClone]=0,0,[Forms]![Projects Subform]![TotalHoursWorked].[Form]![TotalHoursWorked]) and got the same message. Any ideas? > Ok, so at the risk of sounding like a moron, how should i change this: > [quoted text clipped - 73 lines] > > > > > Best, > > > > > Susanna It still doesn't appear you have it all!! Is Projects Subform the name of the subform control on the main form? If so, that part is good. You now need to add the name of the main form after "Forms". Look at the general syntax I gave you in my previous response. The syntax is: Forms!NameOfMainForm!............... Steve PC Datasheet > I tried this: > =IIf([Forms]![Projects [quoted text clipped - 40 lines] > > > > > > > > =[Total Hourly Billings]+[Total Expenses] =IIf(IsNull([ProjectID]),0,DSum("[PaymentAmount]","Payments","[ProjectID]=" > > > > & [ProjectID])) > > > > [quoted text clipped - 34 lines] > > > > > > Best, > > > > > > Susanna Hi Steve! Ok, here's what I did, and I still can't get it to work: =IIf([Forms]![Projects]![Projects Subform].[Form].[RecordsetClone]=0,0,[Forms]![Projects]![Projects Subform].[Form]![TotalHoursWorked]) I refuse to give up!!! What am I doing wrong? > It still doesn't appear you have it all!! Is Projects Subform the name of > the subform control on the main form? If so, that part is good. You now need [quoted text clipped - 105 lines] > > > > > > > Best, > > > > > > > Susanna Ah - Hah!!! RecordsetClone is not a field and therefore can not be set to 0. RecordsetClone is the group of records in your subform. In an earlier post you had: =IIf([Projects Subform].Form.RecordsetClone.RecordCount=0,0,[Projects Subform].Form![TotalHoursWorked]) Did you delete or leave out RecordCount when you made the changes? Steve PC Datasheet > Hi Steve! > [quoted text clipped - 17 lines] > > > I tried this: > > > =IIf([Forms]![Projects Subform]![TotalHoursWorked].[Form].[RecordsetClone]=0,0,[Forms]![Projects > > > Subform]![TotalHoursWorked].[Form]![TotalHoursWorked]) > > > [quoted text clipped - 12 lines] > > > > > to be wrong. The general syntax is: > > > > > Forms!NameOfMainForm!NameOfSubformControl.Form.RecordsetClone Forms!NameOfMainForm!NameOfSubformControl.Form!NameOfFieldOnSubform > > > > > To emphasize, you use the name of the subform control and not the name > > of [quoted text clipped - 24 lines] > > > > > > > > > > > > =[Total Hourly Billings]+[Total Expenses] =IIf(IsNull([ProjectID]),0,DSum("[PaymentAmount]","Payments","[ProjectID]=" > > > > > > & [ProjectID])) > > > > > > [quoted text clipped - 20 lines] > > the > > > > > > > > "templates" section of your website yesterday. The link is: http://office.microsoft.com/en-us/templates/TC010184611033.aspx. > > > > > > > > I have office 2003 and Windows XP. Everything works fine until > > I [quoted text clipped - 19 lines] > > > > > > > > Best, > > > > > > > > Susanna A few things: First, I downloaded the application, and with a2003, it runs just fine. 2nd: The forms ref works fine also. You do not need to re-write, or change the forms ref. While it is true that you normally need the full forms reference, since the control is on the current form..then you can ELIMINATE the main forms references (so, all those other posts about trying to fix the forms ref is a wild goose chase...and....and the download as "is" should work just fine on your computer). You do NOT need to qualify the full forms ref as the posters have suggested. So, what I would do is launch the application, but hold down the shift key..and not have/allow the main menu form (often called a switch board) run. Now, while looking at the application, hit ctrl-g, and then go debug-Compile "Time and Billing" Do you get any errors? It would not make sense for this application to be posted with errors...as the amount of feedback would be so great as to have the application removed. It is certainly only a "sample" application..but it does run just fine on my computer.. What this means is some how you accident modified the application, (or modified references). Or, there is perhaps something wrong with your install of office. So, try the above compile idea.....and make sure it compiles. As a further thing to try, would be to convert the database to 2002-2003 format, and give that a try... Tools->database utilities->convert database.  SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal Hi Albert, Thanks so much for your suggestions -- unfortunately, neither of them worked :-( Maybe it's something with my computer? Do you have any other ideas? > A few things: > [quoted text clipped - 32 lines] > > Tools->database utilities->convert database. > Hi Albert, > > Thanks so much for your suggestions -- unfortunately, neither of them > worked :-( Maybe it's something with my computer? Do you have any > other ideas? It's an issue with macro security. RecordsetClone is a blocked expression. For more information and the workaround, see http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 SignatureJoan Wild Microsoft Access MVP Okay, I was able to get to a point where most of the problems with the template disapeared simply by putting Macro security to low (risky, I know, but if I only ever use Access for trusted databases I should be fine), so now everything works just fine, right up to the moment when I hit the "Preview Invoice..." button in the "Client" form. When I hit this button, the "Print Invoice" Form comes up propperly, and the Project ID and name display propperly, but no items appear under the words "Time worked on this project for the specified time period." Also, all information under "Total Hours", "Total Hourly Billings", etc. show up as zeros. When I press the "Preview Invoice..." button, the "Invoice" Report comes up, with all company and client information correct, but nothing listed under either "Time:" or "Expenses:" and an "Invoice Total" of zero. Any suggestions? > A few things: > [quoted text clipped - 32 lines] > > Tools->database utilities->convert database. >Now, while looking at the application, hit ctrl-g, and then go >debug-Compile > "Time and Billing" > > Do you get any errors? did you try the above? I'm fairly certain I did, because when I go into the Global Code under Modules the Compile option isn't even available under the Debug menu, it's been greyed out. > >Now, while looking at the application, hit ctrl-g, and then go > >debug-Compile [quoted text clipped - 3 lines] > > did you try the above? I too am trying to find a solution for this exact scenario. I have already tried the macros on low security and have gotten the same results: 0's and no projects associated with the invoice. This is better than the #erro messages in the fields, but not right. I still haven't tried the solution mentioned by Russell (while looking at the application, hit ctrl-g, and then go debug-Compile "Time and Billing") and will try that next, but just wanted to let you all know that I am also running Access2003 and make no changes to the db as downloaded from MS, I have all latest security patches running and no other complaints about my machines config. In fact, I have tried this on several machines and it is the same results. So I will try a few more things and hope that it works, but if anyone has any more suggestions, please feel free to bring them to our attention. thanks > I'm fairly certain I did, because when I go into the Global Code under > Modules the Compile option isn't even available under the Debug menu, it's [quoted text clipped - 7 lines] > > > > did you try the above? See http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 > I too am trying to find a solution for this exact scenario. I have > already tried the macros on low security and have gotten the same [quoted text clipped - 26 lines] >>> >>> did you try the above? SignatureJoan Wild Microsoft Access MVP The info in the link works, but only partly. For it to have full effect, you must also alter similar (but not the same) code in the "Print Invoice" form. This has worked for me. Also, I have discovered that the "Begin Date" and "End Date" fields cannot be left blank. > See > > http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 It looks like the discussion ended without a solution. Did anyone ever figure it out, or is it just a crappy template? > The info in the link works, but only partly. For it to have full effect, you > must also alter similar (but not the same) code in the "Print Invoice" form. [quoted text clipped - 4 lines] > > > > http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 Have you followed the steps outlined in the google group thread? That supplimented with Russell's notes should solve your problem. Are you still having problems? As for the usefulness of the template, it might be looked at as a starting point. I doubt that any of the templates can be used as is; they'd all need tweaking I would think. > It looks like the discussion ended without a solution. Did anyone ever > figure [quoted text clipped - 10 lines] >> > >> > http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 SignatureJoan Wild Microsoft Access MVP I checked the Google group link and tried to follow the instructions but I can't find the "Broken Expressions". I used ctrl-g to get to the modules and tried to find it there. What am I doing wrong? Also, what is "sandbox mode". I am reasonably computer literate but not particularly experienced with Access. When I tried the debug-compile there was no indication of anything happening. Thanks! > The info in the link works, but only partly. For it to have full effect, you > must also alter similar (but not the same) code in the "Print Invoice" form. [quoted text clipped - 4 lines] > > > > http://groups-beta.google.com/group/microsoft.public.access/msg/9c97a482f446a678 >I checked the Google group link and tried to follow the instructions but I > can't find the "Broken Expressions". I used ctrl-g to get to the modules > and > tried to find it there. When you use find, be sure to check off 'search entire project' to find the code you are looking for. You may find that going to Tools, Macro, security and setting it to low will get things working for you. SignatureJoan Wild Microsoft Access MVP Thanks Joan. At the risk of sounding like a complete idiot, there is security option under Tools, Macro. I looked other places to set this option (e.g., Tools, Security) and did a help search and can't find anywhere to set any security setting to low. I am using Access 2002. I appreciate your help. I ever get this database working we'll have to have a celebration! Thanks again, Dana > >I checked the Google group link and tried to follow the instructions but I > > can't find the "Broken Expressions". I used ctrl-g to get to the modules [quoted text clipped - 6 lines] > You may find that going to Tools, Macro, security and setting it to low will > get things working for you. > Thanks Joan. At the risk of sounding like a complete idiot, there is > security > option under Tools, Macro. I looked other places to set this option (e.g., > Tools, Security) and did a help search and can't find anywhere to set any > security setting to low. I am using Access 2002. I appreciate your help. I > ever get this database working we'll have to have a celebration! That's the option I meant. Tools - Macro - Security. Did that work for you? SignatureJoan Wild Microsoft Access MVP Oh the power of one little word! What I meant was there was NO Tools-Macro-Security. There was Tools-Macro, but no security option and not other options where I could set the security. > > Thanks Joan. At the risk of sounding like a complete idiot, there is > > security [quoted text clipped - 5 lines] > That's the option I meant. Tools - Macro - Security. Did that work for > you? > Oh the power of one little word! What I meant was there was NO > Tools-Macro-Security. There was Tools-Macro, but no security option and > not > other options where I could set the security. Reset your menubar (View, Toolbars, Customize). Select the Main Menu (don't uncheck it thought) and hit Reset . SignatureJoan Wild Microsoft Access MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.