Lookup table/query

  • Thread starter Thread starter Ammo
  • Start date Start date
A

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