Force subform to always show first record

  • Thread starter Thread starter markv
  • Start date Start date
M

markv

The message below was posted in the forms area, but I
thought it might be more appropriate to post it in the
forms coding area. Thanks for any suggestions
MarkV

==========================
I have an unbound form with a combo box and a subform on
it. I use the combo box to select a customer and the
subform displays all customer order information. I have
it linked correctly with one small glitch.

When I select a customer with 10 orders all 10 orders show
properly in the subform. When I now select a new customer
with only 5 orders, the 5 orders show correctly but the
record pointer is at record #6. The record pointer is
trying to point to record #10 (the previous selection) but
since there are only 5 it moves to the new entry (blank)
record at the end of the data set (#6).

How can I force the subform to always show record #1 as I
make new selections in combobox regardless of how many
records are in the subform?

Thanks for any help
Mark

=========================================================
Hi, Mark.

Having just solved the exact opposite issue, i.e., going
to a new record, try:

DoCmd.GoToRecord , , acFirst

in the On Enter event of the subform control.

HTH
Kevin Sprinkel
Click to expand...
================================================
Thanks Kevin, that works if I tab into (enter)the
subform. Is it possible to achieve the same result by
using the after update event in the combo box (i.e.
without having to tab into the subform?

I tried a few techniques like using bookmarks and
recordset clones but was unable to get the syntax correct.

Thanks for any further ideas
MarkV
 
well, using

RunCommand acCmdRecordsGoToFirst

would work in a main form - in an OnOpen event, for instance. for the
subform, you might try using a With statement in the combo box's AfterUpdate
event, as

With Me!NameOfSubformControl.Form
RunCommand acCmdRecordsGoToFirst
End With

make sure you use the name of the subform *control* in the main form, *not*
the name of the subform object in the database window - they may be
different.

hth
 
Thanks Tina but I was not able to make it do anything. Am
I using the correct reference to the subform?

Private Sub cbxCustomerID_AfterUpdate()
With Me!subfrmQuotesByCustomer.Form
RunCommand acCmdRecordsGoToFirst
End With

The name of the subform on the parent form is
surfrmQuotesByCustomer. I'm not sure what you meant when
you wrote " make sure you use the name of the subform
*control* in the main form, not the name of subform object
in database window"

In the forms window the subform is named
subfrmQuotesByCustomer. The caption for the subform is
the same as the name I used. I didn't see any "name"
property for a form.

The parent form is named frmQuotesByCustomer

Anyway I tried the code you suggested and it doesn't cause
any problem, it just doesn't do anything....

Any thoughts?
Thanks for your efforts.

MarkV
 
i finally got my lazy rear in gear and tested my suggestion. it did work for
me, so perhaps your subform reference is incorrect.

i'll explain what i meant by "subform control". open your form in design
view. click on the subform to select it, and open the Properties box (if not
already open). click on the All tab - at the top of the properties list,
you'll see a Name property; that refers to the control name - not the
subform name.
any form can be used as a subform. within the main form, it is contained in
a subform *control*, which has some of the same properties and behaviors as
other controls, such as text boxes. and the control can have any *name* you
give it, it does not have to match the name of the subform object it holds.
the actual name of the form object, that you're using as a subform, is in
the Source Object property of the subform control. clear as mud? here's an
example:

in my test, my main form is named frmMain. the form i'm using as a subform
is named frmMainSub. in frmMain, the subform control is named Child1 and
its' Source Object is frmMainSub. to refer to the subform control in code
*as a form* (from an event in the main form) the syntax is

Me!Child1.Form

hth
 
Thanks Tina. Your description of the syntax was helpful.
I think that is one area of access that gives me the
biggest problems.

I tried your suggestion but was still not able to make it
work. I'm wondering if the queries it is based on are to
blame.

However, I did find a solution that did work for me, so I
guess I'm happy for now (see below). I basically moved
the focus to each subform, and then set the enter event to
trigger a docmd to move to the first record.

Thanks again for your help
MarkV
--------------------------------------------
Private Sub cbxCustomerID_AfterUpdate()
'Move focus to each of the subforms to trigger the enter
event
' to execute code in subform enter event, then return to
this combo box

Me!subfrmQuotesByCustomerChild.SetFocus
Me!subfrmContactList2Child.SetFocus
Me!cbxCustomerID.SetFocus

End Sub
-----------------------------------------------
Private Sub subfrmContactList2Child_Enter()
'Move the record pointer to the first record in the
recordset
DoCmd.GoToRecord , , acFirst
End Sub
------------------------------------------------
Private Sub subfrmQuotesByCustomerChild_Enter()
'Move the record pointer to the first record in the
recordset
DoCmd.GoToRecord , , acFirst
End Sub
 
i wouldn't think so, since you say the records do come up - it's just the
focus that doesn't suit you.
i see that nobody else has stepped in with better help than i've been able
to come up with. at this point i'm willing to look at your db - if it's
Access97 or newer - and see if i can fix the problem. if you want to send it
to me, do the following:

make a copy. in the copy, delete all proprietary data and enter some
appropriate "dummy" data for system testing.
compact the db.
zip to 1 MB or smaller (you can remove db objects that don't affect the area
in question, if necessary) - i have WinZip to unzip at my end.
reference the Access newsgroups in the email subject line, and copy/paste
the posts into the message so i'll remember what we're talking about. attach
the zip file and email to ttacc_kill_all_spam_ess1 at yahoo dot
com, removing the underscores and all the text between them.
 
Back
Top