New Record on "Not In List"

[mtagliaferri]

The "Not In List" event code opens a form to add the record, however when I close the form the staff number is not added to the main form opened, and by typing the staff number again it will run the "Not In List" event again.
How can I retain the staff number originally typed and display the record details on the form once the "Not In List" event/form has been closed?

Currently once I have added the staff number, I am not able to close/save the form as unless I type a well known staff number, then close the form re-open it and edit that record to the new staff number last entered.

Private Sub Staff_Number_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Staff_Number_NotInList

    If MsgBox("   CREW MEMBER NOT IN LIST   " & vbCrLf & "   Do you want to edit crew list?   ", vbYesNo) = vbYes Then
        Response = acDataErrContinue
        Me.Staff_Number.Undo
        DoCmd.OpenForm "frmCrewMember", , , , acFormAdd
        Forms!frmCrewMember.StaffNumber = NewData
    Else
        Response = acDataErrContinue
        Me.Staff_Number.Undo
    End If

Exit_Staff_Number_NotInList:
    Exit Sub

Err_Staff_Number_NotInList:
    MsgBox Err.Description, , " db1"
    Resume Exit_Staff_Number_NotInList

End Sub

 

[Minty]

You would need to requery the combobox recordsource to get it to include the new data.
You can do that on the close event of the frmCrewMember.

 

[mtagliaferri]

Hi Minty, how can I do that?
also the frmCrewMember will be opened throw other forms for different reasons.

 

[Orthodox Dave]

Simplest code to requery a combo box is

me.[ComboBox].requery

Replace [ComboBox] with the name of your combo box (no quote marks).

 

[Minty]

You would probably need to tell the frmCrewMember that you had opened it from the not in list code - use the OpenArgs property to set a hidden flag on the form or a form declared variable.

Then on your form close code check that flag and then update the combobox on the calling form. The syntax would be;

	Forms!YourFormName!Staff_Number.Requery