Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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

thefarg (Programmer) (OP)
28 Jan 12 20:38
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

CODE

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

CODE

cboMyCombo.controlsource = [Field In Underlying Recordsource]
?
 Thanks
       Mike
TheAceMan1 (Programmer)
28 Jan 12 21:00
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 WHERE clause like the following. Note: you substitute proper names in purple:

CODE

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

If PrimaryKeyName is Numeric:
   "WHERE [PrimaryKeyName] = " & [Forms]![ParentFormName]![PrimaryKeyName] & ";"
Your Thoughts? . . .

See Ya! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

TheAceMan1 (Programmer)
28 Jan 12 21:23
frag . . .

In the On Close event of the secondary form you'll need:

CODE

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

If primarykey is numeric:
   Dim frm As Form, hldID As Long
   
   Set frm = Forms![ParentFormName]
   hldID = frm![PrimaryKeyName]
   frm.Requery
   frm.Recordset.FindFirst "[PrimaryKeyName] = " & hldID
This sets the focus to the newly added record in the parent form ...

Your Thoughts? . . .

See Ya! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

thefarg (Programmer) (OP)
28 Jan 12 21:59
This query based on

CODE

"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 (Programmer) (OP)
28 Jan 12 22:20
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 (Programmer) (OP)
28 Jan 12 22:47
Cant get your code to work as you gave me incomplete SQL statement.
Is is feasible to use something like this

CODE

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 (Programmer)
29 Jan 12 1:11
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 subform1) which opens another form for editing the parent record of subform1?

Your Thoughts? . . .

See Ya! .  .  .  .  .  .

Be sure to see FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

thefarg (Programmer) (OP)
29 Jan 12 1:20
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 (Programmer)
29 Jan 12 9:44
thefarg . . .

Note: if a form is not embedded on another form its an independent form.

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 recordsource for the popup. Then I'll need the following:
  1. The SQL of the query. In query design view, on the far left, dropdown the view listing and select SQL View. Copy/paste what you see in your next post.
  2. The form names.
  3. The primarykey names of each form and datatype string/numeric.
  4. Of the fields you've selected in the query, their names and datatypes.
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: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

thefarg (Programmer) (OP)
29 Jan 12 15:19
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 (Programmer) (OP)
29 Jan 12 15:28
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.

CODE


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 (Programmer) (OP)
29 Jan 12 16:12
OK. I can save the record on the subform before opening the popup with this code -

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 (Programmer)
29 Jan 12 18:19
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: How Do I Get Great Answers To my Tek-Tips Questions?  Worthy Reading! thumbsup2
Also FAQ181-2886: How can I maximize my chances of getting an answer?  Worthy Reading! thumbsup2

thefarg (Programmer) (OP)
29 Jan 12 19:48
Cheers, will do. The SQL there is essentially [Order Details Extended] from the Northwind 2007 with an extra table added.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close