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 7493 articles. 0 followers. Follow

5 Replies
2784 Views

Similar Articles

[PageSpeed] 10

"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:

Cell Editing
Is there any way to make cell editing work like it does when you hit F2 by default? If you just start typing in a cell and then want to use the standard cursor controls for text (e.g., Ctrl-arrow keys to move forward or back a word, home or end to jump to beginning or end of formula or text), Excel instead intercepts these and moves the selection to another cell, forcibly stopping you from editing the cell. On the other hand, if you hit F2 first, everything works as it should. You can also hit F2 mid-edit to toggle the behavior, but if you can't remember which mode it's in, you may b...

comparing values on different sheets and deleting
Is there a way delete value on sheet2 from the values found on sheet1? For example I have a list of items on sheet1. I need to delete all values from sheet1 that match on sheet2. thank. np You might have to do this with a few steps. Add a column in list #1 such as: =VLOOKUP(A2,Sheet2!$A$1:$A$5,1,FALSE) Assuming A2 is the item you are looking for… Assuming the names that might have dups in them is A1:A5 on Sheet2 Anything *not* found will be listed as N/A which mean Not Available. Step 2, turn on Auto Filter. Data Filter Autofilter in 2003 or Home Tab Sort and Filter...

posting values
on excel,i've been using v-lookup to get values in the same row from one column but i was wondering if it was possible to use data in 2 columns and get a value in the same row to transfer into my data sheet. I looked at every function in the program but can't seem to find the way to do that. if anyone knows how to do this, pls respond to this. thanks-lisa Lisa, VLOOKUP can match a value in the first column and return the value from any other column - and the same row as the matched value - just expand the second range to include the data that you want to have returned, and use t...

Cell Values
Hi, I am using Microsoft Excel 2000 and Windows 98. I have a excel file which has two sheets in it. One is Dialy Summary & another sheet is called Monthly Summary. We keep receiving different products daily - which goes into Daily Summary sheet. I have a consolidated total of each day received product in monthly summary. For Example: imagine we have 5 products, we receive products on 1st Day of the month. Product1 > We receive 10 Nos. Product2> We receive 05 Nos. Product3> We didn't receive anything. Product4> We didn't receive anything. Product5> We receive 15 ...

Publisher 07 Default Folder
I have Publisher 2007, how do I set the startup folder to something other than "MyDocuments"? I edited the properties and set the "Start-in Directory" to the desired one, but when I launch Publisher and attempt to open a file, it goes right back to "MyDocuments" Thanks Charliec ****************************************************** Charliec Change the name of the My Documents folder Do the following in these 2007 Microsoft Office system programs: Word, Excel, PowerPoint, or Access Click the Microsoft Office Button, and then click Save As. InfoPath, Project, Pub...

setting text in 1 cell from values in other
hello, I think this is pretty easy. but cannot get it. I have 2 cells. lets say A1 and B1. in B1 I have text. I want to set the value of A1 to 100, if B1 contains the values "ABC" or "DEF". B1 can contain a lot of text. not exact values. can anyoe help me out with this? thanks. One way =IF(SUMPRODUCT(COUNTIF(B1,{"*ABC*","*DEF*"}))>0,100,"") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "bp" <x@y.com> wrote in message news:uk%238NagQGHA.5924@TK2MSFTNGP09.phx.gbl... > hello, &...

How to open new excel file in new window by default?
Hello, Could anyone help? How to open new excel file in new window by default? it opens in the last window on the screen as of now. Bests Jayesh When I open a workbook (or create a new workbook) it is opened in its own new window in addition to whatever I have open at the time. Each open workbook has its own window. You can select which workbook to work with in Excel 2007 by View/Switch Windows and selecting the workbook you want. I believe in Excel 2003, it is Window/List of Windows to do the same thing. You can also cycle through your currently open workbooks by pressing Ctrl+F6. ...

returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name down the first colunm and "time in game" across the top and the position they play in array. I then use vlookup for another spreedsheet by "position" down the first column, time across the top and puts the players name into the positions. All this works fine. Since there are 5 more kids than positions, the orginal spreedsheet has blanks when the kids are out of the game. How do I use vlookup or other to extract the 5 sub'd out kids at the bottom of the 2nd spreadsheet? It only returns the nam...

Save spreadsheet using cell values as filename
I am trying to use a command button to save a spreadsheet using cell values as the file name from the tab Master. For example I want to use cells B2 (Tony.Dungate) and F2 (2010.March) to create a file name of Tony.Dungate.2010.March.xls. Essentialy I would need something like : Master!B2&.&Master!G2&".xls" Can someone advise me on the correct code to do this. I have been trying to use the following without success: Const sRoot As String = "V:\Database Logs\" With ActiveWorkbook .SaveAs Filename:=sRoot & _ ....

Find Value; Return Row number
Goodmorning everybody! I was wondering if someone could help me with the following problem: "I wanted to make a macro which would find the first value (counting from a1) that matches the value stated in C14 of "FORM". When it gives a match I want to return the row number to "Sheet2". I tested it with dates of which I was certain it had to find a match (as I entered them myself in "Sheet1") Still it returns: "Named Argument not Found" (Runtime error 448)" I hope someon sees my mistake(s). Thanks in Advance!! Sub TestDelete() l = Sheet...

"VALUE"
My spreadsheet appears as follows: A B C D 1 "VALUE" 2 2 5 6 13 My D column asks for the sum of A+B+C... WHY DOES THE CELL RETURN A DISPLAY OF "VALUE" WHEN THE CELLS THAT ARE ADDED ARE BLANK?? Thanks, Larry Check your formula to make sure it includes the rows -- =A1+B1+B1 Also make sure there is no data in the row by using the delete key -- using the space bar to clear the cells can cause that error. >-----Original Message----- >My spreadsheet appears as follows: > > > A B C D...

Word default paper size- networked user accounts
Word 2004- processors various, OS 10.5 How do I get Word to default to A4 (instead of US letter) for networked accounts? Hi Jeremy: The succinct answer to this is "By ensuring that your operating system locality preferences are set correctly before you install." Microsoft Office picks up all its global settings from the "Language and Text" system preferences in OS 10.6 (I think they were called something else in 10.5). If this has been done wrongly at installation, you need to set the System Preferences correctly and then delete the individual user's...

count unique values with auto-filter on
Hello, I'm trying to count unique numbers in a resulting list after filtering. I have already learned how to count unique number in an unfiltered list using: =SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1)) but now I need to know how many unique numbers remain after I filter for another column. Help! Cisco Try this array formula** : =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW(A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A40)-ROW(A12)+1)>0,1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)...

Possible to select a sample rows based on a column value?
Hi I want to write a query to select columns from 3 tables (T1=120 million rows, t2=200 M and T3 = 9.5 M) grouped by a column in table T3. select count(*), T3.C1 from T1 <..> Join T2 <..> Join T3 <..> group by T3.C1 The question is it possible for me to select top 10 rows or a sample of rows from each group or rows in the above group by query. Should I try using the Over() clause with partition? Is is possible? Thanks NetNewbie (NetNewbie@discussions.microsoft.com) writes: > I want to write a query to select columns from 3 tables (T1=120 mill...

How do I add/subtract hours and minutes when some values exceed 2.
I am trying to set up a spread sheet whereby I can input a value relating to hours and minutes (this value will be over 24 hours), and then other values in hours and minutes (all under 24 hours) which I need to subtract. I'm struggling to input a 'time' value which exceeds 24 hours. Can anyone help? Hi Try formatting the cell as [hh]:mm -- Andy. "lhasalass" <lhasalass@discussions.microsoft.com> wrote in message news:D00E00AC-14A0-4F7A-A324-0BA8D790D5CB@microsoft.com... >I am trying to set up a spread sheet whereby I can input a value relating >to &...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (at least that is what I am assuming would be best choice)(Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only ...

Setting default font color
How can I set the font color. I have worksheets with the standard black font. It would be handy to change to font color so that any dew data or overwrites I enter would be in another color. Must be a way to do this, but I haven't found it. Appreciate any suggestions. Dave Sorry, forgot to mention WinXP SP2 Excel (Office)2003 Dave "Dave" <djbahb@dcwis.com> wrote in message news:uPd3KPmfJHA.4052@TK2MSFTNGP05.phx.gbl... > How can I set the font color. I have worksheets with the standard black > font. It would be handy to change to font color so that any...

Filter duplicates based on criteria / column values
I have an e-mail contact list that I need to filter. Column A = E-mail Address Column B = Company Name Alot of people from the same company may be on the list, so in Column B there will be alot of repetition while the co-responding values in Column A would be unique. Sometimes a company name will be listed but we don't have the e-mail address, so the value in Column A would be blank. Sometimes a company name will be listed with both e-mail addresses and blank values (in Column A). I'd like to do a few things: 1) Filter the list so that all dupliate company names ...

Error: "Unable to open your default e-mail folders. The microsoft exchange server is unavailable"
Hi: I have a user on our exchange 2003 sp1 system who is receiving the above error when trying to connect to his mailbox. He is able to connect in OWA. I gave myself rights on his mailbox and set up a new profile on my machine (xp, office 2003) and connected fine. I then logged in as his account on my machine, set up a profile and tried to open outlook and received the error above. Any ideas. Not seeing this exact error on the microsoft site or google groups. ...

make a field value a condition for select to populate rest of form
Given a form with 3 fields: ssn firstname lastname Currently, data source for the form is a table, 'emp'. So when we open the form we can scroll through each of the rows in emp. We need to be able to enter a value in ssn and use that value to query emp to populate the other two fields. On Dec 27, 9:06 am, EdStevens <quetico_...@yahoo.com> wrote: > Given a form with 3 fields: > > ssn > firstname > lastname > > Currently, data source for the form is a table, 'emp'. So when we > open the form we can scroll through each of the rows in emp. We n...

return value based on combo box selection
I have a combo box (cmbShipDate) in a form that is based on a table with drop down values of ShipDate1, ShipDate2, ShipDate3, etc. I have created a text box and I want the correct ship date to be displayed based on the combo box selection. These ship dates are all stored in a table called tblOrder. I thought I could use the DLookUp fuction, but I'm not doing something correctly. Thanks I don't understand what you mean by "based on a table with drop down values". And when you say the "ship dates are all stored in ... tblOrder", do you mean tha...

Problem with .Values
Hi all, I have a Sub for construct graphic but if the number of vx is too large, ERROR at .Values = Y. Why ? Thank you for your help, Serge Sub ConstruireGraphiqueParTableauxVBA() Dim X() As Double Dim Y() As Double, i As Long, vx As Double ReDim X(i), Y(i) Application.ScreenUpdating = False ici = ActiveSheet.Name '********************************************* For vx = -15 To 15 'If the number of vx is too large, ERROR 'at .Values = Y. Why ? '********************************************* X(i) = vx Y(i) = vx ^ 2 i = i + 1 ReDim Preserve X(i), Y(i) Next vx Cha...

Earned Value Determination
I have 700 lines of data imported from Project (each line has Start Date, Duration, Finish Date). I want to create a graph of % completion over time where each task gets weighted based on Duration and I give credit for task completion based on Finish Date. How would I convert the data to a format usable for graph creation? ...

Changing positive values to negative
I’m using excel 2007with windows xp pro. I use excel very infreuently and not since upgrading to 2007. I have a long column of dollar values that I need to change from positive to negative values such as: $123.45 ($123.45) I can accomplish the feat manually over many hours by F2>.HOME>- > ENTER> DOWN ARROW and repeat and repeat and repeat. Or I can use a macro. But using a macro has changed from years ago. When I tried the macro inserted a specific value of cell rather than the value of the selected cell. So that didn’t work. What can make this conversion easier? If t...

Change default font for new entries
I have a large spreadsheet that has a lot of different data (obviously). Additions need to be made. Is there any way to change the font for all new entries, besides cell by cell highlighting? Please help. Thank you. Select all the cells that will contain new data and format all at one go. Gord Dibben MS Excel MVP On Thu, 27 May 2010 13:59:37 -0700, Terry <Terry@discussions.microsoft.com> wrote: >I have a large spreadsheet that has a lot of different data (obviously). >Additions need to be made. Is there any way to change the font for all new >entries,...