DLookUp - Formatting : Help Please

[Paul]

I seem to be going around in circles with this any help would be gratefully
appreciated

I am trying to format a field on a tabular form based on the unique number
being equal in another table

I am using the following code

If DLookup("[Acct]", "Monitor", "[Account]=forms!Monitor_Collect![Account]")
Then
Me!Account.ForeColor = 255
Else
Me!Account.ForeColor = 0
End If

I am putting this in the on load event of a tabular form called
Monitor_Collect comparing the account number to those in a table called
Monitoring

What I am finding is that all the account numbers now appear in Red

If I put the code in another property setting for the form no result

I am doing something wrong with this but cannot see any other method of
getting a result

Thank you

 

[Bob Larson]

You aren't using DLookup right. Your DLookup returns a value and your IF
doesn't specify what it should return. So, since it returns a value then it
is true and takes the first option.

You need to change to something like:
If DLookup("[Acct]", "Monitor", "[Account]=forms!Monitor_Collect![Account]")
= SOMETHING HERE Then...


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Paul]

Thanks Bob

From your reply I understand that the property setting will not be set on an
individual record but will on all

Is there another method I could use ?

Thank you

Paul

You aren't using DLookup right. Your DLookup returns a value and your IF
doesn't specify what it should return. So, since it returns a value then it
is true and takes the first option.

You need to change to something like:
If DLookup("[Acct]", "Monitor", "[Account]=forms!Monitor_Collect![Account]")
= SOMETHING HERE Then...


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

I seem to be going around in circles with this any help would be gratefully
appreciated

I am trying to format a field on a tabular form based on the unique number
being equal in another table

I am using the following code

If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
Then
Me!Account.ForeColor = 255
Else
Me!Account.ForeColor = 0
End If

I am putting this in the on load event of a tabular form called
Monitor_Collect comparing the account number to those in a table called
Monitoring

What I am finding is that all the account numbers now appear in Red

If I put the code in another property setting for the form no result

I am doing something wrong with this but cannot see any other method of
getting a result

Thank you

 

[Bob Larson]

What exactly are you trying to compare?


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Thanks Bob

From your reply I understand that the property setting will not be set on
an
individual record but will on all

Is there another method I could use ?

Thank you

Paul

You aren't using DLookup right. Your DLookup returns a value and your IF
doesn't specify what it should return. So, since it returns a value then
it
is true and takes the first option.

You need to change to something like:
If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
= SOMETHING HERE Then...


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

I seem to be going around in circles with this any help would be
gratefully
appreciated

I am trying to format a field on a tabular form based on the unique
number
being equal in another table

I am using the following code

If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
Then
Me!Account.ForeColor = 255
Else
Me!Account.ForeColor = 0
End If

I am putting this in the on load event of a tabular form called
Monitor_Collect comparing the account number to those in a table called
Monitoring

What I am finding is that all the account numbers now appear in Red

If I put the code in another property setting for the form no result

I am doing something wrong with this but cannot see any other method of
getting a result

Thank you

 

[Paul]

Hi Bob

What I am trying to compare is the account number on a tabular form with the
account number in a different table

The Table monitoring contains a list of accounts and contact details that
are with an external agency for monitoring

The tabular form Monitor_Collections allows the selective adding of active
or new accounts to the monitoring list via a check box and a SQL append
routine behind a button on the form.

The account number is indexed with no duplicates and the append routine has
warnings set to false

Although duplicate records are not transferred it seemed to me that a better
way was to indicate on the form that the account was already being monitored
so no need to select it

The if statement was to change the fore colour of the record if the account
was in monitoring

The table name is Monitor the field being compared is Acct - the tabular
form is
Monitor_Collect the field on that form is Account

Thank you for your help with this

Paul

What exactly are you trying to compare?


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Thanks Bob

From your reply I understand that the property setting will not be set on
an
individual record but will on all

Is there another method I could use ?

Thank you

Paul

You aren't using DLookup right. Your DLookup returns a value and your IF
doesn't specify what it should return. So, since it returns a value then
it
is true and takes the first option.

You need to change to something like:
If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
= SOMETHING HERE Then...


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com



I seem to be going around in circles with this any help would be
gratefully
appreciated

I am trying to format a field on a tabular form based on the unique
number
being equal in another table

I am using the following code

If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
Then
Me!Account.ForeColor = 255
Else
Me!Account.ForeColor = 0
End If

I am putting this in the on load event of a tabular form called
Monitor_Collect comparing the account number to those in a table called
Monitoring

What I am finding is that all the account numbers now appear in Red

If I put the code in another property setting for the form no result

I am doing something wrong with this but cannot see any other method of
getting a result

Thank you

 

[Paul]

I am still having a difficulty in getting this to work

Does anybody have any suggestions?

Thank you

Paul

Hi Bob

What I am trying to compare is the account number on a tabular form with the
account number in a different table

The Table monitoring contains a list of accounts and contact details that
are with an external agency for monitoring

The tabular form Monitor_Collections allows the selective adding of active
or new accounts to the monitoring list via a check box and a SQL append
routine behind a button on the form.

The account number is indexed with no duplicates and the append routine has
warnings set to false

Although duplicate records are not transferred it seemed to me that a better
way was to indicate on the form that the account was already being monitored
so no need to select it

The if statement was to change the fore colour of the record if the account
was in monitoring

The table name is Monitor the field being compared is Acct - the tabular
form is
Monitor_Collect the field on that form is Account

Thank you for your help with this

Paul

What exactly are you trying to compare?


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Thanks Bob

From your reply I understand that the property setting will not be set on
an
individual record but will on all

Is there another method I could use ?

Thank you

Paul


:

You aren't using DLookup right. Your DLookup returns a value and your IF
doesn't specify what it should return. So, since it returns a value then
it
is true and takes the first option.

You need to change to something like:
If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
= SOMETHING HERE Then...


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com



I seem to be going around in circles with this any help would be
gratefully
appreciated

I am trying to format a field on a tabular form based on the unique
number
being equal in another table

I am using the following code

If DLookup("[Acct]", "Monitor",
"[Account]=forms!Monitor_Collect![Account]")
Then
Me!Account.ForeColor = 255
Else
Me!Account.ForeColor = 0
End If

I am putting this in the on load event of a tabular form called
Monitor_Collect comparing the account number to those in a table called
Monitoring

What I am finding is that all the account numbers now appear in Red

If I put the code in another property setting for the form no result

I am doing something wrong with this but cannot see any other method of
getting a result

Thank you