Moving to a record on a form

[CK]

I am using the following code snippet to move to a particular record on a
form when doublclicking the record in a list box:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "QuoteID =" & Me.lstQuoteHistory.Value
Me.Bookmark = rst.Bookmark

When this section of the code is run, the form does not appear to have moved
to the record but appears to be sitting still at the first record in the set.

Is there another command I should issue to actually move the form to the
desired record?

 

[Jeanette Cunningham]

CK,
make sure the listbox is not bound to the form - if its control source is
empty then it is not bound to the form.
Make sure that the bound column of the listbox is the column that has the
field QuoteID - not some other field.

As a trouble shooting step, sometimes I make the hidden column of the
listbox visible by setting its width to 1cm or half inch.
This way I can sometimes see where the problem is.

Add a line to the code like this:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "QuoteID =" & Me.lstQuoteHistory
If Not No.Match Then
Me.Bookmark = rst.Bookmark
End If

note: you don't need Me.lstQuoteHistory.Value
just Me.lstQuoteHistory

Jeanette Cunningham

 

[ChrisK]

Jeanette,

Thanks for the fast response. The listbox is definitely not bound to the
form and the bound column is definitely sitting on QuoteID. I added the
rst.nomatch code and that didn't make any difference to the behavior of this
form.

If I open the form and do the doubleclick, the doubleclick event fires:

Private Sub lstQuoteHistory_DblClick(Cancel As Integer)
On Error GoTo ErrorTrap
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "QuoteID =" & Me.lstQuoteHistory
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Call Me.ShowControls
Me.txtQuoteNo.SetFocus
Exit Sub

ErrorTrap:
If Err.Number = 3077 Then 'Double click on a blank area on the box
Exit Sub
Else
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbExclamation
End If
End Sub

Me.ShowControls is a set of commands that display the 20 or so controls on
that form *but* when the ShowControls sub is run, the form has not actually
moved to the chosen record - I've confirmed that by doing a debug.print
me.QuoteID. Is there a command I can run between the "Me.Bookmark =
rst.Bookmark" and the "Call Me.ShowControls" to force the form to move?

kind regards

Chris K

 

[Jeanette Cunningham]

Chris,
The listbox needs to have its multi select property set to none.
I don't know what else on your form is preventing the form moving to the
selected record.
Does the form allow edits - ie, is there any code that would make the form's
recordsource read only
or is its recordsource a non-updateable query by any chance?

I suggest you comment out the line
Call Me.ShowControls
until you can get the form to move to the record selected in the listbox

If that doesn't help, work on a copy of your form.
Comment out all the code except any code that sets up the recordsource for
the form and the listbox.
That should allow the listbox to move the form to the selected record.
Add back some of the commented-out code one step at a time.
Eventually,by a precess of elimination you will find the code that
interferes with the move to selected record of the listbox.

Jeanette Cunningham

 

[ChrisK]

Jeannette,

I worked through this again and again this morning and couldn't find out why
the form was showing the previous current record when the ShowControls sub
is triggered.

However, I have worked around this one by feeding the correct value into the
ShowControls sub from the rst object - a bit of a kludge I know but at least
it's now working....!

Thanks for your help

CK