Lookup table/query

[Ammo]

Hi All,

I have table called tbl_GrantApplicants that consists of
the following fields:

GrantApplicantNumber (Primary Key), GrantApplicant,
AmountGrantAppliedFor, ReasonForGrant, GrantStatus (yes/no
data type, tick = yes, unticked = no), GrantConditions

I have another table called tbl_GrantAllocation that
consists of the following fields:

GrantAllocationNumber (Primary Key), DateGrantAllocated,
GrantRecipient (values in this field selected from
combobox using lookup function), ReasonForGrant,
AmountGrantAllocated

The row source for the lookup function for the
GrantRecipient field in tbl_GrantAllocation is as follows:

SELECT [tbl_GrantApplicants].[GrantApplicant],
[tbl_GrantApplicants].[GrantStatus], [tbl_GrantAllocation].
[AmountGrantAllocated] FROM tbl_GrantApplicants,
tbl_GrantAllocation WHERE ((([tbl_GrantApplicants].
[GrantStatus])=Yes));

Basically what I wish to achieve is when you select
a 'GrantRecipient' value from the combobox I want the
corresponding 'ReasonForGrant' and 'AmountGrantAllocated'
values to appear from 'tbl_GrantApplicants' table. Where
AmountGrantAllocated is the same as AmountGrantAppliedFor.
I hope this makes sense.

Kind Regards

Ammo

 

[Guest]

Ammo,
from what you have here it looks like all of this data should be in the same
table. With that said...

If you are using a form to work with the data in these tables you can easily
change the row source look lookup function sql to:

"SELECT tbl_GrantApplicants.GrantApplicant FROM tbl_GrantApplicants;"

This will give you the name from the other table and then on the form
set this combo box control event for "After Update" to execute some sql.

1) Set a recordset to go get the data you want and then assign it to those
fields

sql = _
"SELECT tbl_GrantApplicants.* " & _
"FROM tbl_GrantApplicants " & _
"WHERE (((tbl_GrantApplicants.GrantApplicant)='MyValue'));"

where 'MyValue' = GrantRecipient.Value

2) Set the values for the other two fields based off of the results from
this recordset.

MyRecordset = sql

ReasonForGrant = MyRecordset("ReasonForGrant ").Value
AmountGrantAllocated = MyRecordset("AmountGrantAppliedFor").Value

With that let me say again I would consider placing all of this data in the
same table...

~SPARKER