field references in forms

If I have a form "myform" with recordsource "mytable" and the table contains 
a field called "id," I can reference the current value of that field in 
expressions used for controls in the form by myform.id.  This is true even 
when there is no control bound to mytable.ID on the form.  I can "print" the 
value of myform.id in the debug immediate window.

If I try the same thing for a form whose record source is a query, ? 
myform.id produces an error.

Why the difference?

And a corollary question: what's the most efficient way to test the values 
of expressions, for example the condition clause (the third clause) of 
DLookup, after constructing it in the expression builder?

Thanks for any help,  Ron 

0
Ron
2/11/2010 4:13:44 AM
access.gettingstarted 618 articles. 1 followers. Follow

4 Replies
592 Views

Similar Articles

[PageSpeed] 25

"Ron" <Harvested@comcast.net> wrote in message 
news:hl005r$t6$1@news.eternal-september.org...
> If I have a form "myform" with recordsource "mytable" and the table 
> contains a field called "id," I can reference the current value of that 
> field in expressions used for controls in the form by myform.id.  This is 
> true even when there is no control bound to mytable.ID on the form.  I can 
> "print" the value of myform.id in the debug immediate window.
>
> If I try the same thing for a form whose record source is a query, ? 
> myform.id produces an error.
>
> Why the difference?

You are mistaken.  So long as the form's recordsource query includes the 
field in the list of fields that it selects, the field is available as a 
property of the form even if there is no control bound to that form.  If the 
field is present in the form's recordset (obtained by querying the 
recordsource), the field can be accessed via the form.  If the field is not 
present in the form's recordset, it can't be access via the form.

So if your form has recordsource "SELECT ID, OtherField FROM MyTable", ID 
can be retrieved via Forms!MyForm.ID .  But if your form has recordsource 
"SELECT OtherField FROM MyTable", then the reference will fail.

> And a corollary question: what's the most efficient way to test the values 
> of expressions, for example the condition clause (the third clause) of 
> DLookup, after constructing it in the expression builder?

Sorry, I don't understand the question you're asking well enough to answer 
it.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
2/11/2010 5:31:39 AM
Thanks Dirk.  I just learned something new (and I tested it).  I always 
thought you could only reference controls on a form in a query.

Something new to add to my knowledge.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
0
John
2/11/2010 1:19:07 PM
Do you want to test if the syntax of the condition clause is correct, if the 
logic is correct, or if Dlookup returns a value?

The only way that I know to test the syntax is to try it - that may generate 
an error.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ron wrote:
> If I have a form "myform" with recordsource "mytable" and the table 
> contains a field called "id," I can reference the current value of that 
> field in expressions used for controls in the form by myform.id.  This 
> is true even when there is no control bound to mytable.ID on the form.  
> I can "print" the value of myform.id in the debug immediate window.
> 
> If I try the same thing for a form whose record source is a query, ? 
> myform.id produces an error.
> 
> Why the difference?
> 
> And a corollary question: what's the most efficient way to test the 
> values of expressions, for example the condition clause (the third 
> clause) of DLookup, after constructing it in the expression builder?
> 
> Thanks for any help,  Ron
0
John
2/11/2010 1:24:03 PM
> Do you want to test if the syntax of the condition clause is correct, if 
> the
> logic is correct, or if Dlookup returns a value?
>
> The only way that I know to test the syntax is to try it - that may 
> generate an error.
>

Thanks for the response.

Actually Access does give hints about syntax errors, at least after I've 
used the expression builder to build a control source property.  If there's 
a syntax error (because I did some typing in there), I get a message when I 
click away from that property. No, I meant "test" in the sense of 
"evaluating the expression."  I can copy and paste the expression into the 
debug immediate window, but when there's an error, I'm never quite sure if 
I've done something wrong or if I'm simply out of scope.

Thanks also to Dirk!  I will test again to see why I made my observation, 
correct syntax, etc., and repost if the problem to which my question 
pertains persists.  (I am trying to build a DLookup criterion parameter. 
The DLookup would serve as the control source for a textbox on a main form. 
The criterion parameter must reference a query which itself is the 
recordsource for a subform.  I was having trouble referencing the 
query.field, so I tried the subform.field and (thought I) noticed the 
disparity I posted.)

-Ron 

0
Ron
2/11/2010 2:53:42 PM
Reply:

Similar Artilces:

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Required fields
I need to set up some VB code to alert the inputter that they need to complete a couple of required fields depending on the value of a third field. At present I have some code (below) attached to the BeforeUpdate event of the form that checks for one of the fields but I need to extend it to the 2nd field and I don't know how to. Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.[NYSPA Received] And IsNull(Me.Date_NYSPA_Received) And Me.New_Cease = "New" Then MsgBox "Date NYSPA Received is required" & vbCrLf & "Record will not be saved&q...

Stripping Blank Characters form Imported Data
I am trying to verify 2 worksheets full of data. One has been imported from Visual Basic and the other from Business Object. The Business object sheet, brings over the entire field not just the charaters needed. Example: For the city field there are 50 characters available. If you use Nashville, the Business Object brings over 50 characters where the Visual Basic brings over 9. When trying to find descrepancies, this brings back a false positive if the city is the same. Is there a way to manipulate that data when it is sent to Excel? The easiest way to deal with this would be to find...

How do I format a field based on the value
I have a form that is populated from a query using several tables. In the detail section I have the following fields: Category Code qty PO number ... .... ... The qty is populated in the query based on whether or not a PO number is available. If the PO number is not available then it comes from table a, if it is available then it comes from table b. On the form, I have my fields color-coded for easy reading. I would like to be able to color the border and column heading based on where this value comes from. If table A, then it is blue, if table b then it...

Keep Added Custom Fields on New Projects
We are importing information from our bidding software. The export is created in XML file. We know the custom fields that are created. How do I make MS Project use the same "look" everytime? I want it to remember the custom fields I created and use that as my default blank project. I don't care about the tasks because those could be different every time. I just want to be able to create each project with the same columns every time. Create or update a view/table with the custom fields and layout you are wanting. Then change the Tools-->Options--View tab to ...

Trouble with Forms based authentication in OWA
I am having some problems in setting up OWA with forms based authentication. From my understanding it should work like this: I go to my usual address http://<myserverdomain>/Exchange and I will be directed to the Forms Based Authentication login screen. Once I am there I can choose basic or premium function in OWA and once I authenticate I should be able to access OWA without any other login prompts. Well none of that is happening. Here is what I have done so far. 1. Enabled Forms based authentication in Exchange System manager. 2. Verified that that Authenticated Users group has Read...

click on page form tab to filter subform
I have page tab with following names: Flat Rate, Global Rate, MSRP Rate. I would like to have subform only show the rates for each page tab. How do I go about doing this. I am new to VBA and from reading all the threads, I can't seem to find the one that fits my exact need. Please explain step-by-step since I am new. Let me know if I need to provide more details. Thank you. Hi Kris Presumably you have a field in the RecordSource of your subform that contains one of the three values (Flat Rate, Global Rate, MSRP Rate) or some other text or code that corresponds to these values....

how to retrieve varbinary field through ADO inVC++?
...

Query by Form Problem 06-19-07
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? I've looked exhaustive...

Table relationships
I've created a database with about 15 tables for a participant data in a research study. Each participant will complete the 15 assessment measures. My ultimate goal is to create a data entry system whereby a research assistant can pull up a form and enter an ID number, plus the answers to the 15 assessment measures. I know I can create a query to combine all the tables, but it seems like the research assistant would have to enter the id and date 15 times (once for each table pulled in to the query). Is there a way to just enter the ID number one time and have it apply t...

Lock and Unlock a Form
I have read many of the other posts in regards to locking and unlocking forms but i am still unable to get it to do what i want. Let me explain my setup. I am working on form to help capture incidents that we have. Each form can have up to 4 subforms. 1 subform is embedded on the main form and the others are on tabs and can be opened using an option group. I have one main number as a key to group all the forms together in a 1-1 relationship. On the main form I have added a combo box with "open" and "close" to help prevent accidental erasing of already entered recor...

Calculated Fields
Is there a way to use an IF or SUMIF function in a calculated field in a pivot table? When I try, I get all 0s as the result. Can you type a small sample of the pivot table layout and data, and explain what you'd like to calculate with a SUMIF formula? Jim C. wrote: > Is there a way to use an IF or SUMIF function in a > calculated field in a pivot table? When I try, I get all > 0s as the result. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi Jim, > Is there a way to use an IF or SUMIF function in a > calculated f...

Populating an IMAP "To:" field
I let this bug me for almost 2 years, but it *IS* Possible, as the following points out (contrary to the lack of responses and denial posted elsewhere in these microsoft misinformation forums): For Outlook 2000/XP/2002: http://support.microsoft.com/?kbid=284371 For Outlook 2003: 1) Select the IMAP mail folder with the broken/empty "To:" field. 2) Go to the "Tools" menu and select "Rules and Alerts..." 3) Select the IMAP account for this folder (should be pre-selected) 4) Click the "New Rule..." button 5) Select the "Start from a blank rule" r...

CRM, Reporting Services & Forms Authentication
Our company has an app with forms auth that is integrated with Reporting Services. We have forms auth working successfully with Reporting Services. We'd like to integrate CRM and still use forms auth. Is this possible with CRM? Would anyone be kind enough to point me to any documentation or examples of CRM w/ forms auth & Reporting Services? ...

auto populating account id field
Does anyone know of any 3rd parties that already have this customization? I'm wanting my account to automatically be assigned an account ID when I transfer my lead to an account and then have this ID be sent to Great Plains to create the account in GP. I know CRM doesn't have this function right out of the box, but I can't let my users type in their own ID. Thanks, Jen ...

Form with adding
Hello I want to create a form for people who use a cabin The columns titles are: name, number of nights, age group, $ per night, then total $'s The $'s per night depends on the age group. It's $10 for ages 0-12, $20 for 13-18, and $30 for 19+. I'm doing a drop down menu for the age groups. Based on their choice, I want the $'s per night to come up automatically in the next column My questions are 1) Is it best to be doing this in Excel, or should I use Word 2) Assuming Excel, how do I set up the $'s per night based on the drop down menu selection 3) I'd like ...

How to detect when user switches between open forms?
The user can have multiple non-modal, restored, forms open at once and can switch between them as they want, often by just clicking on their titlebars. These forms are all instances of F_URL. I am using Vista, Access 2007. How do I run a routine anytime the user switches form? F_URL.GotFocus doesn't fire because the Focus event only fires for the particular control that has the focus on the form, and, there are many such possible controls. F_URL.Activate doesn't fire. So, four possibilities as far as I can see: 1. There is probably some obvious event that I'm ju...

Conditional Formatting for Image box on Continuous Forms
Hello again, This problem is driving me crazy. I am a car enthusiast and love taking photos of classic cars. I have a table which describes the make and model in a series of fields. There are several fields that contain a reference to photos of those cars. Ie: the fields are called "Front" "Side" "Rear" "Angle". My form has four image boxes to display the images stored in these fields. But when I open the form all the records show the images of the first car only. I asked about this a few days ago and was advised to check out http://www.lebans.co...

How do i print an excel file in the form of a booklet?
I have a list of phone numbers and addresse that I need to print in the form of a small booklet that I can carry in my purse. How do I print it? What program and how to set it up? Also I want ohotos on the left and data on the right side pages. I'm not sure excel is the best application for this kind of thing. I'm sure you could do it (after a few hours/days/months of frustration!), but maybe you could find a better application by searching google. Or even put the data into MSWord and use that for all the nice formatting. shireen wrote: > > I have a list of p...

Form now Sub-Form, Query Broken
I recently posted this problem in "Forms" (Lost Focus of Form 7/27) thinking that was the problem, but recently discovered the problem appears to be in the query(ies). I've dropped a from into a form that has multiple tabs/pages to consolidate things. The other - now sub-forms still function as before, but this one retrieves records from the archive and drops them in the the main employee table. The following pops up as an input box - Forms.Archive.track I found this in one of the queries where: Field: Track - its the unique identifier for the employee table Table: E...

Reporting table fields vertically
I've come across a client with a non-normalized table with sales of items for eight different regions. So, the fields in the table are: ItemCode Region1SalesQty Region2SalesQty .... .... Region8SalesQty Typically the client is printing these sales in columns, with a report that has the eight regions spaced horizontally across the page. Something like this: ItemCode Region1SalesQty Region2SalesQty ... Region8SalesQty However, they now need a report with the sales reported vertically, like this: ItemCode Region1SalesQty Region2SalesQty ...

How to reference fields/objects on a form
Hello, Can anyone help me understand how to reference the fields/objects on a form when querying/updating database tables? Example: Form name = "frm_Cont_master" 1. SSN (text box) 2. First name (text box) 3. Last Name (text box) 4. Company (Assume this is a combo box selecting Company name and/or Company ID number from a seperate lookup table). 5. Department (Assume this is combo box selecting department name/number from 2nd lookup table, based on the value in #4 above). Assume Company ABC has departments 1-5 and Company XYZ has departments 6-9. Selecting ABC in ...

Restriction on appending data to custom fields on Account Form?
I'm having problems appending data to a custom field on the Account Form. However, the code works perfectly on a custom form, I've made. Btw, the ONLOAD code is: crmForm.all.fieldname.DataValue = 'Text'; On the custom form, the field is filled with the word Text, but on the Account form, nothing happens. Is there some kind of restriction on the account form, and if so - is it possible to remove this restriction? Thanks btw, another example to illustrate the problem: I use this code... window.moveTo(0,0); window.resizeTo(screen.availWidth, screen.availHeight); ....to...

get data form cells sequentially
Hi there, I have a spreadsheet i am creating which is giving me some amount o hassle!!! I'm sure it is possible to do this but i just cant figure ou how:- I have one worksheet which has many copies of the same table, eac table is consistantly ontop of the other i.e. first table uses cells A - G10 (a rectangle of cells). The dates i am trying to use are in a pattern of a nine row seperatio i.e. first date is in cell G2, next one is G11, next one G20, and s on. I need to find out how i can make a formula which i could copy whic would grab the date out of these cells in sequence for pl...

Pagination on a Continuous Form
I would like to have one continuous form hold a list of 30 records per page, and be able to see the page numbers (and use them) at the top and/or bottom of the form, such that a total of a couple hundred records (or more) can be displayed in groups of 30 at the request of clicking the page number (similar to that of a webpage, but only an MS Access Form). On Fri, 5 Mar 2010 11:23:01 -0800, VWP1 <VWP1@discussions.microsoft.com> wrote: You'll have to do all the work yourself, because such interface is not natively supported. Would be an interesting project though. -To...