DLookup Syntex error it is killing me

[ardy]

I am trying to use DLookup to enter a value from a look-up table in my
form. it is keep giving me syntax error and I have tried all possible
options I can think of. Can some body tell me if what I using is
correct and if not what i need to add to it........

Private Sub Well_Lon_Click()

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]=" & [Forms]!
[Customer_Call]![Tbl_Well_ID])

End Sub

Thanks

 

[Ken Snell]

Is Well_NBR a text field? If yes, delimit the filter parameter with '
characters:

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]='" & [Forms]!
[Customer_Call]![Tbl_Well_ID] & "'")

 

[John W. Vinson]

I am trying to use DLookup to enter a value from a look-up table in my
form. it is keep giving me syntax error and I have tried all possible
options I can think of. Can some body tell me if what I using is
correct and if not what i need to add to it........

Private Sub Well_Lon_Click()

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]=" & [Forms]!
[Customer_Call]![Tbl_Well_ID])

End Sub

Thanks

Three questions:

1. Is Well_NBR a Text field? If so you need the syntactically required
quotemarks:

LonLat = DLookup("[Lon]", "Program_Wells", _
"[Well_NBR]='" & [Forms]![Customer_Call]![Tbl_Well_ID] & "'")

2. If that's not the issue, is the form Customer_Call open, and does the
control named Tbl_Well_ID have a value at the time?

3. What is LonLat? Is it the name of a control on this form? It appears to be
an undeclared VBA variable. If it's a control try

Me!LonLat = DLookUp...


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[ardy]

Is Well_NBR a text field? If yes, delimit the filter parameter with '
characters:

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]='" & [Forms]!
[Customer_Call]![Tbl_Well_ID] & "'")

--

        Ken Snellhttp://www.accessmvp.com/KDSnell/

I am trying to use DLookup to enter a value from a look-up table in my
form.  it is keep giving me syntax error and I have tried all possible
options I can think of.  Can some body tell me if what I using is
correct and if not what i need to add to it........

Private Sub Well_Lon_Click()

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]=" & [Forms]!
[Customer_Call]![Tbl_Well_ID])

End Sub

Thanks

Yes it is text.......
Perfect ,
I get the value I am after....I can show it in a message box......
But for life of me I can't figure out how to input the value in the
textbox in my form. this is the same text box that has this code (On
Click Event). So the user clicks the box he/she gets the value which
you helped me with then it gets inputted to the same box which in turn
is connected to the Control Source(Cus_Well_Lon). The name of the box
in the form is Well_Lon......

I really appreciate it if you could help me on this......I am not very
strong in VB coding. Forgive me if I am not making any seance....

Ardy

 

[ardy]

Is Well_NBR a text field? If yes, delimit the filter parameter with '
characters:

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]='" & [Forms]!
[Customer_Call]![Tbl_Well_ID] & "'")

--

        Ken Snellhttp://www.accessmvp.com/KDSnell/

news:b319baa7-6a15-45f4-8607-7538a66e3639@q25g2000vbx.googlegroups.com....

I am trying to use DLookup to enter a value from a look-up table in my
form.  it is keep giving me syntax error and I have tried all possible
options I can think of.  Can some body tell me if what I using is
correct and if not what i need to add to it........
Private Sub Well_Lon_Click()
LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]=" & [Forms]!
[Customer_Call]![Tbl_Well_ID])
End Sub
Thanks

Yes it is text.......
Perfect ,
I get the value I am after....I can show it in a message box......
But for life of me I can't figure out how to input the value in the
textbox in my form.  this is the same text box that has this code (On
Click Event).  So the user clicks the box he/she gets the value which
you helped me with then it gets inputted to the same box which in turn
is connected to the Control Source(Cus_Well_Lon). The name of the box
in the form is Well_Lon......

I really appreciate it if you could help me on this......I am not very
strong in VB coding.  Forgive me if I am not making any seance....

Ardy

Would this work
=========================Code
Private Sub Well_Lon_Click()

Dim LonLat As String

LonLat = DLookup("[Lon]", "Program_Wells", "[Well_NBR]='" & [Forms]!
[Customer_Call]![Tbl_Well_ID] & "'")
MsgBox LonLat
Well_Lon.Value = LonLat

End Sub
===========================End

I need to take the message box out .........

Ardy

 

[John W. Vinson]

But for life of me I can't figure out how to input the value in the
textbox in my form.

Why on Earth would you WANT to?

This would be storing the value redundantly. If it can be looked up from the
Wells table, do so! Storing the field redundantly in a second table is just
asking for trouble, since it could in principle get edited in either or both
locations; you would then have two different values for Lon with no way to
tell which one is right.

Context please?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[ardy]

Why on Earth would you WANT to?

This would be storing the value redundantly. If it can be looked up from the
Wells table, do so! Storing the field redundantly in a second table is just
asking for trouble, since it could in principle get edited in either or both
locations; you would then have two different values for Lon with no way to
tell which one is right.

Context please?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
John:

Why on Earth would you WANT to?

ommmmm,...............
your point is very well taken, and for most parts I agree with you.
but my reality limits my options due to not having the authority in my
job to tell people above me what they should have. You need to
realize that I just dig the hole. for some peculiar reason they want
to have it recorded with the case. I thought abt the point you made
earlier of it having the chance of being different that what the look-
up table has. My approach was to make the fields in the form read
only so the user can't edit, this way what DLookup reads is what they
record. is there a chance of the Look-up table getting updated with
new lon/lat for each point. very rare or near to none. due to the
fact that most of the points have been GPSed. now if it dose than I
need to make some correction on the back-end. The table is not
reachable from user manipulation all actions are done through forms
and the menus have been disabled. It is probably not the best
approach, but this is the best I can do with limited time and
knowledge I have.

I still do appreciate your thoughts(it keeps me out of trouble) and am
thankful to this community, I have always gotten good feedback from
this community.

Regards
Ardy

 

[John W. Vinson]

for some peculiar reason they want
to have it recorded with the case.

It IS recorded with the case. It's in a related table, and can be connected
with the other data for the case whenever it's needed.

The user need not see the Table, or even know how the tables are structured or
connected. That the user should see is Forms and Reports, based on queries,
pulling together the data in disparate tables.

Perhaps there is a good reason to store this field redundantly but I'm sure
not seeing it!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com