Form Lookup

[dek]

Hi,

Re: Form Lookup (Trying to populate activity codes applicable to contract)

Purpose: I am trying to populate the activity codes that are applicable to a particular contract. Please note the subform is incomplete with fields, however, this is not an issue.

***
Please open the relationships view
Please open the form and subform
***

A contract has only specific activity codes applied -> see "tbl_ActivitiesProject"
Open "tbl_AssetEntry" form. The subform has a field called "List4". This field populates all activity codes irrespective of contract.

Goal: I would like this field to only populate the activity codes specific to that contract.

I will expand the subform later to capture further. This is not an issue.
Assistance appreciated.

[Imdabaum]

One way to do it is by making List4(ActivityId) have the following row source.

SELECT Reference.Activity, Reference.Contract
FROM Reference
WHERE (((Reference.Contract)=[Forms]![tbl_AssetEntry]![List11]));

The other option is to set the Reference query as the row source for the form tbl_Time subform and then link the child/parent relationship on Contract=Contract_Id.

Though I would say something is still wrong with the Reference query as the Activity and Activity_ID return the same values. Is that by intention or should Activity have some sort of meaningful expression?

[dek]

Hi,

The solution works perfectly. Thanks.

I should have outlined that I would like the Activity Code to also be populated in the combo (for visual purposes) so that the end user ensures they populate the correct code.

[VBA]
SELECT Reference.Activity, Reference.Contract
FROM Reference
WHERE (((Reference.Contract)=[Forms]![tbl_AssetEntry]![List11]));[/VBA]
[/VBA]

I amended the above so that it read "SELECT Reference.Activity, Reference.ActivityCode, Reference.Contract" and amended the "Reference" query so that the Activity Code was being populated, however, it did not work.

What am I missing?

[dek]

Hi,

I don't understand the second solution. Can you provide a few steps in how to execute so that I understand?

[dek]

Sorry, my earlier post stated the solution worked. Unfortunately, it doesn't. I just realised when I selected a different contract that the codes did not update specific to that contract.

Lastly, the Activity and Activity_id appear as intended. It is a visually check for me at this stage.

Any suggestions?

One way to do it is by making List4(ActivityId) have the following row source.

SELECT Reference.Activity, Reference.Contract
FROM Reference
WHERE (((Reference.Contract)=[Forms]![tbl_AssetEntry]![List11]));

The other option is to set the Reference query as the row source for the form tbl_Time subform and then link the child/parent relationship on Contract=Contract_Id.

Though I would say something is still wrong with the Reference query as the Activity and Activity_ID return the same values. Is that by intention or should Activity have some sort of meaningful expression?

[Imdabaum]

Let me clarify. The main form shows the contract information. You want the subform to show all related records in the query "Reference"? Do you really want the text field bound to another query? Typically you would bind the form to a query and bind the text fields to fields from the query recordset.

For example add ActivityCode to your query Reference. Then bind the tbl_Time subform to Reference. Then make list4 field simply bound to Activity_Id, add another textbox that is bound to ActivityCode. I'll post the fixes that I would propose. So you can see what I'm talking about.

[Imdabaum]

If you go to design mode and select the outer border of the subform(NOT the top left square in the subform), you will see the Linked child/parent fields. These are a great resource for subforms as they can help you restrict what data is shown.

[dek]

The form is not bound to a query as I need to record (as a record) the main form information (asset, contract info, etc).

I cannot understand the terminolgy that you are using.

I did have my form working perfectly on Friday, but decided to push the limits.

All I need the form to do is:
1. based on the client selection, a list of contracts is available (dependant on client);
2. then based on contract, the appropriate activity code is available in the subform

[Imdabaum]

There are a few problems, I'm seeing here.
1. First requirement is based on the Client selection. While your contract is filtered based on the Client selection, it is actually changing the data of the AssetEntry record. Each time you change the cboClient value, you are actually changing the ClientID tied to the record in tbl_AssetEntry.
A: Possible solution: remove binding from ClientID and simply use it as a FIND feature. So when you select the value in ClientID, it advances to the record where ClientID = cboClient.

2. Requirement says based on the contract, appropriate activity code is available in the subform. Do you mean that specific activity codes should be available in the Activity drop down? Or do you mean that Activities are possessions of the Contract and you should only see the activities that you have added to the contract? Does that make sense?

For example, typically a contract may have different types and any contract may have the ability to possess 10 activities. However if a contract is typeA, then only activities 1-3, 7, and 10 might be available. And for typeB, activities 4-6, 8-9, and 10 are available. I ask because there are no activities to select on the drop down if I want to add a new activity. If you just want to add activities then why not let the Activity_ID rowsource just be all the ActivityCodes?

The row source for your Client will restrict you from ever having new clients. You'll notice Client 3 is not an option. Nor will he ever be an option because the query states: for every contract where client_id = a client_id in tbl_Client, show the client. Since there is no contract for Client3, client 3 will never be shown. Resolution: You want a left Join instead of an Inner Join. For more tips on join types google SQL Joins.
Basically Left join shows all items in left table and only the items from the right table where there is an existing relationship. Inner join shows only the records where items exist in both fields for the given relationship.

Let me know if that doesn't make sense.

Also as a general suggestion, I would say avoid using Lookup fields within a table. If you already plan on removing the Lookups and this is just a mock up of the final result, I beg your pardon. Hope my verbose explanation provides some valuable feedback.

[dek]

SOLVED. Thanks for your assistance. Have changed the db for the process.

[Imdabaum]

Awesome! Glad you found the help you needed.