Help with If statement - microsoft.public.access.formscoding

I have a form where I want to populate a control [txtbusinessnamea] based on the content of either of two other controls [txtmemnbr] and [txtempmemnumbera]. If neither of the latter controls have data, then I want the first control to be blank. So a series of lookup statments may look like this; IF (IsNotNull([txtmemnbr]) and IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," ") Else IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) ,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera]")," ") Else IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then txtbusinessnamea is blank I realise I haven't got the syntax correct but this is just to illustrate what I'm trying to do. Could someone start me off with the VBA code, or an IIF statement to produce the result I want please, but care because I am a novice<g> Many thanks Tony

Tony When I run into functions I don't exactly recall, Access HELP provides some fine examples. Have you looked into the If...Then and the DLookup() functions for exact syntax? Regards Jeff Boyce Microsoft Office/Access MVP "Tony Williams" <tw@invalid.com> wrote in message news:%23UiH9%23nYIHA.5984@TK2MSFTNGP06.phx.gbl... >I have a form where I want to populate a control [txtbusinessnamea] based >on the content of either of two other controls [txtmemnbr] and >[txtempmemnumbera]. If neither of the latter controls have data, then I >want the first control to be blank. So a series of lookup statments may >look like this; > > IF (IsNotNull([txtmemnbr]) and > IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," > ") Else > > IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) > ,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera]")," > ") Else > > IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then > txtbusinessnamea is blank > > I realise I haven't got the syntax correct but this is just to illustrate > what I'm trying to do. > > Could someone start me off with the VBA code, or an IIF statement to > produce the result I want please, but care because I am a novice<g> > > Many thanks > Tony > > > > >

Hi Jeff. I think I understand the syntax for a standard IIF statement. What I'm stuck with here is I've got 3 IIF statements that control the result and its how do I manage all three at the same time. Thanks Tony "Jeff Boyce" <nonsense@nonsense.com> wrote in message news:%2391kPkoYIHA.6044@TK2MSFTNGP05.phx.gbl... > Tony > > When I run into functions I don't exactly recall, Access HELP provides > some fine examples. Have you looked into the If...Then and the DLookup() > functions for exact syntax? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "Tony Williams" <tw@invalid.com> wrote in message > news:%23UiH9%23nYIHA.5984@TK2MSFTNGP06.phx.gbl... >>I have a form where I want to populate a control [txtbusinessnamea] based >>on the content of either of two other controls [txtmemnbr] and >>[txtempmemnumbera]. If neither of the latter controls have data, then I >>want the first control to be blank. So a series of lookup statments may >>look like this; >> >> IF (IsNotNull([txtmemnbr]) and >> IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," >> ") Else >> >> IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) >> ,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera]")," >> ") Else >> >> IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then >> txtbusinessnamea is blank >> >> I realise I haven't got the syntax correct but this is just to illustrate >> what I'm trying to do. >> >> Could someone start me off with the VBA code, or an IIF statement to >> produce the result I want please, but care because I am a novice<g> >> >> Many thanks >> Tony >> >> >> >> >> > >

Hi - Using an if-elseif-endif statement might be the easiest way to handle this: IF Not IsNull([txtmemnbr]) then me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," ElseIF Not IsNull([txtempmemnumbera]) then me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," Else me![txtbusinessnamea]= " " EndIF This structure puts priority on [txtmemnbr], i.e. it checks it first, and and it also does not cover the situation if both [txtmemnbr] AND [txtempmemnumbera] are not null. Where are the fields [txtmemnbr] and [txtempmemnumbera]? If they are on the subform, then the syntax of the IF statements is not correct; it must reference the fields in the same way as in the Dlookup functions. A second question is - where is this code - main form or sub-form? If it is in the sub-form, then the reference to me![txtbusinessnamea] won't work as it is currently written. HTH John Tony Williams wrote: >Hi Jeff. I think I understand the syntax for a standard IIF statement. What >I'm stuck with here is I've got 3 IIF statements that control the result and >its how do I manage all three at the same time. >Thanks >Tony >> Tony >> >[quoted text clipped - 32 lines] >>> Many thanks >>> Tony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

Tony Williams wrote: >I have a form where I want to populate a control [txtbusinessnamea] based on >the content of either of two other controls [txtmemnbr] and >[txtempmemnumbera]. If neither of the latter controls have data, then I want >the first control to be blank. So a series of lookup statments may look like >this; > >IF (IsNotNull([txtmemnbr]) and >IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]", >"[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," >") Else > >IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) >,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]= >Forms!frmMain!Subform1![txtempmemnumbera]")," >") Else > >IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then >txtbusinessnamea is blank I don't think you really need any VBA code to do that. Try setting txtbusinessnamea's conrtol source expression to something like: =DLookUp("txtbusinessname","tblindividual","txtmemnumber=" & Nz(txtmemnbr,Nz(txtempmemnumbera,0))) -- Marsh MVP [MS Access]

Thanks for that guidance. Just to answer your questions. The controls [txtmemnbr] and [txtempmemnumbera] are on the subform I was thinking that the code should be in the Before update event of the control txtbusinessname, which is also on the subform, as that is the control which I'm trying to fill. Both [txtmemnbr] AND [txtempmemnumbera] will not be null at the same time. What do you think? Tony "J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message news:7ef0ac77de4b5@uwe... > Hi - > > Using an if-elseif-endif statement might be the easiest way to handle > this: > > IF Not IsNull([txtmemnbr]) then > me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," > [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," > ElseIF Not IsNull([txtempmemnumbera]) then > me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," > [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," > Else > me![txtbusinessnamea]= " " > EndIF > > This structure puts priority on [txtmemnbr], i.e. it checks it first, and > and > it also does not cover the situation if both [txtmemnbr] AND > [txtempmemnumbera] are not null. > > Where are the fields [txtmemnbr] and [txtempmemnumbera]? If they are on > the > subform, then the syntax of the IF statements is not correct; it must > reference the fields in the same way as in the Dlookup functions. > > A second question is - where is this code - main form or sub-form? If it > is > in the sub-form, then the reference to me![txtbusinessnamea] won't work as > it > is currently written. > > HTH > > John > > > > Tony Williams wrote: >>Hi Jeff. I think I understand the syntax for a standard IIF statement. >>What >>I'm stuck with here is I've got 3 IIF statements that control the result >>and >>its how do I manage all three at the same time. >>Thanks >>Tony >>> Tony >>> >>[quoted text clipped - 32 lines] >>>> Many thanks >>>> Tony > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 >

Thanks Marsh As my controls are on a subform I use this =DLookUp("txtbusinessname","tblindividual","txtmemnumber=" & Nz(Forms!frmMain!Subform1![txtmemnbr],Nz(Forms!frmMain!Subform1![txttempmemnumber],0))) But I didn't get any results and #Error when the form opens on a new record? Any ideas? Thanks Tony "Marshall Barton" <marshbarton@wowway.com> wrote in message news:6fjup311klvk1np7a8jt89cbfsjqgvubbj@4ax.com... > Tony Williams wrote: > >>I have a form where I want to populate a control [txtbusinessnamea] based >>on >>the content of either of two other controls [txtmemnbr] and >>[txtempmemnumbera]. If neither of the latter controls have data, then I >>want >>the first control to be blank. So a series of lookup statments may look >>like >>this; >> >>IF (IsNotNull([txtmemnbr]) and >>IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]", >>"[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," >>") Else >> >>IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) >>,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]= >>Forms!frmMain!Subform1![txtempmemnumbera]")," >>") Else >> >>IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then >>txtbusinessnamea is blank > > > I don't think you really need any VBA code to do that. Try > setting txtbusinessnamea's conrtol source expression to > something like: > > =DLookUp("txtbusinessname","tblindividual","txtmemnumber=" & > Nz(txtmemnbr,Nz(txtempmemnumbera,0))) > > -- > Marsh > MVP [MS Access]

This is what I've tried, referencing the fields on the subform but it doesn't give me txtbusinessname Private Sub txtbusinessname_BeforeUpdate(Cancel As Integer) If Not IsNull(Forms!frmMain!SubForm1.Form![txtmemnbr]) Then Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", "[txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]") ElseIf Not IsNull(Forms!frmMain!SubForm1.Form![txtempmemnumber]) Then Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", "[txtmemnumber]=Forms!frmMain!Subform1.form![txtempmemnumber]") Else Me![txtbusinessname] = " " End If End Sub Any ideas? Thanks Tony "Tony Williams" <tw@invalid.com> wrote in message news:%23$0nfRpYIHA.3964@TK2MSFTNGP03.phx.gbl... > Thanks for that guidance. Just to answer your questions. The controls > [txtmemnbr] and [txtempmemnumbera] are on the subform I was thinking that > the code should be in the Before update event of the control > txtbusinessname, which is also on the subform, as that is the control > which I'm trying to fill. Both [txtmemnbr] AND [txtempmemnumbera] will > not be null at the same time. What do you think? > Tony > "J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message > news:7ef0ac77de4b5@uwe... >> Hi - >> >> Using an if-elseif-endif statement might be the easiest way to handle >> this: >> >> IF Not IsNull([txtmemnbr]) then >> me![txtbusinessnamea]= >> DLookUp("[txtbusinessname]","[tblindividual]"," >> [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," >> ElseIF Not IsNull([txtempmemnumbera]) then >> me![txtbusinessnamea]= >> DLookUp("[txtbusinessname]","[tblindividual]"," >> [txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]")," >> Else >> me![txtbusinessnamea]= " " >> EndIF >> >> This structure puts priority on [txtmemnbr], i.e. it checks it first, and >> and >> it also does not cover the situation if both [txtmemnbr] AND >> [txtempmemnumbera] are not null. >> >> Where are the fields [txtmemnbr] and [txtempmemnumbera]? If they are on >> the >> subform, then the syntax of the IF statements is not correct; it must >> reference the fields in the same way as in the Dlookup functions. >> >> A second question is - where is this code - main form or sub-form? If it >> is >> in the sub-form, then the reference to me![txtbusinessnamea] won't work >> as it >> is currently written. >> >> HTH >> >> John >> >> >> >> Tony Williams wrote: >>>Hi Jeff. I think I understand the syntax for a standard IIF statement. >>>What >>>I'm stuck with here is I've got 3 IIF statements that control the result >>>and >>>its how do I manage all three at the same time. >>>Thanks >>>Tony >>>> Tony >>>> >>>[quoted text clipped - 32 lines] >>>>> Many thanks >>>>> Tony >> >> -- >> Message posted via AccessMonster.com >> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 >> > >

Hi - Since all the controls are on the subform, you can just use Me!.. to reference them: IF Not IsNull([txtmemnbr]) then me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," [txtmemnumber]=Me![txtmemnbr]") ElseIF Not IsNull([txtempmemnumbera]) then me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," [txtmemnumber]=Me![txtempmemnumbera]") Else me![txtbusinessnamea]= " " EndIF The Before_Update event of the [txtbusinessname] (BTW - which is it - [txtbusinessname] or [txtbusinessnameA] ? ) is not the place for this code, because that event does not fire until YOU make a change to that control on the form. How do [txtmemnbr] or [txtempmemnumber] get their values? I notice I messed up my suggested code the first time - I have corrected it above, making the assumption that all the controls and the code are on the sub-form. Sorry about that! In your original post you put: "If neither of the latter controls have data, then I want the first control to be blank. " so that is the way I coded it. John Tony Williams wrote: >This is what I've tried, referencing the fields on the subform but it >doesn't give me txtbusinessname >Private Sub txtbusinessname_BeforeUpdate(Cancel As Integer) >If Not IsNull(Forms!frmMain!SubForm1.Form![txtmemnbr]) Then >Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", >"[txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]") >ElseIf Not IsNull(Forms!frmMain!SubForm1.Form![txtempmemnumber]) Then >Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", >"[txtmemnumber]=Forms!frmMain!Subform1.form![txtempmemnumber]") >Else >Me![txtbusinessname] = " " >End If >End Sub > >Any ideas? >Thanks >Tony >> Thanks for that guidance. Just to answer your questions. The controls >> [txtmemnbr] and [txtempmemnumbera] are on the subform I was thinking that >[quoted text clipped - 52 lines] >>>>>> Many thanks >>>>>> Tony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

Hi there thanks for sticking with me! It should be [txtbusinessname] and [txtempmemnumber]. [txtmemnbr] or [txtempmemnumber] get their values by them being input by the user.If not the Before_Update event where should it go. I feel we're getting close Tony "J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message news:7ef198cf6c529@uwe... > Hi - > > Since all the controls are on the subform, you can just use Me!.. to > reference them: > > IF Not IsNull([txtmemnbr]) then > me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," > [txtmemnumber]=Me![txtmemnbr]") > ElseIF Not IsNull([txtempmemnumbera]) then > me![txtbusinessnamea]= DLookUp("[txtbusinessname]","[tblindividual]"," > [txtmemnumber]=Me![txtempmemnumbera]") > Else > me![txtbusinessnamea]= " " > EndIF > > The Before_Update event of the [txtbusinessname] (BTW - which is it - > [txtbusinessname] or [txtbusinessnameA] ? ) is not the place for this > code, > because that event does not fire until YOU make a change to that control > on > the form. > > How do [txtmemnbr] or [txtempmemnumber] get their values? > > I notice I messed up my suggested code the first time - I have corrected > it > above, making the assumption that all the controls and the code are on the > sub-form. > > Sorry about that! > > In your original post you put: > > "If neither of the latter controls have data, then I want > the first control to be blank. " > > so that is the way I coded it. > > > John > > > > Tony Williams wrote: >>This is what I've tried, referencing the fields on the subform but it >>doesn't give me txtbusinessname >>Private Sub txtbusinessname_BeforeUpdate(Cancel As Integer) >>If Not IsNull(Forms!frmMain!SubForm1.Form![txtmemnbr]) Then >>Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", >>"[txtmemnumber]=Forms!frmMain!Subform1.form![txtmemnbr]") >>ElseIf Not IsNull(Forms!frmMain!SubForm1.Form![txtempmemnumber]) Then >>Me![txtbusinessname] = DLookup("[txtbusinessname]", "[tblindividual]", >>"[txtmemnumber]=Forms!frmMain!Subform1.form![txtempmemnumber]") >>Else >>Me![txtbusinessname] = " " >>End If >>End Sub >> >>Any ideas? >>Thanks >>Tony >>> Thanks for that guidance. Just to answer your questions. The controls >>> [txtmemnbr] and [txtempmemnumbera] are on the subform I was thinking >>> that >>[quoted text clipped - 52 lines] >>>>>>> Many thanks >>>>>>> Tony > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 >

It would go in the After Update event of [txtmemnbr] and [txtempmemnumber], and it would work fine just as it is. But I'm not sure what the logic is. If I had to guess, it would be that the user enters a value in [txtmemnbr], and if that does not result in a valid business name, then the user enters a second value in [txtempmemnumber], and if that is still not valid, the business name is left blank. Am I close? John Tony Williams wrote: >Hi there thanks for sticking with me! >It should be [txtbusinessname] and [txtempmemnumber]. [txtmemnbr] or >[txtempmemnumber] get their values by them being input by the user.If not >the Before_Update event where should it go. I feel we're getting close >Tony >> Hi - >> >[quoted text clipped - 59 lines] >>>>>>>> Many thanks >>>>>>>> Tony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

That's exactly it John. This is a database of complaints where when logging the complaint if the subject of the complaint is a member then we key txtmemnbr to give the business name, if they're not a member but an associate we key in txttempmemnumber to give the business name if they're neither the businessname is left blank. So does the code go in the Afterupdate of both controls? If so,just a thought, could I not split it so that the if statment only refers to the individual controls and therefore needn't have an if statement covering all three situations, if I've explained my self correctly? Thanks Tony "J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message news:7ef200fe23a06@uwe... > It would go in the After Update event of [txtmemnbr] and > [txtempmemnumber], > and it would work fine just as it is. But I'm not sure what the logic is. > If I had to guess, it would be that the user enters a value in > [txtmemnbr], > and if that does not result in a valid business name, then the user enters > a > second value in [txtempmemnumber], and if that is still not valid, the > business name is left blank. > > Am I close? > > John > > > > Tony Williams wrote: >>Hi there thanks for sticking with me! >>It should be [txtbusinessname] and [txtempmemnumber]. [txtmemnbr] or >>[txtempmemnumber] get their values by them being input by the user.If not >>the Before_Update event where should it go. I feel we're getting close >>Tony >>> Hi - >>> >>[quoted text clipped - 59 lines] >>>>>>>>> Many thanks >>>>>>>>> Tony > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 >

Hi "...could I not split it so that the if statment only refers to the individual controls and therefore needn't have an if statement covering all three situations..." Right on! Here's what I would do - I would assume that the user does not know whether the subject is a member or not, and do this: for the After Update of [txtmemnbr]: Dim tmpName as Variant tmpname=nz(DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Me! [txtmemnbr]"),0) if tmpname=0 then [txtempmemnumber].enabled = True else me![txtbusinessname] = tmpname [txtempmemnumber].enabled = False endif and for the After Update of [txtempmemnumber]: me![txtbusinessname]=nz(DLookUp("[txtbusinessname]","[tblindividual]"," [txtmemnumber]=Me![txtempmemnumber]")," ") You could put any text in the second part of the Nz function, e.g. "N/A" in place of just a blank But, now that I look at it again, you don't need the TWO controls [txtmemnbr] and [txtempmemnumber]. The DLookup returns the same field from the same table, and uses the same field in the criteria for both, and there is no mention of whether the subject of the complaint is a member or not. So, given a specified number, either the subject is in the [tblindividual] table or it isn't - you only need one control for the number. Have I missed something? John Tony Williams wrote: >That's exactly it John. This is a database of complaints where when logging >the complaint if the subject of the complaint is a member then we key >txtmemnbr to give the business name, if they're not a member but an >associate we key in txttempmemnumber to give the business name if they're >neither the businessname is left blank. >So does the code go in the Afterupdate of both controls? If so,just a >thought, could I not split it so that the if statment only refers to the >individual controls and therefore needn't have an if statement covering all >three situations, if I've explained my self correctly? >Thanks >Tony >> It would go in the After Update event of [txtmemnbr] and >> [txtempmemnumber], >[quoted text clipped - 20 lines] >>>>>>>>>> Many thanks >>>>>>>>>> Tony -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

John, the two controls [txtmemnbr] and [txtempmemnumber] are both seperate controls based on two fields in the table. The user does know if the subjest is a member or not from the complaint form. What we're trying to do here is from the subjects record in tblindividual, determine want their business name is. Members have numbers, [txtmemnbr], associates have different numbers [txtempmemnumber], and if the subject of the complaint is neither member or assocaite, they don't have a number. If that makes sense? Does that change anything? I'm in the UK and it's 20.02 and my wife is "saying dinner's ready" So i'll pack up now and get back to this tomorrow and let my 63 year old brain have a good nights sleep and pick it up tomorrow. Thanks for sticking with me. Tony "J_Goddard via AccessMonster.com" <u37558@uwe> wrote in message news:7ef27be185e3b@uwe... > Hi > > "...could I not split it so that the if statment only refers to the > individual controls and therefore needn't have an if statement covering > all > three situations..." > > Right on! > > Here's what I would do - I would assume that the user does not know > whether > the subject is a member or not, and do this: > > for the After Update of [txtmemnbr]: > > Dim tmpName as Variant > tmpname=nz(DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Me! > [txtmemnbr]"),0) > if tmpname=0 then > [txtempmemnumber].enabled = True > else > me![txtbusinessname] = tmpname > [txtempmemnumber].enabled = False > endif > > and for the After Update of [txtempmemnumber]: > > me![txtbusinessname]=nz(DLookUp("[txtbusinessname]","[tblindividual]"," > [txtmemnumber]=Me![txtempmemnumber]")," ") > > You could put any text in the second part of the Nz function, e.g. "N/A" > in > place of just a blank > > > But, now that I look at it again, you don't need the TWO controls > [txtmemnbr] > and [txtempmemnumber]. The DLookup returns the same field from the same > table, and uses the same field in the criteria for both, and there is no > mention of whether the subject of the complaint is a member or not. So, > given a specified number, either the subject is in the [tblindividual] > table > or it isn't - you only need one control for the number. > > Have I missed something? > > John > > > > > > Tony Williams wrote: >>That's exactly it John. This is a database of complaints where when >>logging >>the complaint if the subject of the complaint is a member then we key >>txtmemnbr to give the business name, if they're not a member but an >>associate we key in txttempmemnumber to give the business name if they're >>neither the businessname is left blank. >>So does the code go in the Afterupdate of both controls? If so,just a >>thought, could I not split it so that the if statment only refers to the >>individual controls and therefore needn't have an if statement covering >>all >>three situations, if I've explained my self correctly? >>Thanks >>Tony >>> It would go in the After Update event of [txtmemnbr] and >>> [txtempmemnumber], >>[quoted text clipped - 20 lines] >>>>>>>>>>> Many thanks >>>>>>>>>>> Tony > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1 >

Hi Tony - First thing I might suggest, given your information, is to fix your table structure. Instead of having two separate number fields, for member and associate, have only one number field, and a text field to indicate which type of membership it is. You will find things much easier if you do, and guarantees that no-one can be a member and an associate at the same time. The reason I made my suggestion is that if you look (way) back at your original post, both your DLookups used the same field in the criteria expression, so I only thought there was one field. But, even if you leave your table as it is, you can still use only one number control on the form, and have this for the After Update event (we'll use [txtmemnbr]) for the After Update of [txtmemnbr]: Dim tmpName as Variant ' ' Try the member number first ' tmpname=nz(DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnbr] = " & Me![txtmemnbr]),0) if tmpname=0 then ' ' Try the associate number ' me![txtbusinessname]=nz(DLookUp("[txtbusinessname]","[tblindividual]", "[txtempmemnumber]=" & Me![txtmemnbr])," ") else me![txtbusinessname] = tmpname endif NOTE : I changed the syntax of the DLookup criteria expressions a bit. So now both table fields are checked, you need only one form control, and the user doesn't need to know the membership type. John Tony Williams wrote: >John, the two controls [txtmemnbr] and [txtempmemnumber] are both seperate >controls based on two fields in the table. The user does know if the subjest >is a member or not from the complaint form. What we're trying to do here is >from the subjects record in tblindividual, determine want their business >name is. Members have numbers, [txtmemnbr], associates have different >numbers [txtempmemnumber], and if the subject of the complaint is neither >member or assocaite, they don't have a number. >If that makes sense? >Does that change anything? >I'm in the UK and it's 20.02 and my wife is "saying dinner's ready" So i'll >pack up now and get back to this tomorrow and let my 63 year old brain have >a good nights sleep and pick it up tomorrow. >Thanks for sticking with me. >Tony >> Hi >> >[quoted text clipped - 61 lines] >>>>>>>>>>>> Many thanks >>>>>>>>>>>> Tony -- Message posted via http://www.accessmonster.com

For newer versions of Access, the references should use the Form property: Forms!frmMain!Subform1.Form![txtmemnbr] If all of the text boxes are in the subform, then you can just use what I posted. If the expression text box is on the main form and the other two are in the subform, then you could use: Subform1.Form![txtmemnbr You should only get a Null result when neither number is specified (or if neither is in the table). I'm wondering if the name of the txtmemnumber field means that it is a Text field. If it is, then try: =DLookUp("txtbusinessname","tblindividual","txtmemnumber='" & Nz(txtmemnbr,Nz(txtempmemnumbera,0)) & "' ") -- Marsh MVP [MS Access] Tony Williams wrote: > my controls are on a subform I use this >=DLookUp("txtbusinessname","tblindividual","txtmemnumber=" & >Nz(Forms!frmMain!Subform1![txtmemnbr],Nz(Forms!frmMain!Subform1![txttempmemnumber],0))) > >But I didn't get any results and #Error when the form opens on a new record? > >"Marshall Barton" wrote >> Tony Williams wrote: >> >>>I have a form where I want to populate a control [txtbusinessnamea] based >>>on >>>the content of either of two other controls [txtmemnbr] and >>>[txtempmemnumbera]. If neither of the latter controls have data, then I >>>want >>>the first control to be blank. So a series of lookup statments may look >>>like >>>this; >>> >>>IF (IsNotNull([txtmemnbr]) and >>>IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]", >>>"[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," >>>") Else >>> >>>IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) >>>,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]= >>>Forms!frmMain!Subform1![txtempmemnumbera]")," >>>") Else >>> >>>IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then >>>txtbusinessnamea is blank >> >> >> I don't think you really need any VBA code to do that. Try >> setting txtbusinessnamea's conrtol source expression to >> something like: >> >> =DLookUp("txtbusinessname","tblindividual","txtmemnumber=" & >> Nz(txtmemnbr,Nz(txtempmemnumbera,0)))

Just to say I've eventually cracked this. I did it by putting individual Dlookup statements in the Before update events of the controls [txtmemnbr] and [txtempmemnumber]. There will never be an instance where both numbers are entered at the same time so I treated them separately. I should have thought of that in the first place, but we all learn from our own mistakes, I hope!!! Thanks for all your help Tony "Tony Williams" <tw@invalid.com> wrote in message news:%23UiH9%23nYIHA.5984@TK2MSFTNGP06.phx.gbl... >I have a form where I want to populate a control [txtbusinessnamea] based >on the content of either of two other controls [txtmemnbr] and >[txtempmemnumbera]. If neither of the latter controls have data, then I >want the first control to be blank. So a series of lookup statments may >look like this; > > IF (IsNotNull([txtmemnbr]) and > IsNull([txtempmemnumbera]),DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")," > ") Else > > IF (IsNotNull([txtempmemnumbera] and IsNull([txtmemnbr]) > ,DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera]")," > ") Else > > IF IsNull([txtmemnbr]) and IsNull([txtempmemnumbera]), then > txtbusinessnamea is blank > > I realise I haven't got the syntax correct but this is just to illustrate > what I'm trying to do. > > Could someone start me off with the VBA code, or an IIF statement to > produce the result I want please, but care because I am a novice<g> > > Many thanks > Tony > > > > >

Tony Williams wrote: >Just to say I've eventually cracked this. I did it by putting individual >Dlookup statements in the Before update events of the controls [txtmemnbr] >and [txtempmemnumber]. There will never be an instance where both numbers >are entered at the same time so I treated them separately. Glad to hear it. The AfterUpdate event is generally more appropriate. -- Marsh MVP [MS Access]

Similiar Articles:

Help with IF statement check value in multiple cells - microsoft ...
I am trying to use an if statement that checks for a value across multiple cells and I can't seem to get it to work. I am not sure if I am supposed...

Access 2003 multiple if then statement help - microsoft.public ...
Hello, I am having a problem constructing the proper clause for querying my database. I have multiple values used in computing daily averages of cons...

Help with query - iff & greater than less then - microsoft ...
IIf Statement [Greater Than OR Less Than] Query Help: greater ... IIf Statement [Greater Than OR Less Than] Query Help ... have a date with each row of data then you could ...

If statement linking data from one sheet to another - microsoft ...
if formula for time sheet. - help - microsoft.public.excel ... If statement linking data from one sheet to another - microsoft ... I am trying to develop a summery sheet ...

Report Sum/If Statement - microsoft.public.access.reports ...
Access 2003 multiple if then statement help - microsoft.public ... My problem is how to integrate the DoNotUse into the IIF statement I attempted to use Or in conjunction ...

vlookup from two sources - syntax of vlookup statement - microsoft ...
Excel - Adding If Statement To Vlookup - - Free Excel Help Adding If Statement To Vlookup ... an if statement with 2 vlookups but dont no where to start.

Using an IF statement, based on 2 COUNTIF results (I think ...
Help with IF statement check value in multiple cells - microsoft ... Using an IF statement, based on 2 COUNTIF results (I think ... Help with IF statement check value in ...

How do you do an IF statement in Excel comparing three columns ...
HELP: Compare Column values with column names in different tables ... HELP: Compare Column values with column names in different tables ... How do you do an IF ...

in access, if then statement - microsoft.public.access.queries ...
Access 2003 multiple if then statement help - microsoft.public ... Help with IF ... Excel :: IF Statement Help With Values In Multiple Cells IF Statement Help With Values ...

Nested IF(OR(AND) Statement with Two AND Conditions - microsoft ...
I have tried several different variations of Nested IF statements, but to no avail. I do not know VBA, so this is not an option. Any help you can provide would be ...

IIF statement in query criteria, help! - microsoft.public.access ...
IIF statement in query criteria, help! - microsoft.public.access ... IIf statement, wildcard to return all records - microsoft.public ... Hello, I am using an iif ...

Nesting IF Statement in excel 2007 - microsoft.public.excel ...
Access 2003 multiple if then statement help - microsoft.public ... On Wed, 25 Jul 2007 17:57:59 -0000, JAV ... Nested IF(OR(AND) Statement with Two AND Conditions ...

Multiple IIf statements - microsoft.public.access
Multiple IIf statements - microsoft.public.access Can someone please help me with my Case Statement ... the nested if then statements ... Multiple IIF ...

If then statement?? - microsoft.public.access.queries
Access 2003 multiple if then statement help - microsoft.public ... Help with IF statement check value in multiple cells - microsoft ... Access 2003 multiple if then ...

Connecting multiple IF statements into one formula - microsoft ...
Help with IF statement check value in multiple cells - microsoft ... Combine multiple if statements in one text box - microsoft ... Office Help[/url ...

Too many IIF statements - microsoft.public.access
Access 2003 multiple if then statement help - microsoft.public ... How to use "IIf" when there are multiple cases ... Access 2003 multiple if then statement help ...

CountIF Statement combining 2 different pieces of data - microsoft ...
Help with IF statement check value in multiple cells - microsoft ... I am trying to use an if statement that checks for a ... that is a number I need to use the above data ...

Hiding Rows Using IF Statement - microsoft.public.excel ...
... Rows Using If Statements - I am wanting... - Free ... Hiding Rows Using If Statements - I am wanting to hide rows based on if statements If a single... - Free Excel Help

Editing Paragraph Line Spacing using macro; need help - microsoft ...
I can't find > > anything > > that addresses the issue in an if statement. I hope someone can help. > > > > Sub ParaSpacingFix3() > > '2.

Useing IF statements to change a cell color - microsoft.public ...
... And Font Color Using If Statement - I am using ... Change Cell And Font Color Using If Statement - I am using an IF statement and would like to change... - Free Excel Help

IF Statement | Excel Help
IF Statement easy to follow excel guide. Showing step by step, with pictures and diagrams. Also free to ask advice, questions or requests.

Excel if statement (function) - tutorial and help - else - then
The if function statement help and tutorial guide - else then - Excell Microsoft online training. If statement examples.

IF - Excel - Office.com
... if_false arguments are evaluated, IF returns the value returned by those statements. ... Select the example in the Help topic. Note Do not select the row or column ...

Need help on IF Statement:
Actuals Fcst $56 $68 $13 19% $0 $0 $0 94% I'm trying to get the %'s to calculate. The 2nd set of numbers which are 0 (due to rounding) is ...

Excel - If Statement Help - This is probably... - Free Excel Help
If Statement Help - This is probably a very simple fix but I cannot figure... - Free Excel Help

If...Then...Else Statement (Visual Basic)
Conditionally executes a group of statements, depending on the value of an expression.

Execute statements if condition is true - MATLAB
This MATLAB function evaluates an expression, and executes a group of statements when the expression is true.

PHP Tutorial - If Statement - Tizag Tutorials
With the use of PHP if statements you could have this process automated, months in ... Help Tizag Grow Link to Tizag Tizag.com Store

Excel - If Statement - Need some helpI... - Free Excel Help
If Statement - Need some helpI am a VBA noob please excuse the ignorance... - Free Excel Help

Introduction to IF Statements in Excel - Logical Expressions, Inc ...
Learn how to make decision in Excel ... Introduction to IF Statements in Excel by Linda Johnson, MOS: The IF function in Excel is one that many Excel newbies don’t ...

7/10/2012 11:49:41 PM