Pull field from table with SQL, then put into variable with VBA

[ShaneJones]

I am basically trying to get the first value in the first field of a table into a variable that I can use. I have been trying to manipulate the SQL query and play with that, but have come to a brick wall. Any suggestions on how to get the SQL to work or a new directoin would be greatly appreciated.

 

[Scoty]

Shane,
The first value of the first field can be retrieved with DLookup
try:

Dim MyVariable as String

MyVariable = Cstr(DLookup("[My_Field_Name]", "MyTable"))

MyVariable = "The value of MyVariable is " & MyVariable

Msgbox MyVariable

hth
Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[ShaneJones]

Scoty
We are getting closer. When I use the Dlookup, I get a compile error saying "Invalid outside procedure". Any chance for help out of this one. I am still a newby, and am stuck again.
Shane

 

[Scoty]

Shane
on what line of code is the program bombing on?
Scoty "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[ShaneJones]

Scoty
Using this example, the code errors at My_Field_Name. Obviously I have my data in here, but that is the place.

MyVariable = Cstr(DLookup("[My_Field_Name]", "MyTable&quot)
Shane

 

[Scoty]

Shane
All I can say is make sure you have your field name in quotes and brackets. And if you are using a caption you should make sure you are using the actual field name. I have no other advise

Sorry
Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),