Help with If and Then statement

[Tony Williams]

I have a form based on a query which is itself based on 2 tables tbllevy and
tblindividual. The form has a control, txtpaidbybusiness, whose control
source is a YES/NO field in tbllevy with the same name. The form has another
control whose source is txtbusinessname in tbllevy. If txtpaidbybusiness is
YES I want to populate the control txtbusinessname with the value of the
field with the same name from tblindividual. The two tables are linked in
the query by txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the AfterUpdate
event of txtpaidbybusiness?
Thanks

 

[UpRider]

Tony, as described, if you 'populate' txtbusinessname on the form with
tblIndividual.txtbusinessname, it will update tblLevy.txtbusinessname with
that value because the form control is bound to the latter. Is this what
you want?

UpRider

 

[Tony Williams]

Yes That's what I want to happen because I need to report on it later
Tony

 

[UpRider]

OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname because
that's what's there by default.

All the above assumes that the query contains txtbusinessname from both
tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

 

[Tony Williams]

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the message
Access can't find the field | and the whole line of codeMe.txtbusinessname
= [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony

OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from both
tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony

 

[UpRider]

Tony, take a look at your query. See if txtbusinessname is included from
BOTH tables. If you have to add it from tblIndividual, then you will
probably get an error on your form for the txtbusinessname control because
it won't know which one to use. On the data source for that control type
tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony

OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from both
tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form with
tblIndividual.txtbusinessname, it will update tblLevy.txtbusinessname
with that value because the form control is bound to the latter. Is
this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control, txtpaidbybusiness,
whose control source is a YES/NO field in tbllevy with the same name.
The form has another control whose source is txtbusinessname in
tbllevy. If txtpaidbybusiness is YES I want to populate the control
txtbusinessname with the value of the field with the same name from
tblindividual. The two tables are linked in the query by
txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks

 

[Tony Williams]

Hi I've tried it wit and without txtbusinessname from tblindividual in the
query and get the same message. Is my code complete? Do I need to add
anything else?
Thanks
Tony

Tony, take a look at your query. See if txtbusinessname is included from
BOTH tables. If you have to add it from tblIndividual, then you will
probably get an error on your form for the txtbusinessname control because
it won't know which one to use. On the data source for that control type
tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony

OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from both
tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form with
tblIndividual.txtbusinessname, it will update tblLevy.txtbusinessname
with that value because the form control is bound to the latter. Is
this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control, txtpaidbybusiness,
whose control source is a YES/NO field in tbllevy with the same name.
The form has another control whose source is txtbusinessname in
tbllevy. If txtpaidbybusiness is YES I want to populate the control
txtbusinessname with the value of the field with the same name from
tblindividual. The two tables are linked in the query by
txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks

 

[UpRider]

Please post the query SQL.

UpRider

Hi I've tried it wit and without txtbusinessname from tblindividual in the
query and get the same message. Is my code complete? Do I need to add
anything else?
Thanks
Tony

Tony, take a look at your query. See if txtbusinessname is included from
BOTH tables. If you have to add it from tblIndividual, then you will
probably get an error on your form for the txtbusinessname control
because it won't know which one to use. On the data source for that
control type tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony
OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from both
tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form
with tblIndividual.txtbusinessname, it will update
tblLevy.txtbusinessname with that value because the form control is
bound to the latter. Is this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control, txtpaidbybusiness,
whose control source is a YES/NO field in tbllevy with the same name.
The form has another control whose source is txtbusinessname in
tbllevy. If txtpaidbybusiness is YES I want to populate the control
txtbusinessname with the value of the field with the same name from
tblindividual. The two tables are linked in the query by
txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks

 

[Tony Williams]

Hi there hope you're still with me! I'm in the UK and it got late last night
as I was working so I gave up. However back this morning, here is the SQL

SELECT tblLevy.txtfirstname, tblLevy.txtsurname, tblLevy.txtbusinessname,
tblLevy.txtmemnbr, tblLevy.txtwritnbr, tblLevy.txtlevyfee,
tblLevy.txtdatepaid, tblLevy.txtpaidby, tblLevy.txtpaidbybusiness,
tblLevy.txtdatereceived, tblLevy.txtnotes
FROM tblLevy LEFT JOIN tblindividual ON tblLevy.txtbusinessname =
tblindividual.txtbusinessname;

Hope that helps?
Thanks
Tony

Please post the query SQL.

UpRider

Hi I've tried it wit and without txtbusinessname from tblindividual in
the query and get the same message. Is my code complete? Do I need to add
anything else?
Thanks
Tony

Tony, take a look at your query. See if txtbusinessname is included
from BOTH tables. If you have to add it from tblIndividual, then you
will probably get an error on your form for the txtbusinessname control
because it won't know which one to use. On the data source for that
control type tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony
OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from
both tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form
with tblIndividual.txtbusinessname, it will update
tblLevy.txtbusinessname with that value because the form control is
bound to the latter. Is this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control,
txtpaidbybusiness, whose control source is a YES/NO field in tbllevy
with the same name. The form has another control whose source is
txtbusinessname in tbllevy. If txtpaidbybusiness is YES I want to
populate the control txtbusinessname with the value of the field
with the same name from tblindividual. The two tables are linked in
the query by txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks

 

[Tony Williams]

Hi I've cracked it! I've used an IIf statement combined with a DLookup
statement as the control source and it worked fine.
Thanks anyway for your guidance
Tony

Please post the query SQL.

UpRider

Hi I've tried it wit and without txtbusinessname from tblindividual in
the query and get the same message. Is my code complete? Do I need to add
anything else?
Thanks
Tony

Tony, take a look at your query. See if txtbusinessname is included
from BOTH tables. If you have to add it from tblIndividual, then you
will probably get an error on your form for the txtbusinessname control
because it won't know which one to use. On the data source for that
control type tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony
OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from
both tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form
with tblIndividual.txtbusinessname, it will update
tblLevy.txtbusinessname with that value because the form control is
bound to the latter. Is this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control,
txtpaidbybusiness, whose control source is a YES/NO field in tbllevy
with the same name. The form has another control whose source is
txtbusinessname in tbllevy. If txtpaidbybusiness is YES I want to
populate the control txtbusinessname with the value of the field
with the same name from tblindividual. The two tables are linked in
the query by txtmemnumber.tblindividual and txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks

 

[Dupatt]

Hi Tony, Congratulations for "getting it". would you mind posting the code
you ended up with? I am having the same sort of problem. Thanks, Pat
If you don't want to post it my email is (e-mail address removed) (minus
block)

--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

Hi I've cracked it! I've used an IIf statement combined with a DLookup
statement as the control source and it worked fine.
Thanks anyway for your guidance
Tony

Please post the query SQL.

UpRider

Hi I've tried it wit and without txtbusinessname from tblindividual in
the query and get the same message. Is my code complete? Do I need to
add anything else?
Thanks
Tony
Tony, take a look at your query. See if txtbusinessname is included
from BOTH tables. If you have to add it from tblIndividual, then you
will probably get an error on your form for the txtbusinessname control
because it won't know which one to use. On the data source for that
control type tblLevy.txtbusinessname to clear the error.

UpRider

Hi there here is my code in situ
Private Sub txtpaidbybusiness_AfterUpdate()
If Me.txtpaidbybusiness = True Then
Me.txtbusinessname = [tblindividual].[txtbusinessname]
End If

End Sub

However when I click on the tickbox I get a VBA error break and the
message Access can't find the field | and the whole line of
codeMe.txtbusinessname = [tblindividual].[txtbusinessname]
is highlighted. Any ideas?
Thanks
Tony
OK, the txtpaidbybusiness Y/N control after update would be:

If me.txtpaidbybusiness = true then
me.txtbusinessname = [tblindividual].[txtbusinessname]
endif

There's no need for an 'else' to update with tbllevy.txtbusinessname
because that's what's there by default.

All the above assumes that the query contains txtbusinessname from
both tables, with me.txtbusinessname bound to tbllevy.txtbusinessname

UpRider

Yes That's what I want to happen because I need to report on it
later
Tony
Tony, as described, if you 'populate' txtbusinessname on the form
with tblIndividual.txtbusinessname, it will update
tblLevy.txtbusinessname with that value because the form control is
bound to the latter. Is this what you want?

UpRider

I have a form based on a query which is itself based on 2 tables
tbllevy and tblindividual. The form has a control,
txtpaidbybusiness, whose control source is a YES/NO field in
tbllevy with the same name. The form has another control whose
source is txtbusinessname in tbllevy. If txtpaidbybusiness is YES I
want to populate the control txtbusinessname with the value of the
field with the same name from tblindividual. The two tables are
linked in the query by txtmemnumber.tblindividual and
txtmemnbr.tbllevy
Can someone help with the If Then statement I need to put in the
AfterUpdate event of txtpaidbybusiness?
Thanks