[AccessD] Not In List Not the Right Solution

Tue Apr 9 20:31:35 CDT 2013

Full-blown NIL ...

==========================================================================
In main form:

Private Sub CardexStreetID_NotInList(NewData As String, Response As Integer)
    Dim Msg As String
    Msg = "'" & NewData & "' is not in the Streets table. "
    Msg = Msg & "Would you like to add it?"
    If vbNo = MsgBox(Msg, vbYesNo + vbQuestion, "Add Street?") Then
        Response = acDataErrDisplay
    Else
        On Error Resume Next
        Dim varOpenArgs As String
            varOpenArgs = "varCaption=Add Street;varCycle=1;varNewData=" &
StrConv(NewData, vbProperCase) & ";"
        DoCmd.OpenForm "frmStreets", acNormal, , , acFormAdd, acDialog,
varOpenArgs
        If Err Then
            MsgBox "An error occurred. Please Try Again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
        Me!CardexStreetID.Requery
    End If
    Msg = ""
End Sub
==========================================================================
In called form:

Option Compare Database
Option Explicit
Dim varKeyData As Variant
Private Sub Form_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
        Form.Caption = Nz(adhGetItem(Me.OpenArgs, "varCaption"))
        Form.Cycle = Nz(adhGetItem(Me.OpenArgs, "varCycle"), 0)
        varKeyData = Nz(adhGetItem(Me.OpenArgs, "varNewData"))
    End If
End Sub
Private Sub Form_Current()
    If Not varKeyData = "" Then
        If IsNull(StreetName) Or StreetName = "" Then
            StreetName = varKeyData
        End If
    End If
End Sub
Private Sub StreetName_AfterUpdate()
    StreetLastUpdated = Date
End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tony Septav
Sent: Tuesday, April 09, 2013 20:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Not In List Not the Right Solution

Hey Rocky
What a lot of code for NotInList.
Just Smile In

Tony Septav
Nanaimo, BC
Canada

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: April-09-13 2:11 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3272 / Virus Database: 3162/6234 - Release Date: 04/09/13

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com