VBA for NotInList return to old record or BeforeUpdate value

[Barry A&P]

I have a unbound combo box to find a record on a form. it is basically wizard
created code. if the ID is NotInList the form jumps to a random record and
the combo still displays the Invalid ID giving the impression the correct
record is selected. i tried to add a message box which pops up but the form
maintains the same behavior. After the message box is confirmed i would like
everything to remain on whatever record was previously selected before the
Invalid ID was entered

Private Sub Form_Current()
Me.SelectByPN_Combo = Me.TRKID 'Updates SelectByPN_Combo when navigation
buttons are used
Me.SelectBySN_Combo = Me.TRKID
Me.SelectByTRKID_Combo = Me.TRKID
Me.SelectByDescription_Combo = Me.TRKID
End Sub

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
If rs.NoMatch = True Then
MsgBox "The selected ID does not exist!"
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Sorry i Pester you guys so much
Thanks

Barry

 

[Marshall Barton]

I have a unbound combo box to find a record on a form. it is basically wizard
created code. if the ID is NotInList the form jumps to a random record and
the combo still displays the Invalid ID giving the impression the correct
record is selected. i tried to add a message box which pops up but the form
maintains the same behavior. After the message box is confirmed i would like
everything to remain on whatever record was previously selected before the
Invalid ID was entered

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
If rs.NoMatch = True Then
MsgBox "The selected ID does not exist!"
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

You are setting the form's bookmark under the wrong
condition, probably because the stupid wizard did it that
way.

Try something more like:

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[TRKID] = " &
Str(Nz(Me![SelectByTRKID_Combo], 0))
If .NoMatch = True Then
MsgBox "The selected ID does not exist!"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Post back if the item is always in the combo box list or if
users can type whatever they want in the combo box. In it
is always in the list, the code could be different. If hte
only way to get NoMatch is when a user enters junk into the
combo box, you could just set the combo box's LimitToList
property and let Access deal with situation.

 

[Barry A&P]

Thank you for the code marshall i will play with it a little to see if i can
get whatever complicated thing i was trying to do.. But for now "limit to
list" i feel so stupid for overlooking the obvious.. Thanks again

Barry

I have a unbound combo box to find a record on a form. it is basically wizard
created code. if the ID is NotInList the form jumps to a random record and
the combo still displays the Invalid ID giving the impression the correct
record is selected. i tried to add a message box which pops up but the form
maintains the same behavior. After the message box is confirmed i would like
everything to remain on whatever record was previously selected before the
Invalid ID was entered

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
If rs.NoMatch = True Then
MsgBox "The selected ID does not exist!"
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

You are setting the form's bookmark under the wrong
condition, probably because the stupid wizard did it that
way.

Try something more like:

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[TRKID] = " &
Str(Nz(Me![SelectByTRKID_Combo], 0))
If .NoMatch = True Then
MsgBox "The selected ID does not exist!"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Post back if the item is always in the combo box list or if
users can type whatever they want in the combo box. In it
is always in the list, the code could be different. If hte
only way to get NoMatch is when a user enters junk into the
combo box, you could just set the combo box's LimitToList
property and let Access deal with situation.