Help with If statement

[Tony Williams]

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

 

[Jeff Boyce]

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]

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

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

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

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Marshall Barton]

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)))

 

[Tony Williams]

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

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

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

 

[Tony Williams]

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

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)))

 

[Tony Williams]

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
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

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

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

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Tony Williams]

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 -

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

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

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Tony Williams]

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],
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

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

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Tony Williams]

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

"...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

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

 

[J_Goddard via AccessMonster.com]

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

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]

 

[Marshall Barton]

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]

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

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)))

 

[Tony Williams]

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

 

[Marshall Barton]

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.