Go to last record input on underlying subform.

[Moss100]

Hello all,

I have an unbound for which has two subforms on it.

Subform1 displays a list of names.
Subform2 contains details related to each name.

I have a button which is used to add a new record (this opens up a pop up form (which is subform2)).

Once added I would like the newly added record to be selected in Subform1, but am unsure of how to do this.

Firstly I thought I would just tell the form to be filtered by going to last record, but of course as the subform1 is filtered by name, this just brings up Z....

The unique ID in subform 1 is APP_ID

So basically after adding the new record I want Subform1 to be on the related APP_ID that I have just added.

Helppppp!!!! Thanks Mark

 

[MajP]

Many ways to do this. I always make my pop up forms dialog.

On the pop up form I have an Ok and Cancel button.
The cancel button does an undo and closes the form
The OK button hides the form but does not close it.

Then I use this function contained in a standard module.

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
  'FormName: Name of the popup form
  'PopupControlName: Name of the control on the pop up/dialog that you want the value
  Dim frm As Access.Form
  DoCmd.OpenForm formName, , , , acFormEdit, acDialog
  'wait until form is closed or hidden
  'The popup needs an OK button that hides the popup(me.visible = false), and a Cancel button that just closes it
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    getValueFromPopUp = frm.Controls(PopUpControlName).Value
    DoCmd.Close acForm, formName
  End If
End Function

I can get any value from any popup form by the name of the form and name of the control.

So the code on the main form would be something like

dim newPK as variant
'call the popup. Execution stops.
newPK = getValueFromPopUp("MyPopUp","txtAPP_ID")
'Now you have the new App_ID if you did not cancel
'Requery and move to the new ID.
if not isnull(newPK) then
  me.requery
  me.recordset.movefirst "App_ID = " & newPK
end if