DLookup default Value

I am having trouble getting the correct syntax using DLookup as a default 
value in a text box on a form.  

Form Name:  FrmMainInput
Unbound control Name: FunctionNumber

I am trying to get the default value to look up the FunctionNumber, using 
the FunctionName from the table TblFunction.  I am using the expression below 
in the default value of the FunctionNumber control, but am not getting any 
value.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" & 
[Forms]![FrmMainInput]![FunctionNumber] & "")


0
Utf
2/22/2008 4:18:13 PM
access.formscoding 7494 articles. 0 followers. Follow

5 Replies
1775 Views

Similar Articles

[PageSpeed] 52

"rbb101" <rbb101@discussions.microsoft.com> wrote in message 
news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
>I am having trouble getting the correct syntax using DLookup as a default
> value in a text box on a form.
>
> Form Name:  FrmMainInput
> Unbound control Name: FunctionNumber
>
> I am trying to get the default value to look up the FunctionNumber, using
> the FunctionName from the table TblFunction.  I am using the expression below
> in the default value of the FunctionNumber control, but am not getting any
> value.
>
> =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> [Forms]![FrmMainInput]![FunctionNumber] & "")

If FunctionNumber is a numeric field, drop the & "'" from the end.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" & 
[Forms]![FrmMainInput]![FunctionNumber])

If it's a text field, you need a single quote between the second equal
sign and the double quote.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] ='" & 
[Forms]![FrmMainInput]![FunctionNumber] & "")

Tom Lake 

0
Tom
2/22/2008 4:37:29 PM
This can't work. The timing is wrong.

Access applies the DefaultValue as soon as you move to a new record, before 
you start the entry. At that time, the FunctionNumber has not been filled 
it.

Use the AfterUpdate event of the FunctionNumber text box to assign the 
value. Example in the 2nd part of this article:
    Calculated Fields
at:
    http://allenbrowne.com/casu-14.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rbb101" <rbb101@discussions.microsoft.com> wrote in message
news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
>I am having trouble getting the correct syntax using DLookup as a default
> value in a text box on a form.
>
> Form Name:  FrmMainInput
> Unbound control Name: FunctionNumber
>
> I am trying to get the default value to look up the FunctionNumber, using
> the FunctionName from the table TblFunction.  I am using the expression 
> below
> in the default value of the FunctionNumber control, but am not getting any
> value.
>
> =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> [Forms]![FrmMainInput]![FunctionNumber] & "") 

0
Allen
2/23/2008 7:05:37 AM
Thanks Allen, that makes sense, but I can't get the syntax correct.  Can you 
help out with that.  

Thanks.

"Allen Browne" wrote:

> This can't work. The timing is wrong.
> 
> Access applies the DefaultValue as soon as you move to a new record, before 
> you start the entry. At that time, the FunctionNumber has not been filled 
> it.
> 
> Use the AfterUpdate event of the FunctionNumber text box to assign the 
> value. Example in the 2nd part of this article:
>     Calculated Fields
> at:
>     http://allenbrowne.com/casu-14.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "rbb101" <rbb101@discussions.microsoft.com> wrote in message
> news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
> >I am having trouble getting the correct syntax using DLookup as a default
> > value in a text box on a form.
> >
> > Form Name:  FrmMainInput
> > Unbound control Name: FunctionNumber
> >
> > I am trying to get the default value to look up the FunctionNumber, using
> > the FunctionName from the table TblFunction.  I am using the expression 
> > below
> > in the default value of the FunctionNumber control, but am not getting any
> > value.
> >
> > =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> > [Forms]![FrmMainInput]![FunctionNumber] & "") 
> 
> 
0
Utf
2/23/2008 4:27:01 PM
The syntax notwithstanding, your code doesn't make a lot of sense. You're
trying to set the value of your control

[FrmMainInput]![FunctionNumber]

and you're asking Access to do this by looking up the field

[FunctionNumber] from the table "TblFunction"

by comparing the field [FunctionNumber] from the table "TblFunction" to the
control 

[FrmMainInput]![FunctionNumber]

which is empty!

You're trying to fill [FrmMainInput]![FunctionNumber] by using [FrmMainInput]!
[FunctionNumber] as the criteria!

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1

0
Linq
2/23/2008 6:55:57 PM
Are you indicating this cannot be done, or that I do not have the correct 
critieria.  I am out of my element when using code.

What I am trying to do is the default value on the control [FunctionNumber] 
on the [FrmMainInput] look up it's value by using the critieria 
[FunctionName] from the table [TblFunction].  [FunctionName] is a field on 
the same form.

I appreciate your feedback.  Thanks.

"Linq Adams via AccessMonster.com" wrote:

> The syntax notwithstanding, your code doesn't make a lot of sense. You're
> trying to set the value of your control
> 
> [FrmMainInput]![FunctionNumber]
> 
> and you're asking Access to do this by looking up the field
> 
> [FunctionNumber] from the table "TblFunction"
> 
> by comparing the field [FunctionNumber] from the table "TblFunction" to the
> control 
> 
> [FrmMainInput]![FunctionNumber]
> 
> which is empty!
> 
> You're trying to fill [FrmMainInput]![FunctionNumber] by using [FrmMainInput]!
> [FunctionNumber] as the criteria!
> 
> -- 
> There's ALWAYS more than one way to skin a cat!
> 
> Answers/posts based on Access 2000/2003
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1
> 
> 
0
Utf
2/25/2008 12:34:02 PM
Reply:

Similar Artilces:

[in horz.bar+chart] Disply dates on value axis?
Something about selcting - 'date option'. But, where, how? -- Boswell You can only select the time scale option for a category axis. This is the axis that the bars grow out of. If you need dates on the value axis, you can simply select a date number format, and provide numerical dates for the Y values. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Boswell" <boswell6@sbcglobal.net> wrote in message news:01A192F9-6684-4B2A-815F-1F6C65528B4F@microsoft.com... > Something about selcting - 'date o...

Returning the last value in a row
Hi What formula do I need to return the last known value in a row? ie. ROW 1 contains 5 values in COL a,b,c,d and e ROW 2 contains 2 values in COL a, b is empty,c,d is empty, e is empty How can I return the last value in each respective ROW (COL e and COL c)? I think I need something to jump from one colum to the next and stopping when it finds a value but still continues to the end of the specific amount of columns just in case there is another value Hope I explained this well Hi if you have no blank columns in between use something like =OFFSET($A$1,0,COUNTA(1:1)-1) for row 1 ...

How do I set the view to default to last modified
I like my files to always be in last modified order with the most recently saved file at the top of the list. In Excel 2000 I set the order in the open window and from then on all files in all folders were in the correct order. Recently I changed to Excel 2003. Since then I have had problems. I change the vies as before but each time i close the application it reverts to sorting by name - in alphabetical order. Any suggections? ...

Repalce value
I have sheet1 that contains A B C D E F SCHWARTZ412BEDFORD 718-599-1113 EPSTEIN415BEDFORD 718-000-7328 656-642-7771 BRACH416BEDFORD 718-202-0373 957-781-4383 Then I have another sheet2 that A B C D E F SCHRITZER412BEDFORD 5/3/1949 F DEM SCHWARTZ412BEDFORD 8/24/1972 M LND SCHWARTZ412BEDFORD 9/29/1769 F DEM What I need is a formula to find the value from sheet 1 a1 in sheet 2 row A then If the value of Row E is =93F=94 it should Copy the value from row F to sheet 1 Cell E1 if the value from row E is =93M=94 then it should copy the Value in sheet 1 col...

Find certain cell value
Hi, I have a matrix with on the rows dates of stock prices and on th columns stock names. Now I'm trying to find a certain stock price on a certain date. If try to find the stock price of Microsoft on January 1st 2005, how woul this be done. I know how to lookup the stock name and the stock date (using VLOOKU etc.) but not how to combine these lookup functions in order to receiv only one cell value. Tnanks in advance. Ro -- Rob ----------------------------------------------------------------------- RobJ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

dlookup with if
Access 2003 On the form called fClosure ApprovalPopUp I have a combobox called SignaturePM. The default value for SignaturePM is currently =DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) How do I edit the above DLookUP to this... =if LoginUser = PMUser then DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) if LoginUser = ManagerUser then DLookUp("[ContactPMgrID]","[t040Project]","[ProjectID] =" &a...

Simple calculation where values change to letters?
I have a simple formula (A1*B1*C1)/((A1*B1*C1)+(D1*E1*F1)+(G1*H1*I1)). However there are times when the values of D through F or the G through I denominators can change into letters (if D changes to a letter so do E and F, same happens to H and I if G changes to a letter). I need the formula to complete the calculation by assuming any value that becomes a letter as zero. Thanks for your help. Regards, ZUO Try this: =PRODUCT(A1:C1)/SUM(PRODUCT(A1:C1),PRODUCT(D1:F1),PRODUCT(G1:I1)) PRODUCT and SUM both ignore text inputs. -- Best Regards, Luke M "Zuo&q...

Portfolio Review--After tax Value is *greater* than pre tax value
Why would the pre-tax value for long term and short term holdings be less than the after tax value? I just discovered the same issue and find your post while investigating. I have no idea what is going on. "WOppenhe" wrote: > Why would the pre-tax value for long term and short term holdings be less > than the after tax value? > > > ...

Can I change the default for custom filters?
"Custom filte" always begins with "Show rows where <field name> EQUALS ..." I almost always use CONTAINS. Can I change the default so that it'll automatically display CONTAINS as the starting criterion? If so, how? I'm in Mac OS 10.4.7. Thanks. You can't change it the wintel version. But you can fool it. Say you want to check to see if the value contains cbry. Use Equals and *cbry* surround your value with asterisks to get the equivalent of Contains Leading asterisk (*cbry) is the same as ends with trailing asterisk (cbry*) is the same as begins ...

Default Compose Font
Can someone tell me how to change the default font in Outlook 2003 to 10 instead of 12? Thanks! :) Tools->Options->Mail Format->Fonts. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Paul asked: | Can someone tell me how to change the default font in Outlook 2003 to | 10 instead of 12? | | Thanks! | | :) Tools > Options > Mail Format > Fonts button. (Note: I don't...

Group policy + default save location
Can anyone tell me how to modify the publisher 2007 adm template to configure users to save to their my documents rather than their C:\ drive. All help appreciated How administrators can use Office policy templates together with the Group Policy settings of Windows http://support.microsoft.com/kb/924617/en-us -- Mary Sauer http://msauer.mvps.org/ <jamessharepoint@googlemail.com> wrote in message news:6332f3c9-a56c-4636-8622-323d2427c016@s9g2000prg.googlegroups.com... > Can anyone tell me how to modify the publisher 2007 adm template to > configure users to save to their my d...

Default View in MS-Outlook
Hi all When i set an default view (for example: "All open Leads") for the Webclient, it works fine. But this change does not appear in the MS-Outlook client. I still get the "My .." views. Any ideas around? Thanks in advance :-) Stephan ...

Dlookup
Hello. I am trying to add a dlookup like I use on my forms to pull a value from an unassociated query onto my report. Is there a way I have to structure this that is different than a form? I am just looking up one field in a query called balance. The query is StatementBalance. It does not depend on anything in the report to determine the record to pull. The query has already done the work. Thanks On Thu, 6 Dec 2007 09:23:34 -0800 (PST), nybaseball22@gmail.com wrote: > Hello. I am trying to add a dlookup like I use on my forms to pull a > value from an unassociated query onto my...

Restore MOSS Default.aspx Page
I have a MOSS environment that consists of - WFE Server - DB Server There is only one farm on this installation. Recovery MOSS Server that host MOSS and SQL. This is backed up by DPM 2007. Backs work fine. When it comes to restores. The following happens... - single items (documents, files, etc.) all restore without a problem - restore default.aspx - fails with the following: The recovery jobs for SharePoint Farm Sharepoint Farm\SP-DB \SharePoint_Config_New that started at Friday, January 08, 2010 1:38:25 PM, with the destination of SP-DB.domain.com, have completed. Mos...

Text Function: Store Value instead of Reference
I apologize if this is a newbie question but I can't seem to find th answer anywhere. I am trying to clean up some data and have a column that contains 1 character ID numbers. I only need the last 5 characters so I setup another column to get the using =RIGHT(B2,5) Now that I have the last 5, I don't need the original column and wan to delete it and retain my new column. I can't though since the ne column has a reference to the original. How can I store the value returned from the RIGHT function instead o the reference? Or, is there a better way to do this such as running so...

OWA for Exchange 2003 not on default website
I've created a new website on our server that lets people (by using host headers) type webmail.xxx.com avoid the default website (which doesn't allow access from the outside world) My question is, how do I get OWA to work on this new site. I've tried redirecting and got nowhere, is there a document that explains how to this for Exchange 2003 anywhere ? Thanks S ...

Dlookup error
Hi, and thank you for any help. I put this section into the load event on a form: Private Sub Form_Load() ACT_TRVL = DLookup("[GTotal]", "qryTravel", "[SSN] = '" & [SSN] & "'") End Sub ACT_TRVL is connected to TblAttendance The problem I am having is that I get an error message (but it still works ok): "Object doesn't support this property or method" How do I get rid of the message or correct this. Also I have noticed that the field does not show up until I close the form and reopen. Also does this for some cont...

multiple value lookups
How do you do a lookup with multiple values "will" <anonymous@discussions.microsoft.com> wrote in message news:372DE651-4DB6-4823-90C8-6625C176133E@microsoft.com... > How do you do a lookup with multiple values Your question could have been clearer! If you want to do a two-dimensional lookup, this formula looks up A1(row) and B1 (column) in an array named 'Table': =INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX(Table,1,),0)) If you want to do a double-column lookup, this formula looks up D1 in column A and E1 in column B, returning the column C value corr...

How do I set default stationery for when I click 'Reply'?
I'm setting up Outlook 2003, and have worked out how to set default stationery for new messages. However, can't see how to get it to use this for Replies. HarveyL <HarveyL@discussions.microsoft.com> wrote: > I'm setting up Outlook 2003, and have worked out how to set default > stationery for new messages. However, can't see how to get it to use > this for Replies. Replies always use the format of the original message, including the stationery it used. If it ddn't include any, then the reply won't either. -- Brian Tillman ...

dlookup syntax
Is there anything wrong with my syntax? It showed me error highting that you have cancelled the previous operations... Private Sub cmdJobCompletion_Click() strPartNumber = Nz(DLookup("W_QtyPending", "WIPRawDetails", "JobNumber='" & Me.txtJobNumber & "' AND W_QtyPending<> 0 ")) Debug.Print strPartNumber If strPartNumber <> 0 Then MsgBox ("No job completion") End If End Sub -- Message posted via http://www.accessmonster.com...

Retrieving Textbox Values
Hi, I have a myriad of textboxes on my form, and from time to time, I need to retrieve some of their values in comma-separated format. The textboxes are named in the format of a letter and two digits, for easy identification, like A22 or B54 etc. Before a textbox is updated, I want a display of values in the adjoining boxes displayed to assist a user in making his decision on a new entry. This is the code that I tried on their got-focus event: 'If TypeOf Screen.ActiveControl Is TextBox Then If IsNull(Screen.ActiveControl) Then Dim X As Integer, P As Integer, ...

Assign a new the default PivotChart
Hi, I am using Office 2003. We know that the default chart type for a PivotChart report is a stacked column chart, which compares the contribution of each value to a total across categories. And whenever you filter the data in the pivotchart, the default chart type is used. Can i assign the custom chart - "Line – column on 2 Axes" as my default PivotChart ? Any comments/help appreciated. Thanks, Denny Daniel ...

Linking Values in Worksheets
Is there a simple method of linking a column of values in one worksheet to another worksheet? I can do it manually, one at a time but is there a way of copying the link down the column? (The columns are identical in length) Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1369 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1370 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1371 etc, etc Many thanks david Hi David try ='C:\Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C1369 and copy down Frank DavidM wrote: > Is there a simple method of linki...

No DLookUp
Is there a way of doing this without DLookUP or Me.TxtPath? I have the Path stored in a table which I need to do incase I have to swith paths. This way I don't have to change all of my code. Thanks DS Me.TxtPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "") If Len(Dir$(Forms!Form1!TxtPath)) > 1 Then Dim Test2SQL As String DoCmd.SetWarnings False Test2SQL = "UPDATE table1 IN '" & Forms!Form1!TxtPath & "' " & _ "SET table1.IDName = '" & Forms!Form1!TxtInfo &...

Default web browser
How do I change my default web browser I have tried going to "Set Program access and Defaults" and changing it but it keeps going back to "use current browser" which is Crome. Jeff T. wrote: > How do I change my default web browser I have tried going to "Set Program > access and Defaults" and changing it but it keeps going back to "use > current browser" which is Crome. > > http://www.google.com/support/chrome/bin/answer.py?hl=en&answer=95417 The link given in the article, takes you here. http://windows....