[AccessD] Updating Form Recordsource and Selecting Control

David Emerson newsgrps at dalyn.co.nz
Sun Feb 12 19:21:20 CST 2017


In my testing I am using values that will return a recordset and the data
appears on the screen.

I tried Rocky's suggestion of requerying the field but with no luck.

Further test based on one below.  I placed another hidden field at the start
of the tab order with the barcode as the second field.  When the
recordsource is updated the first field in 
the tab order is the current field (as shown by the Status bar text).
However, pressing the tab key still doesn't move the cursor to the next
field in the tab order.  

When I place the following in the Enter event of the first field:

    Me!txtWallpaperBarcode.SetFocus

the barcode field ends up with the focus after the recordset is updated (as
shown by the Status bar text) but pressing the tab key still doesn't move
the cursor (nor do Enter, arrow keys or any other key).  However, putting a
messagebox on the barcode's KeyDown event indicates the event is firing even
though nothing seems to be happening.

What can I do with this information?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Sunday, 12 February 2017 5:40 p.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Updating Form Recordsource and Selecting Control

Well you might test that with an empty recordset just to verify what's
coming back. I'm too lazy to do that.  I just put the move last in every
time - 20 year habit now. Don't even have to think about it. :)

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Bill Benson
Sent: Saturday, February 11, 2017 8:13 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Updating Form Recordsource and Selecting Control

Well I think that was to get an accurate record count... the result used to
come back "1" I believe. So in this case since OP is only testing >0,
shouldn't be a factor.  I was a bit hasty.

>From my non-flammable Note 3,
Bill Benson

On Feb 11, 2017 10:59 PM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

> That was what I was taught as well.  And that was in Access 97.  So I 
> always movelast before getting the Count. But I don't know if it's 
> still true in
> A2010 or A2013.
>
> R
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Bill Benson
> Sent: Saturday, February 11, 2017 7:45 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Updating Form Recordsource and Selecting 
> Control
>
> Probably neither here nor there but I thought recordset.count is 
> unreliable if you do not do rst.movelast first.
>
> On Fri, Feb 10, 2017 at 12:04 PM, Rocky Smolin 
> <rockysmolin at bchacc.com>
> wrote:
>
> > I remember having this problem.  I can't remember how I worked 
> > around
it.
> > (old guy, you know.)
> >
> > IIRC, though,  there is a workaround which makes no more sense than 
> > no being allowed to tab to the next control in the tab order.
> >
> > Try Me.txtBox.Requery after updating the record source.
> >
> > BTW, instead of replacing the record source property, could you just 
> > use Me.Requery?
> >
> > R
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of David Emerson
> > Sent: Friday, February 10, 2017 12:23 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Updating Form Recordsource and Selecting 
> > Control
> >
> > Hi Rocky,
> >
> > Yes that is what I ended up trying.  Works fine except after 
> > resetting the recordsource.
> >
> > Further tests.  When the recordsource is updated the first field in 
> > the tab order is the current field.  However, pressing the tab key 
> > doesn't move the cursor to the next field in the tab order.  I need 
> > to manually select another field then tabbing works as expected.
> > Does
> anyone
> know why?
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> >
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of Rocky Smolin
> > Sent: Friday, 10 February 2017 6:07 p.m.
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Updating Form Recordsource and Selecting 
> > Control
> >
> > I always used
> >
> > Me.txtBox.SelStart = 0 ' set the cursor to the start of the filed
> > Me.txtBox.SelLength=len(Me.txtBox) ' set the selected text to the 
> > length of the text in the text box.
> >
> > But I think instead of when the text box becomes current you mean 
> > that it has the focus?
> >
> > HTH
> >
> >
> > Rocky Smolin
> > Beach Access Software
> > 760-683-5777
> > www.bchacc.com
> > www.e-z-mrp.com
> > Skype: rocky.smolin
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of David Emerson
> > Sent: Thursday, February 09, 2017 9:00 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Updating Form Recordsource and Selecting 
> > Control
> >
> > Hi All,
> >
> > Further update,  I checked using the Status Bar Text and the 
> > txtWallpaperBarcode field has the focus - the current text is not 
> > being selected (and the cursor isn't flashing to indicate where it 
> > is
> either!
> >
> > So my question now turns to how to select all the current text when 
> > the field becomes current - I will work on this and return with a
result.
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of John Bodin
> > Sent: Friday, 10 February 2017 4:31 p.m.
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Updating Form Recordsource and Selecting 
> > Control
> >
> > Hi David,
> >
> > If I put two controls on a form - Text0 and Text2 - and in the On 
> > Enter event of Text2 I put me.Text0.Setfocus, I can't move my cursor 
> > into
> Text2.
> > It automatically jumps back to Text0.  So in my dummy table
> > (recordset) I have a field called ID and if I type in "2" into 
> > Text0, the below Select Statement fires correctly, and cursor moves 
> > right back to Text0 for next ID to be input.  No problem with 
> > Recordset changing.  Is this similar to what you are trying to do?
> >
> > Private Sub Text0_AfterUpdate()
> >     Me.RecordSource = "Select * From [Aging Sample] Where ID = " &
> > Me.Text0 End Sub
> >
> > Private Sub Text2_Enter()
> >     Me.Text0.SetFocus
> > End Sub
> >
> >
> > John
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of David Emerson
> > Sent: Thursday, February 09, 2017 6:47 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: [AccessD] Updating Form Recordsource and Selecting Control
> >
> > Hi Listers,
> >
> > I have a form with an unbound control called txtWallpaperBarcode.
> > When text is entered into the control and the after update event 
> > triggered then the form's recordset is updated using the entered 
> > text as a filter as follows:
> >
> >         Set rstTemp = basRunDataObject("dbo.spfrmWallpaperLookup '" 
> > & Me!txtWallpaperBarcode & "'", adCmdText)
> >         If rstTemp.RecordCount <> 0 Then
> >             Set Me.Recordset = rstTemp
> >        End If
> >
> > This all works fine.  Once the recordset is updated I would like the 
> > control to get the focus again.  This is the part I am struggling 
> > with.
> >
> > The control is the first in the tab order.  Next in tab order is an 
> > unbound control (txtAfterBarcode) which has the following code in 
> > the
> Enter event:
> >
> > DoCmd.GoToControl "txtWallpaperBarcode"
> >
> > When the Update code is finished the second control doesn't get the
> focus.
> > I assume this is because the forms recordset is updated.  I have 
> > tried forcing the move at the end of the Update code with this line:
> >
> >     DoCmd.GoToControl "txtAfterBarcode"
> >
> > But I get an error saying that I can't move the focus to the control 
> > txtAfterBarcode.  It seems to be due to the Enter event wanting to 
> > move it out again.
> >
> > Any suggestions?
> >
> > Regards
> >
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand





More information about the AccessD mailing list