Bookmark the record in the Combo and return to it

[Peter Stone]

XP/Access 2003

I'm a novice.

Users select records by destination using a Combo box (cboDestination).

If there are no records for the selected destination, a message box asks if
they want to return to the record that was originally in the combo box. If
they click "No", I want the form to return to the original record. I presume
I need to bookmark the original record in the Before Update property and
return to it using the After Update property, but my attempts were
unsuccessful.

Private Sub cboDestination_AfterUpdate()
Dim resp, msg As String
Me.lstSelectRecord.Requery
Me.RecordsetClone.FindFirst _
"[DestinationsID] = " & Me![cboDestination]

If Not Me.RecordsetClone.NoMatch Then
'go to the first record in the set
Me.Bookmark = Me.RecordsetClone.Bookmark

Else
msg = "No records. Do you want to add records?"
resp = MsgBox(msg, vbQuestion + vbYesNo, "Add Record")
If resp = vbYes Then
'go to new record
DoCmd.GoToRecord , , acNewRec
Me.DestinationsID = Me.cboDestination
Else
'return to original record
CODE HERE TO RETURN TO THE ORIGINAL RECORD
End If
End If
Me.Refresh
End Sub

 

[Crystal]

Hi Peter,

You can look at this code to get the functionality you are
seeking

This code would go behind your form

Private Function FindRecord()

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long, mPassNumber as integer
Dim mRecordIDcurrent As Long

mRecordIDcurrent = nz(IDfield)
mPassNumber = 0
mRecordID = Me.ActiveControl

'set lookup combo to be empty
Me.ActiveControl = Null

FindRecord_FindIt:
mPassNumber = mPassNumber + 1
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
Else
select case mPassNumber
case 1
'remove the filter and find the record
me.filterOn = false
me.Requery
goto FindRecord_FindIt
case 2
'filter was already removed and record wasn't found
'set record back to where it was
mRecordID = mRecordIDcurrent
msgbox "Record not found",,"Record not found"
goto FindRecord_FindIt
end select
End If

End Function

To implement this on your combo -- if you just wish to
change what I posted for the additional feature that you want...

on your combo, instead of an [Event Procedure]

AfterUpdate --> =FindRecord()
(you can also use the BeforeUpdate event)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Peter Stone]

Thanks Crystal

I had never put any code behind the form before and so as well as solving my
problem I learnt something new.

Thanks again

Peter

 

[Crystal]

You're welcome, Peter happy to help

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com