Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #733 > unrolled thread

Opening a form to a specific record leads to #error for one field

Back to article view | Back to comp.databases.ms-access

Contents

  Opening a form to a specific record leads to #error for one field "buckskin" <buckskin@mailinator.com> - 2011-04-01 01:45 +0100
    Re: Opening a form to a specific record leads to #error for one field "buckskin" <buckskin@mailinator.com> - 2011-04-01 02:13 +0100
    Re: Opening a form to a specific record leads to #error for one field Salad <salad@oilandvinegar.com> - 2011-03-31 20:47 -0500
      Re: Opening a form to a specific record leads to #error for one field "buckskin" <buckskin@mailinator.com> - 2011-04-01 05:19 +0100
        Re: Opening a form to a specific record leads to #error for one field Salad <salad@oilandvinegar.com> - 2011-04-01 07:52 -0500
      Re: Opening a form to a specific record leads to #error for one field "buckskin" <buckskin@mailinator.com> - 2011-04-01 10:25 +0100

#733 — Opening a form to a specific record leads to #error for one field

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?

[toc] | [next] | [standalone]

#734

"buckskin" <buckskin@mailinator.com> wrote in message
news:8vklhiFn41U1@mid.individual.net...
>
> 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?
>
>

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.

[toc] | [prev] | [next] | [standalone]

#735

buckskin wrote:

> 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?
> 
> 
Your statement
   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?

[toc] | [prev] | [next] | [standalone]

#739

"Salad" <salad@oilandvinegar.com> wrote in message
news:kZqdnSwP97MBswjQnZ2dnUVZ_gidnZ2d@earthlink.com...
> buckskin wrote:
>
> > 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?
> >
> >
> Your statement
>    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.*.

Ah good point.

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

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

[toc] | [prev] | [next] | [standalone]

#744

buckskin wrote:

> "Salad" <salad@oilandvinegar.com> wrote in message
> news:kZqdnSwP97MBswjQnZ2dnUVZ_gidnZ2d@earthlink.com...
> 
>>buckskin wrote:
>>
>>
>>>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?
>>>
>>>
>>
>>Your statement
>>   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.*.
> 
> 
> Ah good point.
> 
> 
>>Why are you setting the bookmark prior to code for finding the record?
> 
> 
> Hmmm, the perils of copy/pasting code without understanding what it is.  OK
> so I should set the recordsource first then set the bookmark?
> 

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

[toc] | [prev] | [next] | [standalone]

#741

"Salad" <salad@oilandvinegar.com> wrote in message
news:kZqdnSwP97MBswjQnZ2dnUVZ_gidnZ2d@earthlink.com...
> >
> >
> Your statement
>    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?

BTW thanks Salad it all works spiffingly well now!

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web