Connecting a form's field with its sub-form fields

[jaffar2000]

ok this might be complicated.

Background:
Risk Management program to write the risk and its contingency plans.

long story short -

I have Table1 with fields: T1_ID (pk), Number1.
I have Table2 with fields: T2_ID (pk), Number2, Date, Status (open/close values), t1_id.
The two are connected with t1_id in "1 to Many" connection, meaning each record in Table1 can have many records in Table2

I created Form1 based on Table1.
In Form1, I added a sub-form, Form2, based on Table2.

Here is what I'd like to do (in vb or any other automatic way):

Number1 should be equal to the last (by T2_ID or Date) Number2 (in the connected Table/Form2) where Status = close.

Appreciate your help.
J.

 

[vbaInet]

Number1 should be equal to the last (by T2_ID or Date) Number2 (in the connected Table/Form2) where Status = close.

Appreciate your help.
J.

Your main form should drive the subform, not the subform driving the main form which is what you're trying to do. The last T2_ID in Table2 is the last T2_ID in Table1. If you want to go to the last record in Table1 you can use:

DoCmd.RunCommand acCmdRecordsGoToLast

 

[jaffar2000]

Maybe my explanation wasn't clear.
Here is an example:

Risk number 1 in Table1 has 3 mitigation plans (plan 1, plan 2, plan 3).
Each plan has an id, a date, a status and another number field (call it number2).

Same for Risk2,3 and so on...
The user fills in the data in the above number2 field.

A field in Table1, call it number1, needs to get the last mitigation plan's number2 value where the status = close.

Hope it make things clearer.

 

[vbaInet]

Let's see some sample records of the two tables and also show us which record you're trying to extract.

 

[jaffar2000]

Table1 - Risks (the main form).
ID Name Probability Severity Score UpdatedProbability
1 risk a 5 5 25 .........

Table2 - RiskPlan (my sub form)
ID RiskId Name ExpectedProbability ExpectedSeverity Score Status
1 1 name1 4 5 20 close
2 1 name2 3 5 15 close
3 1 name3 2 4 8 open

UpdatedProbability field should now be 3 cause it the last one where the status = close.

 

[vbaInet]

What field(s) determine which record is the most recent probability? The ID field or is there a Date/Time field?

 

[jaffar2000]

it can be the id or the date, as long it is the last record that has statu = close.
e.g:
if RiskPlan1 = close and also RiskPlan2 = close and RiskPlan3 = open, then data is taken from RiskPlan2.

if RiskPlan1 = close and also RiskPlan2 = close and RiskPlan3 = close, then data is taken from RiskPlan3.

 

[vbaInet]

Right, so from what you explained what I was actually after is the ID field determines which one is the most recent.

1. Create a query Sorted By ID in Descending Order and Grouped By RiskID
2. Set criteria under the Status field to 'Close'
3. In the textbox on your form, use the DLookup function to return the ExpectedProbability from the query created above as follows:

=DLookup("[COLOR=Red]ExpectedProbability[/COLOR]", "[COLOR=Red]qryTable2[/COLOR]", "[COLOR=Red]Status [/COLOR]= 'Close' AND [COLOR=Red]RiskID [/COLOR]= " & [[COLOR=Blue]RiskID[/COLOR]])

 

[jaffar2000]

Thanks, worked fine, the DLookup in the field in the main form takes the value from the field in the sub form as required however...since I'm putting the DLookup in the textbox of the field, as u suggested, the source of the field is no longer the record from the main table so any value my field gets in the form, does not appear in the original field in the table.
Is there a solution for this?

Also, is there an option to do all this with a code?
I managed to do this in excel with IF statements in the following logic:

If RiskPlan1 = close And RiskPlan2 And RiskPlan3 = close Then Take Value from ExpectedProbability3
Else If RiskPlan1 = close And RiskPlan2 Then take value from ExpectedProbability2
Else If RiskPlan1 = close Then Then take value from ExpectedProbability1
Else Do command X

thank for your help.

 

[vbaInet]

Thanks, worked fine, the DLookup in the field in the main form takes the value from the field in the sub form as required however...since I'm putting the DLookup in the textbox of the field, as u suggested, the source of the field is no longer the record from the main table so any value my field gets in the form, does not appear in the original field in the table.
Is there a solution for this?

Also, is there an option to do all this with a code?
I managed to do this in excel with IF statements in the following logic:

If RiskPlan1 = close And RiskPlan2 And RiskPlan3 = close Then Take Value from ExpectedProbability3
Else If RiskPlan1 = close And RiskPlan2 Then take value from ExpectedProbability2
Else If RiskPlan1 = close Then Then take value from ExpectedProbability1
Else Do command X

thank for your help.

Does it have to be saved? It's normally not recommended. What is recommended is that you do it on the fly and save it only in special cases. Read this:

http://allenbrowne.com/casu-14.html

 

[jaffar2000]

Thanks.
Can't see how to combine the DLookup with the need to save the data as explained in the link.
I guess my need here is much bigger with less knowledge on my side.
I appreciate your help very much.

J.

 

[vbaInet]

If you really want to save it then you do save the DLookup's value to the control/field in the After Update event of the form.

 

[jaffar2000]

Could you please elaborate more on what and where should I do with the DLookup function, the relevant field and the form??

 

[vbaInet]

It's the form you're using and you will assign the value to the control that is bound to the field you wish the value to be saved to.

Have a look in the Property Sheet for the form and you will find a tab called Event, click on that and look for After Update, click the button, select Code Builder and click ok. Then you enter your code like this:

Me.[COLOR=Red]TextboxName[/COLOR].Value = DLookup([COLOR=Red]...[/COLOR])
DoCmd.RunCommand acCmdSaveRecord

where TextboxName is the name of the textbox bound to the field.

 

[jaffar2000]

I connected back the TextBox to the original field from the Table.
I added the code.
Gettign a complie error: Argument not optional.

 

[vbaInet]

What code did you add? I'm sure you know that the "..." in the DLookup statement above needs to be filled in.