?
Solved

DLookup Fails

Posted on 2007-07-31
6
Medium Priority
?
334 Views
Last Modified: 2008-02-01
Hello,

This is making me a little nuts.  To update the records available in a combobox, I have a popup form with a listbox (LENDER_ADD) of available values.  The user can doubleclick on the listbox to add the value to the underlying table and requery the combobox.  That I have no problem getting to work.  Before doing so, I run a query to check if the value has already been added.  I use a DLookup to check the query results (this is what I've been using to test):

If (DLookup("[LENDERID]", "CHECK_LENDERID", "") = Me!LENDER_ADD) Then
        MsgBox "Works!"
        Exit Sub
    Else
        MsgBox "Doesn't Work!"
        Exit Sub
End If

(LENDER_ADD's value equals LENDERID)

The problem is that it always fails, even if the value exists in the underlying table.  I get "Doesn't Work!" every time.  For instance, lets say the LENDERID (Integer) is 150 and LENDER_ADD = 150, which I verfied by returning it in a MsgBox so I knew I had the right column set in the ListBox.  Now, if I replace Me!LENDER_ADD with the number 150 it works.  That tells me it must be an issue with the ListBox not being a number, but I don't see why and haven't been able to reformat it in a way that makes it work.  And just for clarification, if I MsgBox LENDER_ADD before running the DLookup, it does return 150.

Help!
0
Comment
Question by:tommyboy115
  • 3
  • 2
6 Comments
 
LVL 85
ID: 19602686
What's the .RowSource for your listbox? If you've got multiple columns, you might need to refer to one of them explicitly:

If (DLookup("[LENDERID]", "CHECK_LENDERID", "") = Me.LENDER_ADD.Column(0)) Then
        MsgBox "Works!"
        Exit Sub
    Else
        MsgBox "Doesn't Work!"
        Exit Sub
End If

Note that Columns in a Listbox or Combo are Zero-based, so Column(0) refers to the first column, etc etc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19602692


If Not IsNull(DLookup("[LENDERID]", "CHECK_LENDERID", "[LENDERID]" = " & Me!LENDER_ADD)) Then
        MsgBox "Works!"
        Exit Sub
    Else
        MsgBox "Doesn't Work!"
        Exit Sub
End If
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 19602700
too many "


If Not IsNull(DLookup("[LENDERID]", "CHECK_LENDERID", "[LENDERID] = " & Me!LENDER_ADD)) Then
        MsgBox "Works!"
        Exit Sub
    Else
        MsgBox "Doesn't Work!"
        Exit Sub
End If
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:tommyboy115
ID: 19602766
Capricron1's solution worked, though I'd like to know what was wrong with my logic in the way I did it.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19602802
1.you did not include the criteria for the dlookup>  "[LENDERID] = " & Me!LENDER_ADD
2. you are not testing the existence of the record >  If Not IsNull(DLookup
0
 

Author Comment

by:tommyboy115
ID: 19603269
I was handling the criteria part in the query, but I wasn't doing part 2 for sure.  Thanks.  
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question