Automatic Dlookup update

[J Sedoff]

I have a form using DLookup which should update after a user inputs data, but
currently sticks on whatever the previous Dlookup result from the previous
input was.

The user types in the date into the [Enter Date] form's [Text0] textbox
(which is formatted to "mm/dd/yyyy") and in textbox [Text4], I have the
ControlSource set to =DLookUp("Week","tbl2004CloseSchd","Date = [Text0]").
In the table [tbl2004CloseSchd], I have a fiscal week breakdown based on the
date (field name "Date" in a Date/Time format) and would like to retrieve the
week that contains the user's input date to use in several week-to-date
reports.

It works great when I open it initially, but once a user types in a new
date, nothing happens in the way of updating to reflect the correct week for
the new date.

Any suggestions/help would be incredibly appreciated! Thanks, Jim

 

[Klatuu]

1. The reference to the ambiguously named text box needs to be outside the
quotes.

2. Using Date as a field name is a bad practice because it is a reserved
word and since it is an intrinsic function will almost certainly cause
problems. The best solution would be to change the name of the field, but at
least enclose it in brackets.

3. The correct syntax for data data types is to enclose the value to be
searched in #.
4. Try this:

=DLookUp("[Week]","tbl2004CloseSchd","[Date] = #" & Me.Text0 & "#")

 

[J Sedoff]

1. Don't you love ambiguity? I'm new to Access. .. and lazy/uncreative (??
not really, I just have been more worried about getting things to work than
make good names.)

2. Would changing the field name (I'm trying to improve a pre-existing
database that I did not make) mess up the database?

3. I thought I had seen that somewhere else. Does that force Access to view
it as a Date, much like the "&" symbol concatenates and forces the String
data type?

4. Works great, except that Access keeps thinking that "Me" is an object,
but removing it and just using [Text0] works fine.

Thanks for the help!
Jim

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

1. Don't you love ambiguity? I'm new to Access. .. and lazy/uncreative (??
not really, I just have been more worried about getting things to work than
make good names.)

Understandable, but using relavent names will help you keep track of what
each object means. Just trying to help you for the long run.

2. Would changing the field name (I'm trying to improve a pre-existing
database that I did not make) mess up the database?

You would have to change it every where it is used or yes, it would cause
problems.

3. I thought I had seen that somewhere else. Does that force Access to view
it as a Date, much like the "&" symbol concatenates and forces the String
data type?

Yes, when using any Domain Aggreage function and in SQL, you have to use the
correct delimiters depending on the data type of the field you are searching

For text, it can be either single or double qoutes. It is best to always
use double qoutes, because you can get into a problem with names like O'Tool
or O'Reilly, or any field that has an apostrophe (single qoute)
For dates, the # sign
For all numerics, no delimiters.

4. Works great, except that Access keeps thinking that "Me" is an object,
but removing it and just using [Text0] works fine.

Me is an ojbect. It is a reference to the form object the code is in. Not
only should it work, it is a good practice to qualify your objects.

 

[boblarson]

Dave:

Me is a VB reference to the current class object. As far as I know it will
not work in control sources or function calls within properties and his

=DLookup ...etc.

looks like a control source item to me.

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

--
Dave Hargis, Microsoft Access MVP

1. Don't you love ambiguity? I'm new to Access. .. and lazy/uncreative (??
not really, I just have been more worried about getting things to work than
make good names.)

Understandable, but using relavent names will help you keep track of what
each object means. Just trying to help you for the long run.

2. Would changing the field name (I'm trying to improve a pre-existing
database that I did not make) mess up the database?

You would have to change it every where it is used or yes, it would cause
problems.

3. I thought I had seen that somewhere else. Does that force Access to view
it as a Date, much like the "&" symbol concatenates and forces the String
data type?

Yes, when using any Domain Aggreage function and in SQL, you have to use the
correct delimiters depending on the data type of the field you are searching

For text, it can be either single or double qoutes. It is best to always
use double qoutes, because you can get into a problem with names like O'Tool
or O'Reilly, or any field that has an apostrophe (single qoute)
For dates, the # sign
For all numerics, no delimiters.

4. Works great, except that Access keeps thinking that "Me" is an object,
but removing it and just using [Text0] works fine.

Me is an ojbect. It is a reference to the form object the code is in. Not
only should it work, it is a good practice to qualify your objects.

Thanks for the help!
Jim

 

[Klatuu]

Right, Bob. I know better, I just forget he had originally said it was a
control source.

--
Dave Hargis, Microsoft Access MVP

Dave:

Me is a VB reference to the current class object. As far as I know it will
not work in control sources or function calls within properties and his

=DLookup ...etc.

looks like a control source item to me.

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

--
Dave Hargis, Microsoft Access MVP

1. Don't you love ambiguity? I'm new to Access. .. and lazy/uncreative (??
not really, I just have been more worried about getting things to work than
make good names.)

Understandable, but using relavent names will help you keep track of what
each object means. Just trying to help you for the long run.

2. Would changing the field name (I'm trying to improve a pre-existing
database that I did not make) mess up the database?

You would have to change it every where it is used or yes, it would cause
problems.

3. I thought I had seen that somewhere else. Does that force Access to view
it as a Date, much like the "&" symbol concatenates and forces the String
data type?

Yes, when using any Domain Aggreage function and in SQL, you have to use the
correct delimiters depending on the data type of the field you are searching

For text, it can be either single or double qoutes. It is best to always
use double qoutes, because you can get into a problem with names like O'Tool
or O'Reilly, or any field that has an apostrophe (single qoute)
For dates, the # sign
For all numerics, no delimiters.

4. Works great, except that Access keeps thinking that "Me" is an object,
but removing it and just using [Text0] works fine.

Me is an ojbect. It is a reference to the form object the code is in. Not
only should it work, it is a good practice to qualify your objects.

Thanks for the help!
Jim