Auto fill based on second form

[jekirksey]

I have a form with a combo box on it that shows our companies employees. It has 2 columns the first one bound [UserID] and the second one an expression showing [LastName]&", "&[FirstName]. The first column has size 0. If a name is not in the database there is a button next to it that opens a second form where you can add a new user, after doing that it returns to the first form and the new user appears on the drop down list, what I would like is to have the new name appear in the combo box field. Any ideas?

 

[David R]

I believe you need to Requery your combo box to get recently added names to be included right away in the listing. Look up Requery action/method in Access help.

 

[jekirksey]

sorry, maybe it wasn't clear, what I would like is when the user closes the second form after adding a new name, to have that name appear already filled in in the combo box, not just on the list (I know it is a small thing, but really would make it much nicer)

 

[David R]

Oh, so when you finish entering someone new, it goes back and selects that value in the original combo box.

Interesting idea. I do something similar, but I used NotInList and just had it prompt me to add the new entry, instead of making a separate command button. Try something along these lines:

Main Form
Control: NeighID
Row Source: SELECT [tableNeighborhoods].[NeighID], [tableNeighborhoods].[NeighName] FROM tableNeighborhoods;
On Not In List:

Private Sub NeighID_NotInList(NewData As String, Response As Integer)
'Used from Microsoft's Support pages (Q197526)

Dim Result
Dim Msg As String
Dim CR As String

    CR = Chr$(13)

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Ask the user if he or she wishes to add the new neighborhood.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Neighborhood Information form in data entry
       ' mode as a dialog form, passing the new Neighborhood name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in the [Neighborhood Information] form's Form_Load
       ' event procedure.
       DoCmd.OpenForm "Neighborhood Information", , , , acAdd, acDialog, NewData
    End If

    ' Look for the neighborhood the user created in the Customers form.
    Result = DLookup("[NeighName]", "tableNeighborhoods", "[NeighName]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the neighborhood was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the neighborhood was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
End Sub

Popup Neighborhood Form:
Open Event:

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      ' If form's OpenArgs property has a value, assign the contents
      ' of OpenArgs to the NeighName field. OpenArgs will contain
      ' a neighborhood name if this form is opened using the OpenForm
      ' method with an OpenArgs argument, as done in the
      ' [Participant Information] form's NeighName_NotInList
      ' event procedure.
      Me![NeighName] = Me.OpenArgs
   End If
End Sub

When it's done, provided I didn't change the Neighborhood Name, it keeps that value selected.

Hope that helps,
David R


[This message has been edited by David R (edited 03-07-2002).]