I've got an unbound combo box on my main form to allow looking up existing records. If the record exists, it loads it. If it doesn't exist, it moves the inputted code into the (bound) textbox for the ParticipantID on a new record for entering.

There are four possible scenarios:
[list=1]
Old Record loaded, Lookup Another Old Record
Old Record Loaded, Attempt to Lookup & Create New Record
New Record open, Lookup Old Record
New Record open, Attempt to Lookup a different New Record.
[/list=a]

Three of the four cases work perfectly, however I cannot get the last one to move the information over. It does seem to recognize that it's a new record and doesn't give any error messages, but I'd like to have them all act the same. Here's the relative code snippets:

Private Sub Form_Current()
If Me.NewRecord = False Then
'Old record, don't want to change the ParticipantID unwittingly
Me.ParticipantID.Enabled = False
Me.ParticipantID.Locked = True
Else
'New record, import the data in ParticipantID_Lookup if any
Me.ParticipantID.Enabled = True
Me.ParticipantID.Locked = False
If Me.ParticipantID_Lookup <> "" Then
Me.ParticipantID = Me.ParticipantID_Lookup
End If

End If
'Clean up our mess
Me.ParticipantID_Lookup = ""

End Sub

....

Private Sub ParticipantID_Lookup_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ParticipantID] = '" & Me![ParticipantID_Lookup] & "'"

If (Me.NewRecord = True And Me.Dirty = True) Then Me.Undo

If rs.Nomatch = True Then
'No record found, go to new record
DoCmd.GoToRecord , , acNewRec
Me.ParticipantID.SetFocus
Else
'Go to the matching record
Me.Bookmark = rs.Bookmark
Me.FirstName.SetFocus
End If

End Sub


....
This isn't actually part of that code loop, but it affects the same bound box.

Private Sub ParticipantID_BeforeUpdate(Cancel As Integer)
If (Me.NewRecord = False) Then
If (Me.ParticipantID = "" Or IsNull(Me.ParticipantID) = True) Then Me.Undo
End If
End Sub

.....


Any insight?
TIA,
David R

Just a guess, but this line looks orphan and doesn't seem to have an accompanying END IF.

If (Me.NewRecord = True And Me.Dirty = True) Then Me.Undo

Ian

My understanding of VBA code is that If Then statements on single lines do not require an End If, similar to C code on the same line not requiring {}.
If I'm wrong I'd have expected it to break before this because I've used this structure in several places.

Here's the code that solved the loophole:


Private Sub Form_Current()

If (Me.ParticipantID_Lookup = "") Then
Me.ParticipantID_Lookup.SetFocus
Exit Sub
Else

If Me.NewRecord = False Then
'Old record, don't want to change the ParticipantID unwittingly
Me.ParticipantID.Enabled = False
Me.ParticipantID.Locked = True

Me.FirstName.SetFocus

Else
'New record, import the data stored in Me.ParticipantID_Lookup
Me.ParticipantID.Enabled = True
Me.ParticipantID.Locked = False

Me.ParticipantID = Me.ParticipantID_Lookup
Me.ParticipantID.SetFocus
End If

End If

'Clean up our mess
Me.ParticipantID_Lookup = ""

End Sub

....

Private Sub ParticipantID_Lookup_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[ParticipantID] = '" & Me.ParticipantID_Lookup & "'"

If rs.Nomatch = True Then
If (Me.NewRecord = True) Then
If (Me.Dirty = True) Then
Me.Undo
Else
End If

If Me.ParticipantID_Lookup <> "" Then
Me.ParticipantID = Me.ParticipantID_Lookup
Me.ParticipantID_Lookup = ""
End If

Else
DoCmd.GoToRecord , , acNewRec
End If

Else
'Go to the matching record
Me.Bookmark = rs.Bookmark

End If

End Sub



Cheers,
David R