DLOOKUP coding issue

[Sean Timmons]

OK, so I clearly don't have the capacity to determine why my code isn't
working. Could someone set me straight?

My code:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = " & _
"Me.Rep_Name")
Me.Rep_ID = VarX
End Sub

Gets an error:

Run Time error 2001
You cancelled the previous operation.

What am I spazzing on?

 

[Tom Lake]

OK, so I clearly don't have the capacity to determine why my code isn't
working. Could someone set me straight?

My code:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = " & _
"Me.Rep_Name")
Me.Rep_ID = VarX
End Sub

Try this:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = '" & _
Me.Rep_Name & "'")
Me.Rep_ID = VarX
End Sub


Tom Lake

 

[Sean Timmons]

Part of me hoped it was that easy, but my ego would have been crushed.

Still getting the same error message as below.

Not sure if it's relevant, but the row source for my rep name field is
queried based on a supervisor name selection.

 

[fredg]

OK, so I clearly don't have the capacity to determine why my code isn't
working. Could someone set me straight?

My code:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = " & _
"Me.Rep_Name")
Me.Rep_ID = VarX
End Sub

Gets an error:

Run Time error 2001
You cancelled the previous operation.

What am I spazzing on?

The control's Change event fires as you enter each character, so this
code will repeat as you enter each letter.
You should use the control's AfterUpdate event.
In addition, try this change in code.

What is the Rep_ID field's datatype?
what is the Representative field's datatype?
If both are Text, then use:

Private Sub Rep_Name_AfterUpdate()
Me.Rep_ID = DLookup("[ACSS_ID]", "[Hierarchy_Table]", "Representative
= """ & Me.[Rep_Name] & """")

End Sub

The above assumes the "Rep_ID", "Acss_ID", "Hierarchy_Table" and
"Rep_Name" object names include the underscore as shown above.

If the object names include a space instead of the underscore, i.e.
Rep ID , then you need to enclose the object name within brackets, and
replace the underscore with the space, i.e. [Rep ID], [Acss ID], etc.

If [Rep_ID] and Representative are not text datatype, then you'll have
to post back with more information as to the the datatypes involved
and an example of the data.

 

[Klatuu]

Error 2001 means Jet could not complete the request. That ususally means
there is some name that is not correct in the DLookup. Make sure the table
and field names are correct and that the criteria argument syntax is correct
for the field's data type.

As written, you are expecting Representative to be a numeric field, which I
doubt.
Also, the reference to the form control Me.Rep_ID needs to be outside the
quotes. As written, you are looking for a match in the numeric field
Representative field to be a literal text value of "Me.Rep_Name"

Also, you should be using the After Update event. the Change event fires
for every keystroke. I doubt that is what you want.

This would work just as well:

Private Sub Rep_Name_AfterUpate()

Me.Rep_ID = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = """
& _
Me.Rep_Name & """"")
End Sub


Other than that, I don't see a problem <g>

 

[John W. Vinson]

OK, so I clearly don't have the capacity to determine why my code isn't
working. Could someone set me straight?

My code:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = " & _
"Me.Rep_Name")
Me.Rep_ID = VarX
End Sub

Gets an error:

Run Time error 2001
You cancelled the previous operation.

What am I spazzing on?

Just in addition to the caveats from the other responders... is Representative
a Lookup field? If so, it actually contains a concealed numeric ID, and
searching for a text Rep_Name in it will fail.

 

[Sean Timmons]

OMG, it was the underscore...

[ACSS ID] and [Rep ID] got that piece

and After Update appears to be better for me.

this worked! Thank you much!!!!

OK, so I clearly don't have the capacity to determine why my code isn't
working. Could someone set me straight?

My code:

Private Sub Rep_Name_Change()
Dim VarX As Variant
VarX = DLookup("ACSS_ID", "Hierarchy_Table", "Representative = " & _
"Me.Rep_Name")
Me.Rep_ID = VarX
End Sub

Gets an error:

Run Time error 2001
You cancelled the previous operation.

What am I spazzing on?

The control's Change event fires as you enter each character, so this
code will repeat as you enter each letter.
You should use the control's AfterUpdate event.
In addition, try this change in code.

What is the Rep_ID field's datatype?
what is the Representative field's datatype?
If both are Text, then use:

Private Sub Rep_Name_AfterUpdate()
Me.Rep_ID = DLookup("[ACSS_ID]", "[Hierarchy_Table]", "Representative
= """ & Me.[Rep_Name] & """")

End Sub

The above assumes the "Rep_ID", "Acss_ID", "Hierarchy_Table" and
"Rep_Name" object names include the underscore as shown above.

If the object names include a space instead of the underscore, i.e.
Rep ID , then you need to enclose the object name within brackets, and
replace the underscore with the space, i.e. [Rep ID], [Acss ID], etc.

If [Rep_ID] and Representative are not text datatype, then you'll have
to post back with more information as to the the datatypes involved
and an example of the data.

 

[Sean Timmons]

Thank you for the detailed explanation. This helps me understand the why
part. I was just cribbing off Access Help files to get what I had in the
first place.

 

[Sean Timmons]

And yeah, 4 lines of code and about 6 errors, so that was pretty minimum <g>

 

[Sean Timmons]

Thankfully not this time. But, I do have several other tables with lookups,
so I'm sure this will come up eventually. How would I work around referencing
a lookup field within a DLOOKUP?

 

[Klatuu]

Don't use Lookup fields.
They are evil little demons to be avoided.

 

[John W. Vinson]

Thankfully not this time. But, I do have several other tables with lookups,
so I'm sure this will come up eventually.

Get rid of them! See
http://www.mvps.org/access/lookupfields.htm
for a critique of this misfeature. They are NEVER necessary (you can easily
create a Combo Box on a Form, without needing to have a lookup field in a
table).

How would I work around referencing
a lookup field within a DLOOKUP?

If you really need DLookUp (it can be really inefficient!) you can use a
reference to the actual numeric ID; this could be from (say) a combo box
control on an unbound form, which displays the human-readable name but
actually returns the numeric value. If it's just a matter of displaying the
name, there are other ways - a dlookup is probably NOT the best.

 

[Sean Timmons]

Yet another handy tip! Time to do some overhauling!

Evil little demons tells me all I need to know...

 

[Mike Painter]

Make sure you have the quoting right.
That's single quote, double quote before the first "&" and double quote,
single quote, double quote at the end.

Why are you looking this up rather than using a related table? There could
be more than one person with the same Rep_name in most cases.