Display a value in a field based on the value of 2 other fields

Andrew R in Canberra · Nov 12, 2005

I'm sure this should be easy, but can't make it work, can anyone help me ??
I have a form based on a table. In that table there are 2 fields that are
filled in then a 3rd field should display a result that depends on what is in
the other 2 fields.
I am trying to use an unbound field that has a query as its control source,
the query has the 2 filled in fields in it with criteria [Forms]![form
name]![field name]. This query works, ie it reads the fields in the form and
based on those criteria presents the value I want from another table. I
can't seem to get that value to display in the current forms 3rd field
though??? Bizare, no? Any suggestions? Other methods I could use? (I've
also tried an action query to update the value from the query to the table,
but for some reason can't get that to display the result either???)
Many thanks in anticipation of a genius solution!
Andrew R in Canberra

Allen Browne · Nov 12, 2005

As you found, you can't put a query statement into the ControlSource of a
text box. The nearest thing is probably a DLookup() expression, where
literal values from the other 2 controls is concatenated into the 3rd
argument. Something like this:
=DLookup("CreditLimit", "tblCreditLimit", "(ClientID = " &
Nz([ClientID],0) & ") AND ([Status = """ & [Status] & """)")

For more help on how to form the 3rd argument, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

There might be a better solution. Could you create a query that contains the
lookup table as well as the main table for your form, and set the
RecordSource of your form to that query? If so, you can include the lookup
field in the query, and just treat it like any other field in your form.

David Lloyd · Nov 12, 2005

Andrew:

One alternative you might want to consider is the DLookup function. This
function allows you to find the value in another table or query based on
specified criteria. You could use this function to lookup your third value
based on the value of the other two fields. For example, you can set the
Control Source property of the form textbox:

=DLookup("[MyThirdFieldName]", "MyTableOrQueryName", "[MyFirstFieldName] ="
& Forms![MyFormName]!MyFirstFieldControlName & " AND [MySecondFieldName] ="
& Forms![MyFormName]!MySecondFieldControlName)

The following link is an MSDN reference page on how this function works.

http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

You will need to adjust this example for data type, and of course your form,
control and field names.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

"Andrew R in Canberra" <Andrew R in [email protected]>
wrote in message I'm sure this should be easy, but can't make it work, can anyone help me ??
I have a form based on a table. In that table there are 2 fields that are
filled in then a 3rd field should display a result that depends on what is
in
the other 2 fields.
I am trying to use an unbound field that has a query as its control source,
the query has the 2 filled in fields in it with criteria [Forms]![form
name]![field name]. This query works, ie it reads the fields in the form
and
based on those criteria presents the value I want from another table. I
can't seem to get that value to display in the current forms 3rd field
though??? Bizare, no? Any suggestions? Other methods I could use? (I've
also tried an action query to update the value from the query to the table,
but for some reason can't get that to display the result either???)
Many thanks in anticipation of a genius solution!
Andrew R in Canberra

David Lloyd · Nov 14, 2005

Andrew:

One alternative you might want to consider is the DLookup function. This
function allows you to find the value in another table or query based on
specified criteria. You could use this function to lookup your third value
based on the value of the other two fields. For example, you can set the
Control Source property of the form textbox:

=DLookup("[MyThirdFieldName]", "MyTableOrQueryName", "[MyFirstFieldName] ="
& Forms![MyFormName]!MyFirstFieldControlName & " AND [MySecondFieldName] ="
& Forms![MyFormName]!MySecondFieldControlName)

The following link is an MSDN reference page on how this function works.

http://msdn.microsoft.com/library/d...n-us/vbaac11/html/acfctDLookup_HV05187182.asp

You will need to adjust this example for data type, and of course your form,
control and field names.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

"Andrew R in Canberra" <Andrew R in [email protected]>
wrote in message I'm sure this should be easy, but can't make it work, can anyone help me ??
I have a form based on a table. In that table there are 2 fields that are
filled in then a 3rd field should display a result that depends on what is
in
the other 2 fields.
I am trying to use an unbound field that has a query as its control source,
the query has the 2 filled in fields in it with criteria [Forms]![form
name]![field name]. This query works, ie it reads the fields in the form
and
based on those criteria presents the value I want from another table. I
can't seem to get that value to display in the current forms 3rd field
though??? Bizare, no? Any suggestions? Other methods I could use? (I've
also tried an action query to update the value from the query to the table,
but for some reason can't get that to display the result either???)
Many thanks in anticipation of a genius solution!
Andrew R in Canberra

Andrew R in Canberra · Nov 15, 2005

Allen Browne said:
As you found, you can't put a query statement into the ControlSource of a
text box. The nearest thing is probably a DLookup() expression, where
literal values from the other 2 controls is concatenated into the 3rd
argument. Something like this:
=DLookup("CreditLimit", "tblCreditLimit", "(ClientID = " &
Nz([ClientID],0) & ") AND ([Status = """ & [Status] & """)")

For more help on how to form the 3rd argument, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

There might be a better solution. Could you create a query that contains the
lookup table as well as the main table for your form, and set the
RecordSource of your form to that query? If so, you can include the lookup
field in the query, and just treat it like any other field in your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Andrew R in Canberra said:

I'm sure this should be easy, but can't make it work, can anyone help me
??
I have a form based on a table. In that table there are 2 fields that are
filled in then a 3rd field should display a result that depends on what is
in
the other 2 fields.
I am trying to use an unbound field that has a query as its control
source,
the query has the 2 filled in fields in it with criteria [Forms]![form
name]![field name]. This query works, ie it reads the fields in the form
and
based on those criteria presents the value I want from another table. I
can't seem to get that value to display in the current forms 3rd field
though??? Bizare, no? Any suggestions? Other methods I could use? (I've
also tried an action query to update the value from the query to the
table,
but for some reason can't get that to display the result either???)
Many thanks in anticipation of a genius solution!
Andrew R in Canberra

Click to expand...

Thanks - I'll try both those - in the end I used 3 hidden controls - ie 2 that displayed numbers relating to the 2 choosing fields and another that added the 2 together - I then used a table with a list of possible results and displayed the result using a combo box - It doesnt show the result automatically, but allows only the correct result to be shown!!! strange, but it works!

Thanks Allan

Andrew R in Canberra · Dec 4, 2005

Thanks for that David

I ended up solving the problem another way, but now I have discovered a very
baffling problem which I am hoping you may be able to assist with.

Every time I exit my database and open it again a query which has the value
from a form as one of its criteria changes the criteria so that the wrong
form is named in the criteria - all by itself????? This is most upsetting
because of course it prevents the query from working!! How could access be
doing this? Any clues would be gratefully received.

Regards Andrew

Allen Browne · Dec 4, 2005

Sounds like another Name AutoCorrupt issue.

Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database to get rid of this junk:
Tools | Database Utilities | Compact

Here's a partial list of some of the problems this misfeature causes:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

Andrew R in Canberra · Dec 12, 2005

Thanks to Allen Browne and David Lloyd very much for your helpful advice.
As a result I:
1 finished the project on time and it works properly
2 learned how to use the DLookup function to make a better database
3 learned how to use SQL in a macro to avoid the problem I had
4 learned about name auto correct causing problems

So thanks again - I'll watch the posts and try to help others with some of
the stuff I know about how to make Access sing (without knowing any vb code
or programming) if I can - this is a great community

Cheers Andrew