MS Access form - New record on open and after saving record

Question

In relation to my question posted here:

MS Access Form where user name is selected from list

I have created a form used as an employee time sheet. Users double click this form and another form pops up where they select their user name from a combo box and the main time sheet form loads up specifically for their user name. They are able to add new records, and also view their previous submissions by scrolling through the "Previous" and "Next" records.

However, when a user opens up the form, their last record displays. Also, when a new record is saved, that new record continues to display unless a user clicks the "New (blank) record" button.

Can someone help me modify the VBA code in the form to allow for:
1) A blank record to automatically load when the form is opened up 2) A blank record to automatically load when a new record is saved/modified.

This is the pop up window - form that pops up when you select the main data entry form:

This is the code behind the pop up form (above):

Private Sub Form_Current()
If VBA.Strings.Len(txtUN & "") = 0 Then DoCmd.OpenForm "frm_UserName", acNormal, , , , acDialog
If VBA.Strings.Len(txtUsername & "") = 0 Then txtUsername = txtUN
End Sub

 Private Sub Form_BeforeUpdate(Cancel As Integer)


   Dim ctl As Control

   On Error GoTo Err_BeforeUpdate


   If Me.Dirty Then

      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
      End If
   End If

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate

End Sub

This is the main data entry form [Specialist - Timesheet Entry]:

This is the code behind the [Specialist - Timesheet Entry] form:

Option Compare Database

Private Sub cboUserName_AfterUpdate()
   Forms![Specialist - Timesheet Entry].txtUN = cbousername
   Forms![Specialist - Timesheet Entry].Filter = "user_full_name = '" & cbousername & "'"
   Forms![Specialist - Timesheet Entry].FilterOn = True
      'Forms![Specialist - Timesheet Entry].Requery
      'DoCmd.Close

End Sub
Private Sub Form_Unload(Cancel As Integer)
  If (VBA.Strings.Len(cbousername & "") = 0) Then
     MsgBox "You must supply a user name before proceeding.", , "ERROR: Missing Info."
     Cancel = True
  End If
End Sub  

Once the [Specialist - Timesheet Entry] is selected, the [form_UserName] pops up. You must select a user name from the list, and hit the "X" button. Once the "X" button is selected than the data entry form [Specialist - Timesheet Entry] will appear for a user to enter the necessary fields. I am also looking for the user to not have to hit the "X" button in order to initiate the [Specialist - Timesheet Entry] form. Ideally once the user selects the user name from the drop down list in the pop up, i'd like the form to close automatically and generate the [Specialist - Timesheet Entry] form.

Answer 1

I think I have something for you here.

I share the same sentiment as OverMind - I'm iffy about the Me.Dirty code. I don't think it's necessary. All you need to do is click the dropdown, select a name and open a form that is filtered using the selected name. Same thing goes for the Form_BeforeUpdate event. You're not actually modifying any data, you're just clicking and selecting. I recommend removing both of those events.

If you don't mind, I reworked this a little since I don't have the entire application in front of me. Try the following (there's a few parts):

1.) frm_UserName

Comment your existing code in the cboUserName_AfterUpdate event and add this line:

       DoCmd.OpenForm "Specialist - Timesheet Entry"
        'Open the main timesheet form and set the filter based on the dropdown selection in the previous form.
       Forms![Specialist - Timesheet Entry].Filter = "Username = '" & Forms![frm_UserName].cboUserName.Column(1) & "'"
       'Turn the filter on.
       Forms![Specialist - Timesheet Entry].FilterOn = True
       'Set the username textbox to the selected record.
       Forms![Specialist - Timesheet Entry].txtUN = Me.cboUserName.Column(1)
       'Jump to a new record even though the form is filtered.
       Forms![Specialist - Timesheet Entry].SetFocus
       DoCmd.GoToRecord , , acNewRec
       'Close the previous form - we no longer need it.
       DoCmd.Close acForm, "frm_UserName"

This will open the Timesheet - Specialist Entry from frm_UserName form "automatically load [a blank record] when the Timesheet form is opened up" using a the selection as the filter.

2.) Specialist - Timesheet Entry

Create a new procedure in the Form Open event and add this code:

Private Sub Form_Open(Cancel As Integer)

    DoCmd.OpenForm "frm_UserName", acNormal, , , , acDialog

End Sub

Next, create a new procedure in the Save button Click event and add this code:

Private Sub cmdSave_Click()

    'Save the record.
    RunCommand acCmdSaveRecord
    'Load a new record after the save.
    DoCmd.GoToRecord , , acNewRec

End Sub

This will automatically load a blank record when a new record is saved/modified.

I do hope this helps. If you have any questions at all, please let me know. I'd be glad to help even further. I also have a working copy available...