[AccessD] Not In List Not the Right Solution

Thanks everyone for your suggestions.  Sorry to bother.  I worked around the
problem by checking to see if the name they enter when adding a new person
is already there and if so, displaying that record so they can update the
other fields.  Client likes this:

Private Sub fldParticipantLastName_AfterUpdate()

Dim lngID As Long

    If Me.NewRecord = False Then Exit Sub
    If Nz(Me.fldParticipantFirstName) = "" Then Exit Sub
    
    lngID = Nz(DLookup("fldParticipantID", "tblParticipant",
"fldParticipantLastName = '" _
        & Me.fldParticipantLastName _
        & "' AND fldParticipantFirstName = '" & fldParticipantFirstName &
"'"), 0)
        
    If lngID <> 0 Then
        Me.RecordsetClone.FindFirst "fldParticipantID = " & lngID
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
        
End Sub

Best,

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Tuesday, April 09, 2013 11:36 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Not In List Not the Right Solution

Why don't you post your code, Rocky, so we can try and spot anything out of
whack rather than just guessing.

Charlotte

On Tue, Apr 9, 2013 at 11:32 AM, Rocky Smolin <rockysmolin at bchacc.com>wrote:

> In my case, the combo is on the bound form where they want to add the 
> record.  But something like that may work.
>
> R
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Tuesday, April 09, 2013 10:06 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Not In List Not the Right Solution
>
>
>   Not in list is the right event.  Would go something like this:
>
> Private Sub cboPatient_NotInList(NewData As String, Response As 
> Integer)
>
> 10      gstrMBTitle = "Patient not on file"
> 20      gstrMBMsg = "Do you wish to add?"
> 30      gintMBDef = vbYesNo + vbExclamation + vbDefaultButton1
> 40      gintMBResp = MsgBox(gstrMBMsg, gintMBDef, gstrMBTitle)
>
> 50      If gintMBResp = vbYes Then
> 60        DoCmd.OpenForm "frmPatients", acNormal, , , acFormEdit,
acDialog,
> "ADD;EXITTOFORM=frmVisits"
> 70        Response = DATA_ERRADDED
> 80      Else
> 90        Response = DATA_ERRCONTINUE
> 100     End If
>
>
> End Sub
>
>   This pops up a form, let's the record get added, then forces a 
> requery of the combo and another lookup (Response = DATA_ERRADDED).
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky 
> Smolin
> Sent: Tuesday, April 09, 2013 12:24 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Not In List Not the Right Solution
>
> Dear List:
>
> I have a request from a user that when they try to find a name in a 
> combo box if the name is not there, that the program go to add a new 
> record and place the data entered in the combo box in the last name 
> field and let them finish entering the new data.
>
> I tried the NotInList event until I realized this is to add entries to 
> the combo box.  But I can't seem to get it to behave properly - it 
> goes into a loop displaying my "add then new guy now?" not in list
message.
>
> I think Not In List is not the best way to do this, but don't know how 
> else.
>
> Any ideas?
>
> MTIA
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com 
> <http://www.e-z-mrp.com/>
> Skype: rocky.smolin
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com