Update record in Subform by another Form

[MichealDon]

Hi All,
I need some help with a update record on Subform by another Form.
My Database have 3 Forms:
+ MainForm : with record source from TableMain (Fields : ID_Case, Date, Name)
(Primary Key : ID_Case (data type :text)
+ SubForm : with record source from TableSub (Fields : ID_Case, ID, Details,...)
- Default View : "Continuous forms"
- Primary Key Field : ID (data type :auto number)
(Link Master Fields / Link Child Fields : ID_Case (link between Main/Sub forms))
+ UpdateForm is the same secord source to SubForm, but default view is "Single Form"

On the event, after update the control "Details" on SubForm for the current record, the form "UpdateForm" will be opened with the [ID] on the "UpdateForm" is same [ID] on "SubForm".

I tried to set the Property Sheet of UpdateForm with :
- Filter : [ID] = [Forms}![MainForm]![SubForm]![ID]
or [ID] = [Forms]![SubForm]![ID]
- Filter on load : yes

But I could not make it as my expectation.
I am sorry for my post whether that is duplicated to another post.

Thank you for your help.
MichealDon

 

[mike60smart]

Hi

Can you upload a zipped copy of the Db?

 

[Gasman]

Shouldn't that be

[SubForm]!Form![ID]

http://access.mvps.org/access/forms/frm0031.htm

 

[MajP]

No. Needs to be

.Form

. Form is a property not a member of a collection. But yes it has to be

Forms!FormName.SubformControlName.form.ControlOnSubFormName

or

Me.SubformControlName.Form.ControlOnSubformName

if called from the current form reference
You can use ! between form name and control name since they are members of a collection

 

[MajP]

ID = Forms!MainForm!SubFormConTrolName.Form!ID

But I think ID_Case is the field in the subform not ID

 

[MichealDon]

Hi

Can you upload a zipped copy of the Db?

Thank all for your help.
I upload my Database
When I make new record on SubForm "F_SubDefect" by update the Field "DefectType". After update , the Form "F_DefectDetails" will be open, with the current record on the Form "F_DefectDetails" is same the record which have been just created on the SubForm.
For example :
The record in SubForm with "ID" filed value is 38 must be same the current record on Form Single view with ID.value =38.
Thank you for your help.
Best Regards,
MichealDon

 

[mike60smart]

Hi MichaelDon

There is no need for the additional Popup Form

You had not created a true Parent to Child Relationship between your 2 tables.

See the Modified db attached.

View attachment Defect Case.zip

 

[MichealDon]

Dear Mike,
Thank you very much for modifiing the database.
This is the Demo database to ask you for my learn to understand relationship between subform to other form . I need to open (popup) the other SingleForm to input more data on other controls. It is better for arrangement of controls on only one form than using continuous form that has to input data on long way for many controls in each record. Moreover, I need to code for some thing behind.
I am trying to do some code, but now I still can not solve it.

I want to thank all of you for your support .
Thank you again.
MichealDon

 

[bdra2778]

Hello MichealDon, I modifated mike60smart file, I hope this is what you looking for.
Regards

 

[mike60smart]

Hi bdra

Your modified version now creates orphaned records in the subform.


The foreign key "OpenCaseID" is now blank

 

[bdra2778]

Hi bdra
Your modified version now creates orphaned records in the subform.

The foreign key "OpenCaseID" is now blank

I know, I only show to MichealDon a way to get that he is looking for. This that you expose, He have to validate and solved.

MichealDon write:

I need to open (popup) the other SingleForm to input more data on other controls. It is better for arrangement of controls on only one form than using continuous form that has to input data on long way for many controls in each record. Moreover, I need to code for some thing behind.

 

[mike60smart]

Hi MichaelDon

Why not make your subform a SingleForm then?

Much easier to work with a single record.

You would then just add navigation buttons to add New or navigate previous records.

 

[MichealDon]

Dear All,
First, I want to thank all of you looking at my topic, make me more ideas, tell me more useful access web and help me for solving the first difficult issue of my database

With the version that Bdra2778 have modified to make me to do it easily.
Just only coding some at the event "txtDefect Afterupdated":

Code :
Private Sub txtDefect_AfterUpdate()
myDetailsId = Me.DetailsID
Me.OpenCaseID = Forms!F_OpenCase!OpenCaseID
If Not (IsNull(myDetailsId) Or IsEmpty(myDetailsId)) Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "F_DefectDetails"
Else
MsgBox "Not data in DetailsId"
Exit Sub
End If
End Sub

So I can make the relationship of ControlName "OpenCaseID" between two Main/Sub forms. (highlight in red)
Thank you again.
Best Regards,
MichealDon

 

[mike60smart]

Hi MichaelDon

Well all I can say is that all of the code is absolutely NOT necessary.

All you have is a Main Form / Sub Form so when you need to add a record using the Subform all you need is to make the subform a Single Form View.

Luck with your project