dlookup in a form to lookup a value in a query

[elliottpt via AccessMonster.com]

Hi

I am having a bit of trouble with a dlookup not functioning properly.

I have a continious form based on a table, tblTFCMASTER, in which I need ot
lookup a value from a query,qryMAXSAILDATANDSAILWEEK. Table TFC master
contains amongst others a text field called SHIPNO, which I want to use to
search for the MAXWEEKNO in the query. The query qryMAXSAILDATANDSAILWEEK is
based on another qry in order to calulate the maximum weekno. The query
contains the following fields(amongst others):SHIPNO, MAXWEEKNO

I have written the following dlookup and placed it in the on load event of
the form to search for the maximum week no and display it in the unbound
field WEEKNO in the form.

Me.WEEKNO = DLookup("[MAXWEEKNO]", "qryMAXSAILDATANDSAILWEEK", "[SHIPNO] ='"
& Me.SHIPNO & "'")

It however only finds(correctly I might add) the first record's MAXWEEKNO and
displays it correctly too, but the problem is it also displays the first
record's MAXWEEKNO now for all the other records too.

It puzzles me as I understand the Me. to use the current records information;
ie the current records SHIPNO as the comparison.

Help will be much appreciated.

Kind regards
Philip

 

[Cheese_whiz]

Hi elliot,

I think you want to use that dlookup in the control source of the control on
your form and get rid of that form load event you are using.

CW

 

[arrkle]

Phillip,

As you have noticed, DLookup does, in fact, NOT look beyond the first record
that matches the specified criteria.

Since you are looking for a maximum value, you might have better luck with
DMax -- with (or without) criteria, whichever is appropriate.

 

[Cheese_whiz]

The problem is he put the code in the 'load' event of the form, so the
calculation only runs once.

If he uses dlookup in the control source as I suggested, he will get the
values he needs. He could also do it in the query as a calculated field.

He really isn't looking for a 'maximum' value, at least not with this
particular calculation. The maximum value was calculated already and is part
of each record in the form's record source. At least that's the way I read
it.

CW

 

[elliottpt via AccessMonster.com]

Splendid Cheesewiz...

Thanks so much, works on the control source.

To clarify the calculation took place in the query and I just need to draw
the info from there.

Could you perhaps clarify why the me. doesn't work>isn't it supposed to use
each records value; ie the shipno if Me.SHIPNO

The problem is he put the code in the 'load' event of the form, so the
calculation only runs once.

If he uses dlookup in the control source as I suggested, he will get the
values he needs. He could also do it in the query as a calculated field.

He really isn't looking for a 'maximum' value, at least not with this
particular calculation. The maximum value was calculated already and is part
of each record in the form's record source. At least that's the way I read
it.

CW

Phillip,

[quoted text clipped - 3 lines]

Since you are looking for a maximum value, you might have better luck with
DMax -- with (or without) criteria, whichever is appropriate.

 

[Cheese_whiz]

Hi again,

Glad I was able to help some. Sorry I didn't get back to this latest post
sooner, but it's been one of those days...

'Me' is simply a shorthand way of referring to the current form in this
case. When you are writing code in a form's module, and you need to refer to
something in the form, like a control or a property or method, you can use
syntax like this: Forms!FormName!ControlName, or you can use the shorthand
method: Me!ControlName or Me.PropertyName.

That's all 'me' means.

CW

Splendid Cheesewiz...

Thanks so much, works on the control source.

To clarify the calculation took place in the query and I just need to draw
the info from there.

Could you perhaps clarify why the me. doesn't work>isn't it supposed to use
each records value; ie the shipno if Me.SHIPNO

The problem is he put the code in the 'load' event of the form, so the
calculation only runs once.

If he uses dlookup in the control source as I suggested, he will get the
values he needs. He could also do it in the query as a calculated field.

He really isn't looking for a 'maximum' value, at least not with this
particular calculation. The maximum value was calculated already and is part
of each record in the form's record source. At least that's the way I read
it.

CW

Phillip,

[quoted text clipped - 3 lines]

Since you are looking for a maximum value, you might have better luck with
DMax -- with (or without) criteria, whichever is appropriate.