NoMatch vs NotInList (1 Viewer)

[David R]

One works, the other doesn't. I'm not sure why.
1. Using NoMatch= True, with Limit to List set to No

Private Sub ParticipantID_Lookup_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Dim PIL As String
    
    If (IsNull(Me.ParticipantID_Lookup)) Or (Me.ParticipantID_Lookup = "") Then Exit Sub
    
    'Include check for Len(ParticipantID) = 6
    PIL = Left(Me.ParticipantID_Lookup, 6)
        If (Len(PIL) < 6) Then
        MsgBox ("Make sure the ParticipantID has 6 characters.")
        Exit Sub
    End If
    
    Set rs = Me.Recordset.Clone
    
    rs.FindFirst "[ParticipantID] = '" & PIL & "'"
    
    If rs.Nomatch = True Then
        If (Me.NewRecord = True) Then
            If (Me.Dirty = True) Then Me.Undo
        Else
           DoCmd.GoToRecord , , acNewRec
        End If
        
        Me.ParticipantID = Left(PIL, 6)
        Me.ParticipantID_Lookup = ""
    Else
      'Go to the matching record
       Me.Bookmark = rs.Bookmark
       
    End If
    
    Me.ParticipantID_Lookup.Requery
    Me.subformDevices.Requery
    Set rs = Nothing
End Sub

2. Using NotInList, with Limit to List set to Yes

Private Sub ParticipantID_Lookup_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Dim PIL As String
    
    If (IsNull(Me.ParticipantID_Lookup)) Or (Me.ParticipantID_Lookup = "") Then Exit Sub
    
    'Include check for Len(ParticipantID) = 6
    PIL = Left(Me.ParticipantID_Lookup, 6)
        If (Len(PIL) < 6) Then
        MsgBox ("Make sure the ParticipantID has 6 characters.")
        Exit Sub
    End If
    
    Set rs = Me.Recordset.Clone
    
    rs.FindFirst "[ParticipantID] = '" & PIL & "'"
    
      'Go to the matching record
       Me.Bookmark = rs.Bookmark
       
    Me.ParticipantID_Lookup.Requery
    Me.subformDevices.Requery
    Set rs = Nothing
End Sub

Private Sub ParticipantID_Lookup_NotInList(NewData As String, Response As Integer)
    If (Me.NewRecord = True) Then
        If (Me.Dirty = True) Then Me.Undo
       ' DoCmd.GoToRecord , , acLast
    End If

    Response = acDataErrAdded

    DoCmd.GoToRecord , , acNewRec

    Me.ParticipantID = Left(NewData, 6)
    Me.ParticipantID_Lookup = ""

    Me.ParticipantID.SetFocus
End Sub

Any ideas? I keep getting a 2105 error, Cannot go to specified record. Is it because I am somehow still in the recordset clone from the previous event? If so, how do I escape that? I would like to use the events as they're designed, rather than reinventing the wheel.

TIA,
David R

 

[Rich]

I'm trying to figure out what your rying to do, but it's getting late and I've got a bug etc.
If (Me.NewRecord = True) Then If (Me.Dirty = True) Then Me.Undo ' DoCmd.GoToRecord , , acLast
If someone's entering a new record then the Form's dirty, can't figure out why you need both. Does your code indicate that if they're not in a new record you want the code to add it, there's no message box asking for confirmation?

 

[David R]

Here's the logic train:
Unbound lookup box, value didn't match an existing record.
IF this is a new record,
IF it's a dirty new record, clear out the partially entered information. The unbound box gets the focus from Form_Current when it is a new record, so there shouldn't be any data in it.
The acLast line was added because of this 2105 error. I thought perhaps going to an existing record, and then back to a new record would help. Since it didn't work either, I commented it out.
ELSE this isn't a new record, go to a new record.
END IF
IN ANY CASE: fill the (bound) ParticipantID field with the value in the lookup field, and blank the lookup field.

That help any?
David R

 

[freshmaker]

David-

Though I can't explain why, my experimenting with the solution led me to believe that the problem lies in the fact the lookup is unbound.

The following uses the bound control with satisfactory results.

Private Sub SSN_BeforeUpdate(Cancel As Integer)
'If SSN already exists, move to existing record

Dim strLookup As String
strLookup = Forms!frmSubscribers!SSN

If Me!SSN = DLookup("SSN", "tblSubscribers", "SSN = Forms!frmSubscribers!SSN") Then
DoCmd.CancelEvent
Me.Undo
DoCmd.FindRecord strLookup, , True, , True
End If

End Sub



[This message has been edited by freshmaker (edited 01-16-2002).]