Problem with Dlookup, using it twice

  • Thread starter Thread starter Carl Jansson via AccessMonster.com
  • Start date Start date
C

Carl Jansson via AccessMonster.com

Background

I have one table called Components. In that table I have the following
fields (and some more not relevant):
Comp_id is Autonumber
Responsible_Dept is Number

Another table called Departments have the following fields:
Department_ID is Autonumber (and is joined to the Responsible_Dept in the
components table.
Department_email is String

I have a Form called KR which have a combobox called Component_Combo. The
combobox stores a number in a a table.

What I am trying to do is the following:
First I want to look into the table Components, based on what is in
Component_Combo, to get Responsible_Dept.

After that I want to use that result, a number which tells me what
department is responsible, to look into the table Departments to get the
Department_email.

In the end I want to use this in a SendObject to create en e-mail.

I have the forrowing code:

Dim Dept As Integer
Dim Email As String
Dept = DLookup("[Responsible_Dept]", "Components", "[Comp_id]= Forms!KR!
[Component_Combo]")

Email = DLookup("[Department_email]", "Departments", "[Department_ID] =
Dept")


When running this I get the errormessage "You cancelled the previous
operation"

If I run the code only for Dept I get a numerical value back. At least I
get the value 3,1,4 etc but when I use this number in the Email Dlookup I
get the error message. If I manually put say for example a 3, in the email
formula (Email = DLookup("[Department_email]", "Departments", "
[Department_ID] = 3") I get the correct output.

Someone with an idea of what I am doing wrong or an idea how this coulde be
done in att better way? I am a novice at VBA.

Thanks for your help
Carl
 
In your second DLookup statement, the "Dept" should be outside the quotes.
Your statement:

Email = DLookup("[Department_email]", "Departments", "[Department_ID] =
Dept")

Should be:

Email = DLookup("[Department_email]", "Departments", "[Department_ID] =" &
Dept)

Give that a try.

Danny
 
Thanks, worked like a charm even though I still don't quite understand why.
Hmmm, guess I have to read som more on the syntax in VBA.

Thanks for the green fingers
Carl
 
The 'Dept' shouldn't be inside the double quotes, because it's a variable in
your statement, and can't be read literally - ie, as "Dept" - but rather will
be read as the value it represents - in your case, 3.

Does that make sense?
 
Back
Top