Opening forms with fields pre-filled (1 Viewer)

[mikenyby]

Hello Access Wizards!

I have a fairly simple thing I would like to do with a couple of forms. I have an entry form linked to one table that has a button that opens a pop-up form if the user needs to add a field to a related table. When the pop-up form is closed, the earlier entry form reloads to the last record entered (Order By TimeStamp DESC) so the user doesn't lose their place. I would like a combo box on the re-opened form to be prefilled with the record just entered on the pop-up form. Here's a little more context.
The related tables in question:

The entry form is for the Persons table. It is called EnterNewPerson. It looks like this:

The "Add New" button next to associated institution opens this pop-up form named EnterNewInstitution:

The submit button closes the pop-up form, closes the Persons entry form underneath it, and reopens a very similar form called EnterNewPersonLASTRECORD which is just the EnterNewPerson form but is Ordered By TimeStamp descending so the record the user was working on is reloaded. Here is the event:

What I would like to do is have the EnterNewPersonLASTRECORD form open up with the value that was just entered into the EnterNewInstitution form's Institution Name field (which in the Institution table is simply "Institution") prefilled in the Associated Institution combo box. Is this possible? I have tried futzing around with TimeStamp DESC criteria in the form's source query and tried a Where condition in the event pictured above, but none of my attempts have worked out.

 

[theDBguy]

Hi. Sorry, I didn't really read your entire post, but if you want to know the value from one form to be used on another, there are several approaches to get that done. For exampel, you can store the values to TempVars before closing the form, or you can simply hide the form instead of closign it and then use absolute references to get those values.

 

[moke123]

As DBG states there are numerous ways to do this, but using Macros you are somewhat limited ( At least to me as I never use macros)
You should not need more than the 2 main forms to accomplish this.
You could also use the combobox NotInList event to open a form and enter the new institution. Google it.

Here's an example using withevents that's relatively simple.

 

[mikenyby]

As DBG states there are numerous ways to do this, but using Macros you are somewhat limited ( At least to me as I never use macros)
You should not need more than the 2 main forms to accomplish this.
You could also use the combobox NotInList event to open a form and enter the new institution. Google it.

Here's an example using withevents that's relatively simple.

Thank you moke123, this seems like an elegant solution, and it works perfectly on your example. However, I tried it on my database and I get this error message:

Here's the code for my EnterNewPerson form. I converted the macros to VBA.

Option Compare Database

'------------------------------------------------------------
' SubmitEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub SubmitEnterNewPerson_Click()
On Error GoTo SubmitEnterNewPerson_Click_Err

    DoCmd.Close , ""


SubmitEnterNewPerson_Click_Exit:
    Exit Sub

SubmitEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume SubmitEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' CancelEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub CancelEnterNewPerson_Click()
On Error GoTo CancelEnterNewPerson_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="SubmitEnterNewPerson" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterf
    ' _AXL:aceMacro>
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close , ""


CancelEnterNewPerson_Click_Exit:
    Exit Sub

CancelEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume CancelEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' Command6_Click
'
'------------------------------------------------------------
Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="CancelEnterNewPerson" Event="OnClick" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="UndoRecord"/><Action Na
    ' _AXL:me="CloseWindow"><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro>
    DoCmd.OpenForm "EnterNewInstitution", acNormal, "", "", , acDialog
     Set frmNewInst = Forms("EnterNewInstitution")
     frmNewInst.OnClose = "[Event Procedure]"

Command6_Click_Exit:
    Exit Sub

Command6_Click_Err:
    MsgBox Error$
    Resume Command6_Click_Exit

End Sub

Private Sub frmNewInst_Close()
    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID
End Sub

 

[moke123]

you forgot to put the form variable in the declarations.

you should also have Option explicit in ALL your code modules

Option Compare Database
Option Explicit

Dim WithEvents frmNewInst As Access.Form   'a form variable with the withevents keyword

 

[moke123]

I just noticed an error I made in my example. I left out setting the form variable to nothing. OOPs.

Private Sub frmNewInst_Close()

    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID

    Set frmNewInst = nothing                                              '<<<<<<<dont forget to add this line.

End Sub

 

[mikenyby]

Thanks for catching that. Added it but still get the same error message. Here's the code now:

Option Compare Database
Option Explicit

Dim WithEvents frmNewInst As Access.Form   'a form variable with the withevents keyword

'------------------------------------------------------------
' SubmitEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub SubmitEnterNewPerson_Click()
On Error GoTo SubmitEnterNewPerson_Click_Err

    DoCmd.Close , ""


SubmitEnterNewPerson_Click_Exit:
    Exit Sub

SubmitEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume SubmitEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' CancelEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub CancelEnterNewPerson_Click()
On Error GoTo CancelEnterNewPerson_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="SubmitEnterNewPerson" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterf
    ' _AXL:aceMacro>
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close , ""


CancelEnterNewPerson_Click_Exit:
    Exit Sub

CancelEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume CancelEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' Command6_Click
'
'------------------------------------------------------------
Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="CancelEnterNewPerson" Event="OnClick" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="UndoRecord"/><Action Na
    ' _AXL:me="CloseWindow"><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro>
    DoCmd.OpenForm "EnterNewInstitution", acNormal, "", "", , acDialog
     Set frmNewInst = Forms("EnterNewInstitution")
     frmNewInst.OnClose = "[Event Procedure]"

Command6_Click_Exit:
    Exit Sub

Command6_Click_Err:
    MsgBox Error$
    Resume Command6_Click_Exit

End Sub

Private Sub frmNewInst_Close()
    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID
End Sub

 

[mikenyby]

I just noticed an error I made in my example. I left out setting the form variable to nothing. OOPs.

Private Sub frmNewInst_Close()

    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID

    Set frmNewInst = nothing                                              '<<<<<<<dont forget to add this line.

End Sub

Added Set frmNewInst = nothing to the code. Still the same error message.

Complete code below:

Option Compare Database
Option Explicit

Dim WithEvents frmNewInst As Access.Form   'a form variable with the withevents keyword

'------------------------------------------------------------
' SubmitEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub SubmitEnterNewPerson_Click()
On Error GoTo SubmitEnterNewPerson_Click_Err

    DoCmd.Close , ""


SubmitEnterNewPerson_Click_Exit:
    Exit Sub

SubmitEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume SubmitEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' CancelEnterNewPerson_Click
'
'------------------------------------------------------------
Private Sub CancelEnterNewPerson_Click()
On Error GoTo CancelEnterNewPerson_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="SubmitEnterNewPerson" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterf
    ' _AXL:aceMacro>
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close , ""


CancelEnterNewPerson_Click_Exit:
    Exit Sub

CancelEnterNewPerson_Click_Err:
    MsgBox Error$
    Resume CancelEnterNewPerson_Click_Exit

End Sub


'------------------------------------------------------------
' Command6_Click
'
'------------------------------------------------------------
Private Sub Command6_Click()
On Error GoTo Command6_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="CancelEnterNewPerson" Event="OnClick" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="UndoRecord"/><Action Na
    ' _AXL:me="CloseWindow"><Argument Name="Save">No</Argument></Action></Statements></UserInterfaceMacro>
    DoCmd.OpenForm "EnterNewInstitution", acNormal, "", "", , acDialog
     Set frmNewInst = Forms("EnterNewInstitution")
     frmNewInst.OnClose = "[Event Procedure]"

Command6_Click_Exit:
    Exit Sub

Command6_Click_Err:
    MsgBox Error$
    Resume Command6_Click_Exit

End Sub

Private Sub frmNewInst_Close()
    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID
    
    Set frmNewInst = Nothing
End Sub

 

[moke123]

Make sure you are using the correct object names (form name and combo box name.)
This is your code cleaned up a little and error handlers removed. You should be able to just copy and paste into your form module.
Make sure it compiles. (debug>Compile database in the VBE)

Option Compare Database
Option Explicit

Dim WithEvents frmNewInst As Access.Form   'a form variable with the withevents keyword

Private Sub SubmitEnterNewPerson_Click()

    DoCmd.Close acForm, Me.Name                            ' be explicit closing the form

End Sub

Private Sub CancelEnterNewPerson_Click()

    DoCmd.RunCommand acCmdUndo
    DoCmd.Close acForm, Me.Name

End Sub

Private Sub Command6_Click()

    DoCmd.OpenForm "EnterNewInstitution", , , , acFormAdd          'since you're adding a record, open the form in add mode
 
     Set frmNewInst = Forms("EnterNewInstitution")
 
     frmNewInst.OnClose = "[Event Procedure]"

End Sub

Private Sub frmNewInst_Close()

    Me.cboInstitutionID.Requery
    Me.cboInstitutionID = frmNewInst.InstitutionID
 
    Set frmNewInst = Nothing
 
End Sub

edit: here's another example using your form and table names

 

[Pat Hartman]

Get rid of the macro. Use VBA instead. Two lines of code in the click event of the AddNew button.

DoCmd.OpenForm, "EnterNewInstitution",,,,,acDialog
Me.NameOfYourCombo.Requery

You open the form in dialog mode. That stops code execution in the calling program AND limits focus to the popup form. You can't go back to the opening form until you close the popup.
Then when the popup closes, control returns to the calling form and the requery of the combo includes the new institution in the list.

 

[mikenyby]

Got it! Thank you, @moke123. The issue was the combobox name. Renamed the combobox to InstitutionID and now it works like a charm.

Thank you for your patience with me. Up until now I've managed to achieve everything my databases need using macros, but clearly it's time I get proficient in VBA.

I love this forum.

 

[moke123]

Congrats. Glad to see you got it sorted out.

The issue was the combobox name.

That's why it is important to include Option Explicit in all your code modules. It will point out any misspelling or other errors.

but clearly it's time I get proficient in VBA.

You'll find it opens up a whole new world of what you can do. Your already a little bit ahead of the game if you understand how that code works.

You may want to take a look at this recent thread and check out some of the links included.

Event driven programming

 

[mikenyby]

Congrats. Glad to see you got it sorted out.


That's why it is important to include Option Explicit in all your code modules. It will point out any misspelling or other errors.


You'll find it opens up a whole new world of what you can do. Your already a little bit ahead of the game if you understand how that code works.

You may want to take a look at this recent thread and check out some of the links included.

Event driven programming

I wonder if you can help me do the same thing on a different form in the same database. I'm using the same code as the Institution form on a form in which the user can add new persons. As far as I can tell, the code syntax all looks fine, but when I try to add a new person I get an error message:

The debugger shows the problem here:

Here is the code I have for this form:

Option Explicit
Dim WithEvents frmNewPers As Access.Form

Private Sub cboNameSelect_KeyDown(KeyCode As Integer, Shift As Integer)
Me.cboNameSelect.Dropdown
End Sub

Private Sub cmdAddNewPerson_Click()
Me.txtName.Undo
Me.cboNameSelect.Undo
DoCmd.OpenForm "EnterNewPerson", , , , acFormAdd, acDialog
Set frmNewPers = Forms("EnterNewPerson")
frmNewPers.OnClose = "[Event Procedure]"
Me.cboNameSelect.Requery
End Sub

Private Sub Form_Current()
Me.cmdAddNewPerson.SetFocus
End Sub

Private Sub txtName_Enter()
Me.cboNameSelect.SetFocus
End Sub

Private Sub frmNewPers_Close()
Me.PersonIDFK.Requery
Me.PersonIDFK = frmNewPers.PersonID
End Sub

 

[moke123]

Set the form to modal in the form properties and drop the acDialog. See what happens.

 

[MajP]

DoCmd.OpenForm "EnterNewPerson", , , , acFormAdd, acDialog
Set frmNewPers = Forms("EnterNewPerson")

The ACDIALOG argument does something more than just opening the form Modal and Popup. It stops code execution in the calling routine until the called form is closed. The second line does not start running until the called form is closed. Therefore when you close form EnterNewPerson the line below then runs
Set frmNewPers = Forms("EnterNewPerson")
However the form is already closed and thus the error.

 

[MajP]

So if you want to do what you have

Private Sub cmdAddNewPerson_Click()
  Me.txtName.Undo
  Me.cboNameSelect.Undo
  DoCmd.OpenForm "EnterNewPerson", , , , acFormAdd
  Set frmNewPers = Forms("EnterNewPerson")
  frmNewPers.OnClose = "[Event Procedure]"
  'the next line no longer works because the below line will execute immediately after opeing the form
  'So simply move to the Close event which you are trapping
  'Me.cboNameSelect.Requery
End Sub

 

[mikenyby]

Set the form to modal in the form properties and drop the acDialog. See what happens.

That worked. Thank you and thanks @MajP for elucidating the acDialog action. Have a great day!