Match the primary Key in the Main form to the foreign key in the Subform

[John Lee]

I have a main form and a sub form of which I have linked through the child and master fields

I want to obtain a value from the sub form and place it in the main form.

In my Main form I have a field txtRiskMatScore and I want the value from the field txtScoreRating in my sub form to be assigned to that field.

The Sub form obviously can have many records pertaining to the record in the main form, so in my sub form there is a date field for each record in the sub form.

So based on the latest date of a record pertaining to the record in the main form I want the value in txtScoreRating control in the sub form to be assigned to the txtRiskMatScore control in the main form.

The code below find the latest date in the underlying query, but it assigns the value to every record in the main form, which is not what I want to do, I want it to only assign the value if the records in both the main form and sub form are a match:

Code Start- :

' Get the latest date from the subform's record source
latestDate = Nz(DMax("dtmDateOfReview", "qryPreferredSupplierOver10000RiskMatrix"), 0)

' Get the value for that latest date
latestValue = Nz(DLookup("lngRiskScore", "qryPreferredSupplierOver10000RiskMatrix", _
"dtmDateOfReview = #" & Format(latestDate, "dd\/mm\/yyyy") & "#"), "")

' Assign to control on main form
Me.txtRiskMatScore = latestValue

End Code

I'm thinking that I need encapsulate the above code in an IF Statement something like below (which isn't working)

Code Start - :

If [Forms]![ frmPreferredSuppliersListOver10000]![Me.txtSupplierIDMain] = [Forms]![ frmPreferredSupplierOver10000RiskMatrix]![txtSupplierSub] Then

' Get the latest date from the subform's record source
latestDate = Nz(DMax("dtmDateOfReview", "qryPreferredSupplierOver10000RiskMatrix"), 0)

' Get the value for that latest date
latestValue = Nz(DLookup("lngRiskScore", "qryPreferredSupplierOver10000RiskMatrix", _
"dtmDateOfReview = #" & Format(latestDate, "dd\/mm\/yyyy") & "#"), "")

' Assign to control on main form
Me.txtRiskMatScore = latestValue
Else
End
End If

End Code

I'm clearly getting something wrong, but I'm struggling to work out what it is, any assistance would be appreciated.

Kind Regards

John

 

[GPGeorge]

Please clarify a bit.

Why do you want to display the value from the control on the subform in another control on the main form?

Is the value displayed in the control on the subform, txtScoreRating, a value which is calculated from other fields in the subform's recordsource?
If the only thing you need to do is display the value twice, once on the main form and once on the subform, you can do that by referencing the subform control in the main form control's controlsource:

=[YourSubformControlNameGoesHere].[Form]![txtScoreRating]

If, on the other hand, this is an attempt to store the same value in two different tables, we need to step back and evaluate the table design because that is often an indication of problematic table design.

 

[Gasman]

Firstly Dates generally need to be formatted in mm/dd/yyyy or yyyy-mm-dd format, regardless of where you live.
No idea as to why all main records would be updated.

 

[John Lee]

Please clarify a bit.

Why do you want to display the value from the control on the subform in another control on the main form?

Is the value displayed in the control on the subform, txtScoreRating, a value which is calculated from other fields in the subform's recordsource?
If the only thing you need to do is display the value twice, once on the main form and once on the subform, you can do that by referencing the subform control in the main form control's controlsource:

=[YourSubformControlNameGoesHere].[Form]![txtScoreRating]

If, on the other hand, this is an attempt to store the same value in two different tables, we need to step back and evaluate the table design because that is often an indication of problematic table design.

Hi Thank you for your response,

What I am trying to do is get a value from the latest record in the subform and assign that value to the a field in my main form.

The problem is that for some reason access isn't finding my subform which is within the main form and the master and child fields are linked correctly because as I rotate through my records the associated records display as required.

I tried your code above and I keep getting the same message that access can't find the field referred to in my expression, but the field exists in the subform, so I'm still at a bit of a loss as to why it can't find it.

I want the code above to only activate if the primary key in the main form matches the foreign key in the subform

Currently that code runs for each records and populates it with data for the first record rather than for data from the correct subrecord data

Primary Key field is txtSupplierIDMain
Foreign Key field is TxtSupplierIDSub



I would upload the database but its too big .

 

[LarryE]

get a value from the latest record in the subform

You will need to use the DMax function for this in a textbox control on the main form.

 

[John Lee]

You will need to use the DMax function for this in a textbox control on the main form.

Thanks

 

[xavier.batlle]

If you wnat to achieve something similar like this:

There are several ways to achieve it. This is my approach

 

[Ken Sheridan]

I want the code above to only activate if the primary key in the main form matches the foreign key in the subform

If the LinkMasterFields and LinkChildFields properties of the subform control are the names of the primary and foreign keys respectively, that will always be the case. However, you can reference the query directly, and can return the value with an expression as the ControlSource property of an unbound text box in the parent form:

=DLookup("RiskMatScore","qryPreferredSupplierOver10000RiskMatrix","SupplierIDSub = " & SupplierIDMain & " And dtmRateOfReview = #" & Format(DMax("dtmRateOfReview","qryPreferredSupplierOver10000RiskMatrix","SupplierIDSub = " & SupplierIDMain),"yyyy-mm-dd") & "#")

I've assumed that the txt prefixes in your names of the primary and foreign key fields and RiskMatScore are the names of controls bound to columns of a number data type of the same names without the prefixes.

 

[plog]

What I am trying to do is get a value from the latest record in the subform and assign that value to the a field in my main form.

GpGeorge got it right in his first post -- you shouldn't be doing this. You can achieve what you ultimately want, but this is not the way to do it.

You don't store both age and date of birth in a table because age can be calculated off of date of birth. Further, age tomorrow isn't guaranteed to be age today-- its a value that can change and be wrong if not constantly updated. So you store date of birth and calculate age when you need it

Same thing applies here. You don't calculate and store the maximum value of a related table in the main table. You simply calculate that value when you need it.

So, you should build a query to get the max value, then use that query for when you need that value

 

[MajP]

What you were trying highlights several other learning points

1. As mentioned do not store calculated data for many reasons. It is so easy to get out of synch from the true value. You have to make sure every change in the data reflects in the calculation. If you are doing a roll up then every add, delete, and modification of the child records need to be accounted for on every form and possible query.
Instead calculate the data on the fly when you need to display it.
Several of the responses show how to calculate on the fly using information from the form or like Ken shows directly from the table. This will always be in synch (as long as you refresh the display). If you are going to do this calculation in a lot of different places you can wrap Ken's expression in a function

Public Function GetLastValue (SupplierID as long) as Long
  GetLastValue = DLookup("RiskMatScore","qryPreferredSupplierOver10000RiskMatrix","SupplierIDSub = " & SupplierID & " And dtmRateOfReview = #" & Format(DMax("dtmRateOfReview","qryPreferredSupplierOver10000RiskMatrix","SupplierIDSub = " & SupplierID),"yyyy-mm-dd") & "#")
EndFunction

Then in a form, query, calculated control, or code
=GetLastValue([SupplierIDField])

2. You cannot reference a form that is instantiated as a subform through the form's collection, you can only reference it through the parent form
The following would never work

If [Forms]![ frmPreferredSuppliersListOver10000]![Me.txtSupplierIDMain] = [Forms]![ frmPreferredSupplierOver10000RiskMatrix]![txtSupplierSub] Then

You need to do it like this if calling the code from the parent form

If [Forms]![ frmPreferredSuppliersListOver10000]![Me.txtSupplierIDMain] = Me.subFormControlName.Form.txtSupplierSub Then

MainformReference.SubfrmControlName.Form.ControlNameOnSubForm
If you are calling it from somewhere besides the main form the code would be
Forms!MainFormName.SubfrmControlName.Form.ControNameOnSubForm

3.Dates are stored in access as a number regardless of any format that is used for display purpose. For example today's date is stored as the number 45984 and it can be displayed any way you want. When working with literal date values in sql or vba then you are stuck with two formats (because the big software companies reside in USA and got to make the rules early on even though most of the world does not use this format)

1. ANSI/ISO Standard (yyyy-mm-dd) → universally recognized, avoids ambiguity.
2. U.S. Format (mm/dd/yyyy) → widely used in SQL Server and Access, but can cause confusion internationally.