dlookup in a form to lookup a value in a query "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/19/2008 10:08:28 AM 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

RE: dlookup in a form to lookup a value in a query Cheese_whiz 11/19/2008 3:03:01 PM 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 "elliottpt via AccessMonster.com" wrote: [Quoted Text] > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 > >

RE: dlookup in a form to lookup a value in a query arrkle 11/19/2008 4:49:01 PM 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.

RE: dlookup in a form to lookup a value in a query Cheese_whiz 11/19/2008 6:46:02 PM 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 "arrkle" wrote: [Quoted Text] > > 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. > >

RE: dlookup in a form to lookup a value in a query "elliottpt via AccessMonster.com" <u47125[ at ]uwe> 11/20/2008 6:44:47 AM 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 Cheese_whiz wrote: [Quoted Text] >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. -- Message posted via http://www.accessmonster.com

RE: dlookup in a form to lookup a value in a query Cheese_whiz 11/21/2008 4:58:00 AM 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 "elliottpt via AccessMonster.com" wrote: [Quoted Text] > 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 > > > > Cheese_whiz wrote: > >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. > > -- > Message posted via http://www.accessmonster.com > >