Solved

Open child form to new record and populate fields based on parent form

Posted on 2010-09-20
19
936 Views
Last Modified: 2013-11-28
Scenario:
frmSOP display information on SOPs that staff must be trained on
frmEvent is used to schedule training events for specific SOPs
User clicks "Schedule Event" button on frmSOP and frmEvent should open to a new record with the title of the Event prepopulated with the SOP name of the parent form.

The On_Click event for the frmSOP "Schedule Event" button is:
    DoCmd.OpenForm "frmEvent", acNormal, , , acFormAdd
The Form_Load event for the frmEvent is:
    Me.TrnId = Forms!frmSOP.TrnId

I get a message "You can't assign a value to this object"
0
Comment
Question by:NashVegas
  • 10
  • 4
  • 2
  • +2
19 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 33719273

use this command line
 DoCmd.OpenForm "frmEvent", acNormal, , , acFormAdd, , OpenArgs:=Me.TrnId


in the load event of frmEvent

private sub form_load()
if len(me.openargs & "")>0 then
  if me.newrecord then
     me.trnid=me.openargs
 end if
end if

end sub
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33719289
NashVegas,

Try to put the code in the On Current event of the form.

Sincerely,

Ed
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33719299
btw, you mentioned, child form, is frmevent a subform of form frmSOP?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 10

Expert Comment

by:t_hungate
ID: 33719444
Try putting a new field on your form txtTrngID and set its row source to =[Forms]![frmSOP].TrnId.

then use
DoCmd.OpenFrom "frmEvent", acNormal,,,acFormAdd

When you open the form you should see the correct TrnID in the new field.

Now try to set the value of your field.
Private Sub form_load()
Me.TrnId = Me.txtTrngID
End Sub

TLH
0
 

Author Comment

by:NashVegas
ID: 33719473
Capricorn,

frmEvent is not a subform. I used child form to mean it was called into existence from the frmSOP form. When I step through your code, Me.newrecord = 0 so it skips the openargs statement. Was this what you expected?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33719679
it  should be Me.newrecord = -1

check if the form frmEvent Allow additions property is set to YES
0
 

Author Comment

by:NashVegas
ID: 33719755
Capricorn and TLH,

I still get the "Can't assign values" error trying either of your suggestions.

Ed,

The "On Current" event doesn't execute when the form loads. I put breakpoints in the code and the breakpoints are never addressed if all I do is open the form with the "Schedule Event" button.

John
0
 

Author Comment

by:NashVegas
ID: 33719766
Cap,

Yes the allow additions property is set to Yes for frmEvents.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33720006
can you upload a copy of your db..

do a compact and repair first
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33720389
You could create a new record in the child form's record source, populating the field from the value on the calling form, then open the child form to the last record.
0
 

Author Comment

by:NashVegas
ID: 33721621
Cap,

I have it uploaded. From the main menu, select SOP Profile and then click "Schedule Event" to open the Event Details form. Thanks for taking a look!
0
 
LVL 10

Assisted Solution

by:t_hungate
t_hungate earned 250 total points
ID: 33722019
Alright, I had to dig through here to see what all you had going on.  I think I narrowed it down to the recordset type for your event details form was a set to Snapshot, which will not allow edits.  I changed that, and still had some issues setting the value, so I use an on got focus event to do it.  

That took care of it.  All seems to be working now.

NOTES:
I also changed the record source to your tbl instead of the query you had, so you could include the trainers email address.  You should be able to change that back now.

I also noticed that you did not have anyone in your empl table assigned as trainers, the the combobox to select a trainer was not populated.  I just checked that box on a few people to make sure that worked as it should.

Hope this is what you were looking for.

TLH
0
 

Author Comment

by:NashVegas
ID: 33728983
TLH,

Thanks for your help! This got me closer in some ways, but in others it is creating more problems. It creates 2 records on the tblEvt table and neither record gets updated with the trainer or attendee list after the form is saved and closed. The second record gets created when the DoCmd.GoToRecord , , acNewRec code executes. This happens before the user has any opportunity to interact with the form.

Helen,

I made an attempt at interpreting your suggestion. I tried setting the child form's recordsource using an INSERT INTO statement, but I got an error message that this kind of SQL statement is not allowed to be used as a "row source". How do I create a new record in the child form's record source?

Thanks!
John
0
 

Author Comment

by:NashVegas
ID: 33729583
TLH,

Correction: I went back to your original code and noted that the 2nd of the pair of records that gets created every time the "Schedule Event" button is clicked does save the attendee, trainer, etc. If we can figure out how to prevent the first record from getting created we'll be laughing.

Cheers!
0
 

Author Comment

by:NashVegas
ID: 33735246
Okay, I've got it working now. I changed the tblEvts.TrnId field to display the traning name rather than the trnID value (although it is still bound to the trnID column). This simplified matters somewhat. I set the form's recordset type to dynaset and then added the "DoCmd.GoToRecord , , acNewRec" code to the child form's load event. This event also handles pre-populating the attendees based on training deficiencies. The form opens and is editable and all the data saves on the tblEvt table and only one record per event is created. If you open the Event Detail form on its own (rather than from the SOP Profile form) then the form shows all training events and is editable. All this is working as I intended.

I am not exactly sure what the silver bullet was that fixed my issue, but I am attaching the working prototype if anyone is interested. I'll split the points since several suggestions were helpful. Thanks!
0
 

Author Comment

by:NashVegas
ID: 33748511
This file replaces the file attached to comment 33721621

ViewTrainLite.accdb
0
 

Author Comment

by:NashVegas
ID: 33748629
This file replaces the file attached to comment 33722019
ViewTrain---TLH.accdb
0
 

Author Comment

by:NashVegas
ID: 33748646
This file replaces the file attached to comment 33735246
ViewTrainLite-2.accdb
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 40
Normalization of a table 19 74
How to get the closest date in a query in Access 2010 8 22
access query to sql server 3 20
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question