 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / July 2007Simple Syntax for Table Reference Problem
Hello, I have a linked table tblGoals, which has [Messages] of type integer. I am simply trying to assign dMsgGoals = [tblGoals].[Messages]/20. Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50. I think my syntax is off because I keep getting "Access can't find the field "|" referred to in your expression." Your help on this simple problem is much appreciated. >I have a linked table tblGoals, which has [Messages] of type integer. I am >simply trying to assign [quoted text clipped - 5 lines] >I think my syntax is off because I keep getting "Access can't find the field >"|" referred to in your expression." VBA code can not just refer to a field in a table, it must look up the appropriate record and retrieve the value of the field. There are many ways to do that, the "best" way depends on what else you are doing. If the Messages field is in the form's record source, then you can refer to it as a property of the form, e.g. Me.Messages If the field is not related to the form's record source, then you can use the DLookup function to get the value: DLookup("Messages", "tblGoals") but if tblGoals has more than one record, criteria must also be used to select the appropriate record with the value.  SignatureMarsh MVP [MS Access] I think we are getting somewhere. The new error is "Invalid use of null." Messages is of type integer with a value of 1000. > >I have a linked table tblGoals, which has [Messages] of type integer. I am > >simply trying to assign [quoted text clipped - 20 lines] > but if tblGoals has more than one record, criteria must also > be used to select the appropriate record with the value. That might be a useful clue if I could see the code that you used when you got that error and you identified the line that Access complained about. Making some guesses, dMsgGoals is declared as a Double, you used DLookup and it didn't find anything so it returned Null, which can not be stored in anything other than a Variant. SignatureMarsh MVP [MS Access] >I think we are getting somewhere. The new error is "Invalid use of null." >Messages is of type integer with a value of 1000. [quoted text clipped - 23 lines] >> but if tblGoals has more than one record, criteria must also >> be used to select the appropriate record with the value. dMsgGoal = DLookup("Messages", "tblGoals"). dMsgGoal as Integer. > That might be a useful clue if I could see the code that you > used when you got that error and you identified the line [quoted text clipped - 31 lines] > >> but if tblGoals has more than one record, criteria must also > >> be used to select the appropriate record with the value. >dMsgGoal = DLookup("Messages", "tblGoals"). >dMsgGoal as Integer. This will look up the value of the field named [Messages] in the table [tblGoals], returning the value from the first record in the table. If there is one record, or 100 records, or 100000 records in the table, it makes no difference - you'll always get the value from the first record in disk storage order. I somehow suspect that is not your intent. John W. Vinson [MVP] If that's the line with the error, the error message means that tblGoals has a null value in the Messages field or that tblGoals has no records. If that's not the line with the error, then I can't guess without seeing the rest of the procedure. SignatureMarsh MVP [MS Access] >dMsgGoal = DLookup("Messages", "tblGoals"). >dMsgGoal as Integer. [quoted text clipped - 35 lines] >> >> but if tblGoals has more than one record, criteria must also >> >> be used to select the appropriate record with the value. I have not gotten far on this test form I am building (is i t because it is a Form_Open?): Private Sub Form_Open(Cancel As Integer) 'Dim MaxCallTime As Integer 'Maximum minutes in call period 'Dim CallStartTime As Integer 'When does calling start Dim dMsgGoal As Integer 'daily recruiting message goal dMsgGoal = DLookup("RecruitMessages", "tblGoals", "") 'MaxCallTime = 210 'Max minutes: 8:30AM to noon End Sub > If that's the line with the error, the error message means > that tblGoals has a null value in the Messages field or that [quoted text clipped - 41 lines] > >> >> but if tblGoals has more than one record, criteria must also > >> >> be used to select the appropriate record with the value. Please explain what is in tblGoals. Without more information, I have had to assume that there is only one record (with one field). Your code below looks up the value in the RecruitMessages field in the first (only?) record, assigns it to a **local** variable, and then exits (which discards the local variable). Because of your use of a local variable, the procedure has no effect on anything in your application. The Open event may, or may not, be an appropriate place to do this. It depends on what your objective for the dMsgGoal variable happens to be. Lacking all that important information, you might(?) make more progress using: Dim dMsgGoal As Integer 'daily recruiting message goal Private Sub Form_Open(Cancel As Integer) dMsgGoal = DLookup("RecruitMessages", "tblGoals") / 20 End Sub SignatureMarsh MVP [MS Access] >I have not gotten far on this test form I am building (is i t because it is a >Form_Open?): [quoted text clipped - 53 lines] >> >> >> but if tblGoals has more than one record, criteria must also >> >> >> be used to select the appropriate record with the value. The idea of tblGoals is a place where the powers-that-be can set goals for specific activities - one is recruiting. There will be one entry per category, such as RecruitMessages left set to 1000, for example, and Interviews set to whatever. The thought is when the goal changes a single entry in tblGoals will feed the change to other forms. There is one record in tblGoals with fields such as RecreuitMessage=1000, Interviews=20, Hires=10 I hope this helps. Thanks MUCH for sticking with this problem! > Please explain what is in tblGoals. Without more > information, I have had to assume that there is only one [quoted text clipped - 75 lines] > >> >> >> but if tblGoals has more than one record, criteria must also > >> >> >> be used to select the appropriate record with the value. OK, it's good to know that my assumption was correct. But, you still need to explain what you are trying to do with the value from the table. Just stuffing it into a variable, public or not, won't accomplish anything. If you just want to display the goal in a text box on a form/report, then you don't need any code. Simply use the DLookup in the text box's control source exoression: =DLookup("RecruitMessages", "tblGoals") / 20 If you have something else in mind, don't keep it a secret. SignatureMarsh MVP [MS Access] >The idea of tblGoals is a place where the powers-that-be can set goals for >specific activities - one is recruiting. There will be one entry per [quoted text clipped - 68 lines] >> >> >> >> >I think my syntax is off because I keep getting "Access can't find the field >> >> >> >> >"|" referred to in your expression." tblGoals is suppose to store the monthly goals. I'll need to break that down into weekly and business weekly goals (5days). So, when someone presses the "Dial" button, for example, the user will see if he is on track for the number of messages to leave for the day. So, given the start time, the current time and the end time (noon) I will be able to calculate if the user is on pace to reach the goal or needs to pick up the pace. Other fields have other uses, but all of them are of the same nature - a goal to be used to show if on-pace for the time period (day/week/month/quarter). Hopefully this is an OK way get it done. Thanks MUCH! > OK, it's good to know that my assumption was correct. But, > you still need to explain what you are trying to do with the [quoted text clipped - 78 lines] > >> >> >> >> >I think my syntax is off because I keep getting "Access can't find the field > >> >> >> >> >"|" referred to in your expression." Since a goal is only useful(?) information (not something used to control the form's behavior), can I conclude that all you want to do is display it? If so, try using a text box with the expression I suggested earlier. SignatureMarsh MVP [MS Access] >tblGoals is suppose to store the monthly goals. I'll need to break that down >into weekly and business weekly goals (5days). So, when someone presses the [quoted text clipped - 17 lines] >> =DLookup("RecruitMessages", "tblGoals") / 20 >> If you have something else in mind, don't keep it a secret. NO, I do not only want to display it. The goal will change from tim-to-time. The "goal setter" will have a simple form to change the goal. The value for the goal will be used by what I am doing to calculate if on pace or not. I will need to store the various goals and then operate on them. > Since a goal is only useful(?) information (not something > used to control the form's behavior), can I conclude that [quoted text clipped - 21 lines] > >> =DLookup("RecruitMessages", "tblGoals") / 20 > >> If you have something else in mind, don't keep it a secret. Then you need to save the value of the DLookup, probably in a bound text box. Use your form's Load event instead of the Open event. The code we had earlier should work if you add a text box named dMsgGoal. SignatureMarsh MVP [MS Access] >NO, I do not only want to display it. The goal will change from tim-to-time. > The "goal setter" will have a simple form to change the goal. The value for [quoted text clipped - 16 lines] >> >goal to be used to show if on-pace for the time period >> >(day/week/month/quarter). tblGoals is an external file. How would I do a Dlookup on an external file? > Then you need to save the value of the DLookup, probably in > a bound text box. [quoted text clipped - 22 lines] > >> >goal to be used to show if on-pace for the time period > >> >(day/week/month/quarter). Do you always provide critical need to know information in tiny little trickles ;-) What is this "external file"? Can you link to it? SignatureMarsh MVP [MS Access] >tblGoals is an external file. How would I do a Dlookup on an external file? > [quoted text clipped - 25 lines] >> >> >goal to be used to show if on-pace for the time period >> >> >(day/week/month/quarter). I completely deserved that one - my bad. I really thought I gave ALL of the pertinent information. It is a linked table. Does it make a difference in this case? The file is Goals.MDB. Do you need the complete path? > Do you always provide critical need to know information in > tiny little trickles ;-) [quoted text clipped - 30 lines] > >> >> >goal to be used to show if on-pace for the time period > >> >> >(day/week/month/quarter). If it's linked. then Access takes care of the path and other stuff so the fact that it's in another mdb file is irrelevant to this problem. Just use the table name in your front end mdb as if it were actually a local table. You can test this yourself without getting wrapped up in the complexities of your form. Use Ctrl+G to open the VBE Immediate window. Then type: ?DLookup("RecruitMessages", "tblGoals") / 20 and hit enter. You should then see the result or get some kind of error so you can fix things and try again. SignatureMarsh MVP [MS Access] >I completely deserved that one - my bad. I really thought I gave ALL of the >pertinent information. It is a linked table. Does it make a difference in [quoted text clipped - 35 lines] >> >> >> >goal to be used to show if on-pace for the time period >> >> >> >(day/week/month/quarter). |
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.