I'm opening a form like so;

strWhere = "[id] = " & lngRpt
DoCmd.openform "frmCC_Choice", , , , , acDialog, "usercodes"


The form opens and I run this form in the open event

'Dao code courtesy of Allen Browne in 2006

Dim rs As DAO.Recordset
With Forms!frmCC_Choice
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

Me.RecordSource = "SELECT report_defs.*, report_defs.Type,
report_defs.DateModified FROM report_defs WHERE (((report_defs.Type) =" &
"'C'" & ")) ORDER BY report_defs.DateModified DESC;"

This all works - nearly. I can open the form at the record I require and
can then navigate the other records.
But the textbox bound to DateModified now shows #Error.

If I remove that sorting field from the recordsource then it displays ok.
It's obviously the sorting that screws things up (but I need it sorted).

Is there a quick fix?

"buckskin" <buckskin (AT) mailinator (DOT) com> wrote

Just realised that in my current position, the code is NOT actually opening
the desired record (it goes to the first one). If I remove the sorting
field, everything does work but it's sorting on the PK instead of the date.

buckskin wrote:

Me.RecordSource = "SELECT report_defs.*, report_defs.Type,
report_defs.DateModified FROM report_defs

Why do you select Type and DateModified again. They should already be
selected in report_defs.*.

Why are you setting the bookmark prior to code for finding the record?

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Ah good point.

Hmmm, the perils of copy/pasting code without understanding what it is. OK
so I should set the recordsource first then set the bookmark?

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

BTW thanks Salad it all works spiffingly well now!

buckskin wrote:

That would make sense to me. Otherwise you are finding the record first
in the existing recordsource then changing the recordsource thus wiping
out the find.

I noticed also when you are opening a form you pass a "usercode". Do
you use that? If not, you could pass the id of the record you are
searching for. Ex: On the OnOpen even

Me.RecordSource = "SELECT report_defs.*, report_defs.Type...
If Not IsNull(Me.OpenArgs) then
...code to find record in source
strWhere = "ID = " & Me.openargs
rs.FindFirst strWhere
...
endif