Best way to edit the current record on parent form from a child form ?

[thefarg]

I have a form creating an invoice/order.
Apon double clicking part of the record I am launching another form to edit data in the current record on the parent form (details that may or may not be added on a standard order).
I think I can use

Me.Recordset = Me.parent.recordset

but was wondering if I could use something in the recordsource so that I can bind it. I'd like to be able to attach the controls to the fields via the property editor. If not, can I just use

cboMyCombo.controlsource = [Field In Underlying Recordsource]

?
Thanks
Mike

 

[TheAceMan1]

How are ya the frag . . .

Your editing the record of the mainform in a secondary form because obviously all the fields to be edited are not contained in the parent form or else you would edit directly. What you need to do is goto the same record in the secondary. My 1st shot at this uses the primarykey of the mainform. The secondary for need to be based on a query with a [blue]WHERE[/blue] clause like the following. Note: you substitute proper names in [purple]purple[/purple]:

[blue]If PrimaryKeyName is text datatype:
   "WHERE [[purple][b]PrimaryKeyName[/b][/purple]] = '" & [Forms]![[purple][b]ParentFormName[/b][/purple]]![[purple][b]PrimaryKeyName[/b][/purple]] & "';"

If PrimaryKeyName is Numeric:
   "WHERE [[purple][b]PrimaryKeyName[/b][/purple]] = " & [Forms]![[purple][b]ParentFormName[/b][/purple]]![[purple][b]PrimaryKeyName[/b][/purple]] & ";"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

frag . . .

In the [blue]On Close[/blue] event of the secondary form you'll need:

[blue]If primarykey is text:
   Dim frm As Form, hldID As String
   
   Set frm = Forms![[purple][b]ParentFormName[/b][/purple]]
   hldID = frm![[purple][b]PrimaryKeyName[/b][/purple]]
   frm.Requery
   frm.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = '" & hldID & "'"
   set frm = nothing

If primarykey is numeric:
   Dim frm As Form, hldID As Long
   
   Set frm = Forms![[purple][b]ParentFormName[/b][/purple]]
   hldID = frm![[purple][b]PrimaryKeyName[/b][/purple]]
   frm.Requery
   frm.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = " & hldID[/blue]

This sets the focus to the newly added record in the parent form ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[thefarg]

This query based on

"WHERE [PrimaryKeyName] = " & [Forms]![ParentFormName]![PrimaryKeyName] & ";"

.
Are you saying to create a query or have subquery string as the recordsouce property to the child form?
In the select part of the query, what am I querying, the original table/query apon which the parent form is based or can I query tthe form itself?

 

[thefarg]

Sorry, but the recordset is actually in a subform of the main form.
How do I write that for the sql?

I think this is the path for the subform

Forms!frmOrderMaker.frmOrderMaker_OrderSubform.Form

 

[thefarg]

Cant get your code to work as you gave me incomplete SQL statement.
Is is feasible to use something like this

Private Sub Form_Open(Cancel As Integer)
Me.Recordset = Forms.frmOrderMaker.frmOrderMaker_OrderSubform.Recordset
TstMsg (Me.Recordset.RecordCount)
Me.txtJobDate.ControlSource = [Job Date]
End Sub

(not exactly though as its giving me an application defined error)
I would prefer to use SQL in the controlsource of the form if possible, and I've tried querying the form itself. None of the FAQ's seem to fit my position, so if you think you have an answer, please do

Thanks,
Mike

 

[TheAceMan1]

thefarg . . .

I'm with you I'm just mixed up with the forms. Are you saying your double clicking a subform (we'll call it [purple]subform1[/purple]) which opens another form for editing the parent record of [purple]subform1[/purple]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[thefarg]

not quite
I have the main form, MainForm
Embedded in it is a subform, say SubForm1
Double clicking on a field in SubForm1 brings up a third form as a popup, ChildForm1
I guess that ChildForm1 is actually a child of Subform1 .
If possible I would like to have a subquery in the recordsource property forChildform1 so I get the list of fields in the property editor for aech control on the form. Is this possible? I have looked at a number of faqs, but none appear to be on this topic. Any ideas?

 

[TheAceMan1]

thefarg . . .

Note: if a form is not embedded on another form its an [blue]independent form[/blue].

Make a query (using the query pane) that includes only the necessary fields along with the primarykey field. You then use the query name as the [blue]recordsource[/blue] for the popup. Then I'll need the following:
[ol][li] The SQL of the query. In query design view, on the far left, dropdown the [blue]view[/blue] listing and select [blue]SQL View[/blue]. Copy/paste what you see in your next post.[/li]
[li]The form names.[/li]
[li]The primarykey names of each form and datatype string/numeric.[/li]
[li]Of the fields you've selected in the query, their names and datatypes.[/li][/ol]
You could also upload a scaled down version of the db to someplace like 4Shared. Its free. I'm using 2003 ... so if you have a sucessor version you'll to convert to 2003 format.


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[thefarg]

OK, I'll do that.
What I did try was to have the Independant popup recordsource as the same query that the Subform is based off. The problem is that when I open the popup, the record being edited on the Subform is not actually created in the table yet. How can I save the record before opening the popup?

 

[thefarg]

If I finish editing the record, then open the popup, It will allow me to choose listboxes, datepickers and combos. Trying to type into a text box comes up with the error
"Cannot enter value into a blank field on 'one' side of an outer join"
If I change the join to anthing else, no records show in the query.

SELECT [Order Details].*, [Orders Status].[Status Name], Orders.[Status ID], CCur([Discounted Price]+(([Discounted Price]/100)*[GST])) AS [Extended Price], CCur([Unit Price]-((([Unit Price]/100)*100)*[Discount Percentage])) AS [Discounted Price], tblProperties.*
FROM tblProperties RIGHT JOIN (([Order Details] INNER JOIN Orders ON [Order Details].[Order ID] = Orders.[Order ID]) INNER JOIN [Orders Status] ON Orders.[Status ID] = [Orders Status].[Status ID]) ON tblProperties.[Property ID] = [Order Details].[Job Property ID];

is the current SQL for my query. Any ideas?

 

[thefarg]

OK. I can save the record on the subform before opening the popup with this code -

Dim rst As Recordset

Set rst = Me.Recordset
With rst
        .Edit
        ![Product ID] = Me.[Product ID]
        ![Unit Price] = Me.[Unit Price]
        ![Discount Percentage] = Me.[Discount]
        '![Extended Price] = Me.[txtExtended Price]
        ![Status Name] = Me.[Status Name]
        .Update
End With

Dim varWhereClause As String
varWhereClause = "[Order ID] = " & Me![Order ID]
DoCmd.OpenForm "frmOrderMaker_DetailsSubform", , , varWhereClause

However I still have the error while editing text fileds. Anything using datepicker, combo etc is fine.
Any help with this final paert will be most appreciated.

 

[TheAceMan1]

thefarg . . .

The SQL you've shown has thrown me into complete disarray. So I don't waste any more of your time ... I suggest you start a new thread with your origional question ... perhaps someone else can see what I don't. In the meantime I'll make the best sense out of it I can. If I'm successful I'll be sure to let you know.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[thefarg]

Cheers, will do. The SQL there is essentially [Order Details Extended] from the Northwind 2007 with an extra table added.