displaying new record on a form after adding record with popup form

[cahigg]

I have a form with two nested subforms. On the first subform, there is an "Add New" button that will add a new record to the first child form and records to the child's child form. I need to requery to main for to include the new records, but I can't figure out how to set the record selector to the newly added record after I requery. Here is the basic design:

Facility name & address - main form
violations at facility - first child
codes and dates associated with violations - 2nd child

when I add a new violation for a facility through a separate form, 6 codes/dates are added to the 2nd child and the "Add New" form is closed. I requery the parent form but the record displayed is reset to the first parent record. I want the new violation to be displayed along with the parent facility info and the codes and dates. Any help would be appreciated.

 

[CautionMP]

Do you need to requery the main form or the children?

A quick way to answer this is go through the Add New routine. When it's done, click on the subform and hit Shift+F9 (requery). If this does what you want then want to requery the child form and not the main form.

If that dosen't do what you want, then click on the main form and try Records=>Refresh, if this does what you want then you only need to Refresh the main form.

If neither of these work then you can set the main forms filter to the criteria of the current record, apply the filter, then requery the main form. (NOTE: This will disable the ability for the user to scroll through the Facilities using the navigation buttons.)

 

[Zion7]

Dim v As Variant
v = Forms!frmSolarSystem.pkPlanetID
Forms!frmSolarSystem.Requery
Forms!frmSolarSystem.SetFocus
Forms!frmSolarSystem.pkPlanetID.SetFocus
DoCmd.FindRecord v


Can't you use Refresh instead?

 

[TheAceMan1]

How are ya cahigg . . . . .

Need to be sure of the subform levels (affects referencing).

[tt]MainForm with subForm [purple]Level1[/purple]
Level1 subform with subform [purple]Level2[/purple]
Level2 subform with subform [purple]Level3[/purple][/tt]

[blue]What are the names and levels of the forms?[/blue]

See Ya! . . . . . .

 

[cahigg]

Thanks for the help all.
Here are the form names:
frmVNTracking - main form with subform
subfrmVN - subform on frmVNTracking with subform
tabVNactions - subform on subfrmVN (actually there are three tabs each with a subform)

the popup form is frmAddNewVN


I used CautionMP's suggestion and found that requery on subfrmVN keeps the form on the correct facility but moves to the first record on subfrmVN instead of the newly added record. Refresh doesn't show the new record.

Zion7 has me on the right track, but I need to requery the first subform (subfrmVN) and then set the focus to the control vn_number on subfrmVN and then find the vn_number that was created by the popup frmAddNewVN which I saved to variable strNewVNNumber

I don't program often enough and even though I know how I want things to flow, it's the syntax that eats up the time.

Thanks again

 

[cahigg]

One more thing, where do I put the code to requery, setfocus,etc.? Do I put it in the popup (frmAddNewVN) form that creates the new records (which is an on_click event for the Save button that creates the new vn_number, adds related records to the vn_actions table and then closes frmAddNewVN)? Or does it go under one of the events on subfrmVN or frmVNTracking? I'll try a few things today.

Thanks again.

 

[TheAceMan1]

cahigg . . . . .

The following code assumes the forms [blue]frmVNTracking[/blue] & [blue]subfrmVN[/blue] are linked with a [blue]common field[/blue], and that feld is [blue]numeric data type[/blue]. If something is different you'll have to notify as code needs to be changed accordingly.

In the [blue]CloseEvent[/blue] of [blue]frmAddNewVN[/blue], copy paste the following code ([blue]You![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim frm As Form, rst As DAO.Recordset
   Dim IDname As String, ID As Long
   
   IDname = "[purple][b]LinkingFieldNameInVNTracking[/b][/purple]"
   Set frm = Forms!frmVNTracking
   ID = frm(IDname) [green]'Hold current ID for lookup[/green]
   frm.Requery [green]'Requery VNTracking[/green]
   
   Set rst = frm.RecordsetClone
   rst.FindFirst IDname & " = " & ID [green]'Lookup ID[/green]
   Me.Bookmark = rst.Bookmark [green]'View record with ID[/green]
   
   Set rst = Nothing
   Set frm = Nothing[/blue]

If you get an error and the debugger stops on [purple]rst As DAO.Recordset[/purple]:

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK, close out & try again.

[blue]Give it a whirl & let me know . . .[/blue]

See Ya! . . . . . .

 

[Zion7]

Either AceMan's method or Docmd.FindRecord, will find the record you desire.

But, in regards to you 2 last posts....

from the "Save" cmdButton, on the popUp form "frmAddNewVN", you add this code, at the end.

Dim v As Variant
v = Forms!frmVNTracking!pkID
Forms!frmVNTracking.Requery
Forms!frmVNTracking.SetFocus
Forms!frmVNTracking!pkID.SetFocus
DoCmd.FindRecord v
Forms!frmVNTracking!subfrmVN.SetFocus
Forms!frmVNTracking!subfrmVN.Form!vn_number.SetFocus
DoCmd.FindRecord strNewVNNumber

 

[cahigg]

Thanks alot! I went with the DoCmd option as I was having trouble using recordsets on another part of the app (now I know why: DAO wasn't installed!!!) I got it working the way it should and I also used similar code to allow users to search for a VN number across all facilities in the parent form as well! I know where to go if I need help. I'll have to check in and see if I can contribute any of my knowledge to payback the tek-tips community.

Thanks again,