473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

DLookup function with no action or errors

I have a database to manage a company with 4 divisions. A table [employee] has a related form with the employees title. In order to break down employees by division, I created a table [titleconversion] where fields have the [Title] and [Division] and [EmploymentStatus]

In the on the form [employees]![Title] i am using an afterupdate DLookup code to place [Division] and [EmploymentStatus] based on the entry. My problem is NOTHING is happening. No errors, debugging issues, or results populating the fields. I have used DLookup many times but it seems I am missing something. I have used the code both ways listed below with same non-result. Any ideas would be appreciated.

Expand|Select|Wrap|Line Numbers
  1. division = DLookup("[Division]", "[titleconversion]", "Id= " & Forms!employee!Title)
  2. EmployeeStatus = DLookup("[EmploymentStatus]", "[titleconverson]", "Id=" & Forms!employee!Title) 
  3.  
  4. division = DLookup("Division", "titleconversion", "Id= " & Forms!employee!Title)
  5. EmployeeStatus = DLookup("EmploymentStatus", "titleconverson", "Id=" & Forms!employee!Title)
Jun 20 '13 #1
1 1051
zmbd
5,501 Expert Mod 4TB
You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

So to use part of your code:
Expand|Select|Wrap|Line Numbers
  1. DIM strSQL as string
  2. strSQL = "Id= '" & Forms!employee!Title & "'"
  3. '>>Note that I’ve already made a change here as I suspect that the field is a text value
  4. '>>text values need to have a single quote around the string.
  5. '>> Also, if field/control [Title] is local to the this form then one can use Me!Title instead of the forms method
  6. '
  7. 'now you can insert a debug print here for troubleshooting
  8. ' - press <ctrl><g> to open the immediate window
  9. ' - you can now cut and paste this information for review!
  10. '
  11. debug.print "Your criteria = " & strSQL
  12. '
  13. 'now use the string in your code:
  14. division = DLookup("[Division]", "[titleconversion]", strSQL) 
If you will make these little changes to all of your code and post back the resolved strings we can help you tweak the code.
Jun 20 '13 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Ronny Sigo | last post by:
Hello all, When I use the Dlookup function I get the errormsg "Runtime error 2001 - You canceled the previous operation" ... Can anybody tell me what I do wrong here ? Here is the snippet ... : ...
1
by: John Hargrove | last post by:
I am building a database to manage test samples in an environmental laboratory. I am learning Access as I go and don't know much about the programming aspects. I hope to make the application...
2
by: Ronny Sigo | last post by:
Hello all, I already put the same question, only now I have more to tell ... Although I used this code before in the same routine (only the fieldname of the table differs) ___ at this point in the...
2
by: jonvan20 | last post by:
I have been having trouble with a simple Dlookup command that was Reccommended to me by a nice fellow named Vic, On the other hand I have statements like this that wont run they give me a run time...
3
by: sbetancourt | last post by:
Hi, I am making a database that will look up an employee in the main form and in the subform I can see their tasks. The subform is in a datasheet view and it has a combo box that allows you to...
0
by: musman | last post by:
hey all, I have tried to use the select statement instead DLookUp function as i have sql server at my backend and access as my front end. But neither DLookUp function is working nor select state...
3
by: Birky | last post by:
Can someone please help me with my syntax below? I am trying to enable an object on a form for specific users only and in doing so I am trying to use the DLookUp function to try and keep it sweet and...
2
by: Lumpy | last post by:
Hi all, I am having a problem with a line of code in access vba. I am trying to use the DLookup function to check whether or not a record already exists with a dealer name. Once I know if it...
7
by: John | last post by:
Hi Is there a dlookup function in vb.net like in access which returns a column value from a table for a given criteria? If not, is there a sample I can look at? Thanks Regards
5
by: nandithadevaraj | last post by:
Hi, I am working on oracle, frankly speaking till nw i have nt worked in oracle i need help please help me out in this actually i have written query using DLOOKUP function as DLOOKUP...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.