Problem with DLookup()...

[Guest]

I´m trying to retreive a value by using

Dim answer As Varian
answer = DLookup("[myField]", "myTable", "[myId]= " Forms!myForm!myTextbox

in a VBA subroutine attached to a form, i.e extracting "myField" from "myTable" where "myId" corresponds to entry in "myTextbox"-textbox in form "myForm"

This syntax is in accordance with manual, under instructions/examples for function DLookup, but it will not compile because it says that the condition expression is incorrect. I'm using Access 2003 (VB 6.3) and cannot figure out what is wrong

If I hardcode the contition to for example [myId]= '15247' both compilation and run works

A'm I wrong or is there a bug in Access 2003 ? How can I get around this ?

 

[Van T. Dinh]

From your example, it appears that [myId] is a Text Field even though it
contains only digits. In this case, use:

answer = DLookup("[myField]", "myTable", "[myId] = '" & _
Forms!myForm!myTextbox & "'")

(after [myId] = : Single Quote + Double Quote

at the end: Double Quote + Single Quote + Double Quote.

--
HTH
Van T. Dinh
MVP (Access)

I´m trying to retreive a value by using:

Dim answer As Variant
answer = DLookup("[myField]", "myTable", "[myId]= " Forms!myForm!myTextbox)

in a VBA subroutine attached to a form, i.e extracting "myField" from

"myTable" where "myId" corresponds to entry in "myTextbox"-textbox in form
"myForm".

This syntax is in accordance with manual, under instructions/examples for

function DLookup, but it will not compile because it says that the condition
expression is incorrect. I'm using Access 2003 (VB 6.3) and cannot figure
out what is wrong.

If I hardcode the contition to for example [myId]= '15247' both compilation and run works.

A'm I wrong or is there a bug in Access 2003 ? How can I get around this ?

 

[Guest]

Thanks for your tip - and Yes..myId is a text field ! Unfortunatly it could not find the required answer when running the form using your suggested syntax (although I know that the answer is there). In a last frustrated effort I changed the code as follows (myTextbox is really a ComboBox)

Dim code As Varian
Dim str As Strin

Forms!myForm!myTextbox.SetFocu
str = myTextbox.Tex
code = DLookup("[myField]", "myTable", "[myId]= '" & str & "'"

.....AND IT WORKS !!!! But I can't figure out why

Again, thanks for your engagement ! It put me on the right track !

 

[Neil]

Hello,

Use .value instead of .text - you can only use .text when the control has
the focus.

DLookup("[myField]", "myTable", "[myId]= '" & Me.ComboName.Value & "'")

also, .value is the default property so

DLookup("[myField]", "myTable", "[myId]= '" & Me.ComboName & "'")

will do the trick also.

HTH,

Neil.

Thanks for your tip - and Yes..myId is a text field ! Unfortunatly it

could not find the required answer when running the form using your
suggested syntax (although I know that the answer is there). In a last
frustrated effort I changed the code as follows (myTextbox is really a
ComboBox):

Dim code As Variant
Dim str As String

Forms!myForm!myTextbox.SetFocus
str = myTextbox.Text
code = DLookup("[myField]", "myTable", "[myId]= '" & str & "'")

....AND IT WORKS !!!! But I can't figure out why ?

Again, thanks for your engagement ! It put me on the right track !

 

[Guest]

Neil, I tried your suggestions, but they don't work. It compiles correctly but the desired strings are not caught as when I revert to my "work-around" using an itermediate string picked up by

str = myTextbox.Tex

after putting focus on the textbox (or combobox). I get the feeling that something is wrong inside the Access "black box"

 

[Neil]

hmm,

very strange, just out of curiosity, what happens if you use

str = myTextbox
Debug.Print str ' Should print value of str into the immediate window
in VB (may need to goto View->Immediate Window)

Neil.

Neil, I tried your suggestions, but they don't work. It compiles correctly

but the desired strings are not caught as when I revert to my "work-around"
using an itermediate string picked up by:

str = myTextbox.Text

after putting focus on the textbox (or combobox). I get the feeling that

something is wrong inside the Access "black box".

 

[Guest]

Neil, you are right ! When I did as you suggested I discovered that the code picked up the first column (autocounter) of two bound to the combobox (the second is the important string column). Of course, your code selects the first column while my code picks up the string values that are actually populating and displayed in my combobox

This fact generates another question: when I try to populate my combobox with myString using a simple SELECT statement

SELECT myTable.myStrin
FROM myTabl
ORDER BY myTable.myString

nothing shows up in the combobox. I have to use

SELECT myTable.ID, myTable.myStrin
FROM myTabl
ORDER BY myTable.myString

and bound the control to second column. Why is that

 

[Van T. Dinh]

You probably set the column width of the first column to zero.

Check Access VB Help on ColumnWidths and ColumnCount Properties of ComboBox.

--
HTH
Van T. Dinh
MVP (Access)

Neil, you are right ! When I did as you suggested I discovered that the

code picked up the first column (autocounter) of two bound to the combobox
(the second is the important string column). Of course, your code selects
the first column while my code picks up the string values that are actually
populating and displayed in my combobox.

This fact generates another question: when I try to populate my combobox

with myString using a simple SELECT statement :

 

[Guest]

Yes - that's it ! For some reason I didn't have enough control when the combobox was originally created

Thank's guys for your help. As an old C-programmer I get almost completly lost when dealing with such inventions as VBA where you work with a lot of "black boxes" as building bricks. Being used to building the "black boxes" myself, the situation easily gets very frustrating

But every new frustration means new learnings and I can now go on with my work - will certainly come back to you when next frustration occurs

Thanks again !

 

[PC Datasheet]

Just wanted to let you know there is another source of help available if the
situation warrants it --

I am in business to provide customers with a resource for help with Microsoft
Access, Excel and Word
applications. You can view my website at www.pcdatasheet.com.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Yes - that's it ! For some reason I didn't have enough control when the

combobox was originally created.

Thank's guys for your help. As an old C-programmer I get almost completly lost

when dealing with such inventions as VBA where you work with a lot of "black
boxes" as building bricks. Being used to building the "black boxes" myself, the
situation easily gets very frustrating.

But every new frustration means new learnings and I can now go on with my

work - will certainly come back to you when next frustration occurs.