Reload form based on unbound combo box

[jefflach]

My database currently has 3 main forms used for project budgeting. The workflow is:

1) Use loads "New Project Form" and completes basic info about project (tblProjects- code, name, sponsor, etc)


2) User clicks a button and completes expense details in continuous form (tblDetails) where the foreign key is the primary key in tblProjects.
3) Once user completes form, they have the ability to return to a nearly identical form to the "New Project Form", but in this form the project code can no longer be changed and the forms record source is based on the project selected in a combo box placed in the form's header. This is also the form used to modify project details after the project has already been submitted.

My problem is that I would like the user to be able to close the Details form and return to the modify project form with their project automatically selected and the form loaded based on the project. I am able to pass the value for the project key back to the combo box on the project form from the detail form, but cannot get the form to load the corresponding record referenced in the combo box.

[ssanfu]

From HELP for "BeforeUpdate/AfterUpdate" events: Changing data in a control by using Visual Basic or a macro containing the SetValue action doesn't trigger these events for the control.
So you need to call the combo box afterupdate event. Here is one way:

Assumptions:
"ModifyProjectForm" is the name of the "modify project form"
"cboProjectNum" is the name of the unbound combo box in the form header used to select different project numbers.
"ProjectDetails" is the details form

Modify the "cboProjectNum" code:
Delete "Private" or change it to "Public"

Public Sub cboProjectNum_AfterUpdate()
' Your code Here
'and here
End Sub

or

Sub cboProjectNum_AfterUpdate()
' Your code Here
'and here
End Sub

Add code in the "On Close" event of the "ProjectDetails" form code:

Private Sub Form_Close()
   DoCmd.OpenForm ("ModifyProjectForm")
   Forms("ModifyProjectForm").cboProjectNum = Me.FK  'FK = the foreign key is the primary key in tblProjects
   Call Forms("ModifyProjectForm").cboProjectNum_AfterUpdate
End Sub

Be sure and use your control and form names...

[jefflach]

Hmmm - I migrated this code into my database and I'm getting an error message...

This is the code I use in the form "frmModifyProjects" to navigate to the record based on the combo box "cboSelectProject"

Sub cboSelectProject_AfterUpdate()
DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
End Sub

This code executes perfectly with no issues. Where I'm getting an error is on the red line below (or the blue line if I've "dirtied" the form)... The error message is: "the expression you entered requires the control to be in the active window".

Sub cmdCloseForm_Click()
If Me.Dirty = False Then
DoCmd.OpenForm ("frmModifyProjects")
Forms("frmModifyProjects").cboSelectProject.Value = Me.txtFixID
Call Forms("frmModifyProjects").cboSelectProject_AfterU pdate
DoCmd.Close acForm, Me.Name
Exit Sub
ElseIf Me.Dirty = True Then
Dim response As String
response = MsgBox("You have made unsaved changes to the current project. Do you wish to save your changes before closing?", vbYesNoCancel, "Confirm close")
ElseIf response = vbYes Then
MsgBox "Changes successfully submitted."
ElseIf response = vbNo Then
Me.Undo
MsgBox "Your changes were not saved."
ElseIf response = vbCancel Then
Exit Sub
Else
End If
DoCmd.OpenForm ("frmModifyProjects")
Forms("frmModifyProjects").cboSelectProject.Value = Me.txtFixID
Call Forms("frmModifyProjects").cboSelectProject_AfterU pdate
DoCmd.Close acForm, Me.Name
End Sub

Any ideas? (thanks in advance)

[ssanfu]

I think the error is because there is a space in after update => "_AfterU pdate"

Not knowing what you want to happen makes it hard to get the code right, but try this:

Private Sub cmdCloseForm_Click()
  Dim response As String

  If Me.Dirty Then
    response = MsgBox("You have made unsaved changes to the current project." & vbNewLine & vbNewLine & "Do you wish to save your changes before closing?", vbYesNoCancel, "Confirm close")
    'handle response
    If response = vbYes Then
      'save data
      Me.Dirty = False
      MsgBox "Changes successfully submitted."
      DoCmd.OpenForm ("frmModifyProjects")
      'hide details form
      Forms(Me.Name).Visible = False
      Forms("frmModifyProjects").cboSelectProject = Me.txtFixID
      Call Forms("frmModifyProjects").cboSelectProject_AfterUpdate
      'close details form
      DoCmd.Close acForm, Me.Name

    ElseIf response = vbNo Then
      Me.Undo
      MsgBox "Your changes were not saved."
      DoCmd.Close
      '        DoCmd.OpenForm ("frmModifyProjects")

    Else
      'leave details form open - no save
'      Me.Undo
'      Me.Dirty = False

    End If
  Else
    'not dirty so close
    
    '        DoCmd.OpenForm ("frmModifyProjects")
    DoCmd.Close acForm, Me.Name
  End If
End Sub

The lines in BLUE are optional. Un-comment if you want the commands to be effective.

[jefflach]

Still not working, and getting the same error message. The space that was in my post was a typo in the forum and wasn't actually in the code. I slightly modified your code to include the same routine if the form is closed without changing anything.

' Close form and prompt user to save changes
Private Sub cmdCloseForm_Click()
Dim response As String

If Me.Dirty Then
response = MsgBox("You have made unsaved changes to the current project." & vbNewLine & vbNewLine & "Do you wish to save your changes before closing?", vbYesNoCancel, "Confirm close")
'handle response
If response = vbYes Then
'save data
Me.Dirty = False
MsgBox "Changes successfully submitted."
DoCmd.OpenForm ("frmModifyProjects")
'hide details form
Forms(Me.Name).Visible = False
Forms("frmModifyProjects").cboSelectProject = Me.txtFixID
Call Forms("frmModifyProjects").cboSelectProject_AfterU pdate
'close details form
DoCmd.Close acForm, Me.Name

ElseIf response = vbNo Then
Me.Undo
MsgBox "Your changes were not saved."
DoCmd.Close
' DoCmd.OpenForm ("frmModifyProjects")

Else
'leave details form open - no save
' Me.Undo
' Me.Dirty = False

End If
Else
'not dirty so close

' DoCmd.OpenForm ("frmModifyProjects")
DoCmd.OpenForm ("frmModifyProjects")
'hide details form
Forms(Me.Name).Visible = False
Forms("frmModifyProjects").cboSelectProject = Me.txtFixID
Call Forms("frmModifyProjects").cboSelectProject_AfterU pdate
'close details form
DoCmd.Close acForm, Me.Name
End If
End Sub

I'm still getting the same error message when I try to call the AfterUpdate code...

How do you paste code here in the forum to show up like in your posts?

[ssanfu]

Below the Quick Reply window, bottom right there is a button "Go Advanced".
In the tool bar at the top of the window where you type your reply, you will see a "#". Hover the cursor over the "#" and you will see "Wrap [code] tags around selected text".
There are also buttons to add quoted text and insert images.

[ssanfu]

Try adding Public to the combo box code:

Public Sub cboSelectProject_AfterUpdate()

Also attached is a A2k mdb. Change an account number, then click the close button.
Tonight I'll test the code in Acc2010...