Continuous form set focus

[Mike Revis]

Hi Group,
Win xp Access 2007

This is my first effort at working with continuous subforms.

I've read through the posts I can find about continuous forms but nothing
seems to address my situation.

The Northwind forms do exactly what I want but I can't figure out what
Northwind is doing right or what I'm doing wrong.

I have a main form. frmOrder.
frmOrder has a continuous subform. sfrmOrderDetails.

sfrmOrderDetails has fields for
Quantity
PartDescription
PriceEach
ItemTotal (calculated. not stored. )

PartDescription is a combo box. cboPartDescription.
cboPartDescription is set limit to list = yes.

The on not in list event of cboPartDescription opens frmEnterEditPart to add
the information about the new part.
(Northwind doesn't seem to use the not in list event but accomplishes the
same thing. ???)

So far so good.

When I close frmEnterEditPart the frmOrder (?)activates(?) with the focus
set to the first field of the first record of the continuous sub form.
sfrmOrderDetails.

I would like the focus to remain on cboPartDescription for the description I
just added. Just like it does in Northwind.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike

 

[Tom van Stiphout]

On Sat, 7 Nov 2009 19:57:28 -0800, "Mike Revis"

Instead of NotInList event, you can use the new ListItemsEditForm
property. Maybe doing that too will kill two birds with one stone.

-Tom.
Microsoft Access MVP

 

[Mike Revis]

Thanks Tom,
I was able to figure out how to use the ListItemsEditForm after a few tries.

I've never used macros so I copy/paste from the northwind sample.

I got it so I can goto the EnterEditParts form and enter the part info.

The focus problem remains though.

When I return to the order form the focus is still on the first field of the
first record on the order details continuous subform.

I tried changing it to a datasheet but same problem.

I still have to figure out how to get the focus to return to the
cboPartDescription I started from.

The focus returns to the correct field in the northwind sample but I can't
see yet what's different.

Regards,
Mike

 

[Mike Revis]

Thanks Ken,
I have the part about adding a new part using the NotInList event. That all
works just fine.

My problem is that when I return to frmOrder from frmEnterEditPart after
entering the additional part information the focus is on the first field of
the first record on my continuous subform. sfrmOrderDetails.
The focus should return to the cboPartDescription on the record I was
adding.

Regards,
Mike

 

[Mike Revis]

Ken,
Thank you for taking the time to work with me.

I am using the acDataErrAdded and acDataErrContinue stuff. In fact I think
the code I plagerized years ago is the same as yours. I haven't checked it
exactly.

As I have said this is a "setfocus issue".

I have made one discovery.

When I use the sfrmOrderDetails as a stand alone form the focus goes to the
field following cboPartDesription after returning from the form
"frmEnterEditParts".

This is exactly the behavior I would like to happen.

When I perform exactly the same actions using sfrmOrderDetails as a subform
on frmOrder the focus returns to the first field of the first record of
sfrmOrderDetails. Not what I want.

Curiouser and curiouser.

Regards,
Mike

Mike:

It sounds like you are requerying the subform, which is unnecessary. All
you
have to do is set the return value of the NotInList event procedure's
Response argument to acDataErrAdded. That requeries the combo box's list
so
that it includes the new value which you've added. If the user elects not
to
add the new item or backs out of the dialogue form without doing so then
the
return value is set to acDataErrContinue and the control is undone.

If you look at the code I posted it does this in both scenarios if the
user
has elected to add the new item, and in the first scenario (where a form
is
opened in dialogue mode) if the user has actually inserted the new record
in
the form.

Ken Sheridan
Stafford, England

Thanks Ken,
I have the part about adding a new part using the NotInList event. That
all
works just fine.

My problem is that when I return to frmOrder from frmEnterEditPart after
entering the additional part information the focus is on the first field
of
the first record on my continuous subform. sfrmOrderDetails.
The focus should return to the cboPartDescription on the record I was
adding.

Regards,
Mike

Mike:

[quoted text clipped - 133 lines]

Best regards,
Mike

 

[Mike Revis]

Thanks again Ken,
I will take some time to study your suggestions.

Using the ListItemsEditForm as Tom suggested works just as well as the
NotInList event but produces the same result re: the focus issue.

Regards,
Mike

Mike:

As you say, curiouser and curiouser?? For some reason there is clearly an
implicit requerying of the form when its used as a subform. The usual
solution for keeping the record pointer on whatever record was current
before
a requery is to grab the key of the record to a variable, and then after
the
requery find the record with that key value in a close of the form's
recordset and synchronize the form's bookmark with the clone's.

I tried doing this in a subform of my own by putting the code in the
NotInList event procedure, but it still moved to the first row, and
moreover,
triggered the NotInList event again! So I think the repositioning of the
record pointer would have to be done outside the NotInList event procedure
by:


1. In the subform's module declare (a) a module level Boolean variable,
and
(b) a variable of suitable data type in which to store the key of the
subform's underlying recordset.

2. In the NotInList event procedure set the variable to True if the user
elects to add the new item.

3. In the NotInList event procedure grab the key of the subform's current
record to the variable.

4. In the subform's Current event procedure, if the Boolean variable is
True:


(a) Find the row in the subform's recordset's clone where the key
matches the value in the variable.

(b) Synchronize the subform's Bookmark with the clone's.

(c) Set the Boolean variable to False

If you have any problems implementing this post back and I'll see if I can
cobble some code together.

I don't use Access 2007 myself, so I don't know if anything similar could
be
done when using the ListItemsEditForm as Tom suggested.

Ken Sheridan
Stafford, England

Ken,
Thank you for taking the time to work with me.

I am using the acDataErrAdded and acDataErrContinue stuff. In fact I think
the code I plagerized years ago is the same as yours. I haven't checked it
exactly.

As I have said this is a "setfocus issue".

I have made one discovery.

When I use the sfrmOrderDetails as a stand alone form the focus goes to
the
field following cboPartDesription after returning from the form
"frmEnterEditParts".

This is exactly the behavior I would like to happen.

When I perform exactly the same actions using sfrmOrderDetails as a
subform
on frmOrder the focus returns to the first field of the first record of
sfrmOrderDetails. Not what I want.

Curiouser and curiouser.

Regards,
Mike

Mike:

[quoted text clipped - 40 lines]

Best regards,
Mike