DLOOKUP Issue

  • Thread starter Thread starter Scottie
  • Start date Start date
S

Scottie

I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch the
text box that contains it......anyway to make sure the vales shows when the
form opens?
 
Scottie,
Try a Refresh in the OnCurrent event for the form.
As you open, or browse from record to record, the Refresh should
re-calculate the Room# for that specific RID.

Also, you may want to avoid the "#" in control names. Since that
character is associated with dates, you'll always have to bracket the
name in code to prevent problems.
That's not a rule cast in stone... just a suggestion. (Ex. RoomNo)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Yes this is the control source, it is on the detail section and is single
form hope that helps :)

Ken Snell said:
Is this expression the Control Source for a textbox on the form? Is the form
a Continuous Forms view or a Single Form view? In which section of the form
is the textbox located?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Scottie said:
I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
 
I tried using a On Current event using Me.Requery....changes nothing....any
other ideas?

Al Campagna said:
Scottie,
Try a Refresh in the OnCurrent event for the form.
As you open, or browse from record to record, the Refresh should
re-calculate the Room# for that specific RID.

Also, you may want to avoid the "#" in control names. Since that
character is associated with dates, you'll always have to bracket the
name in code to prevent problems.
That's not a rule cast in stone... just a suggestion. (Ex. RoomNo)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Scottie said:
I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
 
Change the expression to this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " & [RID])

Assuming, that is, that the textbox is located on the res_residents form. If
res_residents is a different form, then try this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " &
[Forms]![res_residents]![RID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Scottie said:
Yes this is the control source, it is on the detail section and is single
form hope that helps :)

Ken Snell said:
Is this expression the Control Source for a textbox on the form? Is the
form
a Continuous Forms view or a Single Form view? In which section of the
form
is the textbox located?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Scottie said:
I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch
the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Scottie,
Not sure if a Requery is necessary, or has any effect, on an unbound
calculated control.
Did you try just a Refresh, as I suggested?
Can't hurt to try, and it only takes a minute....
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Scottie said:
I tried using a On Current event using Me.Requery....changes nothing....any
other ideas?

Al Campagna said:
Scottie,
Try a Refresh in the OnCurrent event for the form.
As you open, or browse from record to record, the Refresh should
re-calculate the Room# for that specific RID.

Also, you may want to avoid the "#" in control names. Since that
character is associated with dates, you'll always have to bracket the
name in code to prevent problems.
That's not a rule cast in stone... just a suggestion. (Ex. RoomNo)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Scottie said:
I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch
the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Al,

Forgive my ignorance here but I guess I thought I was doing what you
instructed :)) Perhaps I don't understand the difference between refresh and
requery and how to accomplish it. I tried the refresh button...that didn't
help the only other thing I know how to do is run a requery via my main
macros...could you drop me a bit of code that shows how to refresh?

Al Campagna said:
Scottie,
Not sure if a Requery is necessary, or has any effect, on an unbound
calculated control.
Did you try just a Refresh, as I suggested?
Can't hurt to try, and it only takes a minute....
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Scottie said:
I tried using a On Current event using Me.Requery....changes nothing....any
other ideas?

Al Campagna said:
Scottie,
Try a Refresh in the OnCurrent event for the form.
As you open, or browse from record to record, the Refresh should
re-calculate the Room# for that specific RID.

Also, you may want to avoid the "#" in control names. Since that
character is associated with dates, you'll always have to bracket the
name in code to prevent problems.
That's not a rule cast in stone... just a suggestion. (Ex. RoomNo)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch
the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Ken this works perfectly, and yes the control was on the res_residents
form.....one thing though....what is the difference betwwen your formula and
mine?

Ken Snell said:
Change the expression to this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " & [RID])

Assuming, that is, that the textbox is located on the res_residents form. If
res_residents is a different form, then try this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " &
[Forms]![res_residents]![RID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Scottie said:
Yes this is the control source, it is on the detail section and is single
form hope that helps :)

Ken Snell said:
Is this expression the Control Source for a textbox on the form? Is the
form
a Continuous Forms view or a Single Form view? In which section of the
form
is the textbox located?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I touch
the
text box that contains it......anyway to make sure the vales shows when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Your expression told ACCESS to go find the form and then read a value from
that control (use of Forms!Formname! syntax). But the form was not done
opening yet when the DLookup function was run by ACCESS, so no value could
be found.

My expression tells ACCESS that you want to use a control on the same form
(no use of Forms!Formname! syntax), so ACCESS waits for the form to finish
opening and loading before it runs the DLookup function.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Scottie said:
Ken this works perfectly, and yes the control was on the res_residents
form.....one thing though....what is the difference betwwen your formula
and
mine?

Ken Snell said:
Change the expression to this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " & [RID])

Assuming, that is, that the textbox is located on the res_residents form.
If
res_residents is a different form, then try this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " &
[Forms]![res_residents]![RID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Scottie said:
Yes this is the control source, it is on the detail section and is
single
form hope that helps :)

:

Is this expression the Control Source for a textbox on the form? Is
the
form
a Continuous Forms view or a Single Form view? In which section of the
form
is the textbox located?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I
touch
the
text box that contains it......anyway to make sure the vales shows
when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Nice! Thanks a lot ...once again Ken :) I applied this principle to all of
the lookups and they are working great......cheers

Ken Snell said:
Your expression told ACCESS to go find the form and then read a value from
that control (use of Forms!Formname! syntax). But the form was not done
opening yet when the DLookup function was run by ACCESS, so no value could
be found.

My expression tells ACCESS that you want to use a control on the same form
(no use of Forms!Formname! syntax), so ACCESS waits for the form to finish
opening and loading before it runs the DLookup function.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Scottie said:
Ken this works perfectly, and yes the control was on the res_residents
form.....one thing though....what is the difference betwwen your formula
and
mine?

Ken Snell said:
Change the expression to this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " & [RID])

Assuming, that is, that the textbox is located on the res_residents form.
If
res_residents is a different form, then try this:

=DLookUp("[Room#]","res_source_step 2","[RID] = " &
[Forms]![res_residents]![RID])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Yes this is the control source, it is on the detail section and is
single
form hope that helps :)

:

Is this expression the Control Source for a textbox on the form? Is
the
form
a Continuous Forms view or a Single Form view? In which section of the
form
is the textbox located?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I
touch
the
text box that contains it......anyway to make sure the vales shows
when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Looks like your all set with Ken Snell's suggestion.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Scottie said:
Al,

Forgive my ignorance here but I guess I thought I was doing what you
instructed :)) Perhaps I don't understand the difference between refresh
and
requery and how to accomplish it. I tried the refresh button...that
didn't
help the only other thing I know how to do is run a requery via my main
macros...could you drop me a bit of code that shows how to refresh?

Al Campagna said:
Scottie,
Not sure if a Requery is necessary, or has any effect, on an unbound
calculated control.
Did you try just a Refresh, as I suggested?
Can't hurt to try, and it only takes a minute....
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Scottie said:
I tried using a On Current event using Me.Requery....changes
nothing....any
other ideas?

:

Scottie,
Try a Refresh in the OnCurrent event for the form.
As you open, or browse from record to record, the Refresh should
re-calculate the Room# for that specific RID.

Also, you may want to avoid the "#" in control names. Since that
character is associated with dates, you'll always have to bracket the
name in code to prevent problems.
That's not a rule cast in stone... just a suggestion. (Ex.
RoomNo)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I am using the following lookup on a form

=DLookUp("[Room#]","res_source_step 2","[RID] =
[Forms]![res_residents]![RID]")

the lookup works great except it won't display the value till I
touch
the
text box that contains it......anyway to make sure the vales shows
when
the
form opens?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top