Button requeries form but form goes to first recordset!

J

Jeff

I have a form with the following:

txtbox numStudentRef
txtbox txtName

Tab Control [Information]
On each tab control is a subform control for each tab page.

On the form in the first tab page subform control are personal details
and an autonumber Student Ref;

When a user inputs infor the autonumber is shown, perfect. but the
numStudentRef on the mainform remains blank.

This means that the other subforms in the other subform controls on the
other tab controls don't have anything to reference yet and they don't
filter.

I put a requery on a button on the mainform, and then all the subforms
can show the same filtered record, but when it requeries, it always goes
back to the first record.

How can I get the button to requery the mainform and maintain the
present record?

Any ideas greatly appreciated,

Jeff
 
M

Marshall Barton

Jeff said:
I have a form with the following:

txtbox numStudentRef
txtbox txtName

Tab Control [Information]
On each tab control is a subform control for each tab page.

On the form in the first tab page subform control are personal details
and an autonumber Student Ref;

When a user inputs infor the autonumber is shown, perfect. but the
numStudentRef on the mainform remains blank.

This means that the other subforms in the other subform controls on the
other tab controls don't have anything to reference yet and they don't
filter.

I put a requery on a button on the mainform, and then all the subforms
can show the same filtered record, but when it requeries, it always goes
back to the first record.

How can I get the button to requery the mainform and maintain the
present record?


SInce Requery could concievably retrieve a completely
different set of records, there's no way for Access to
assume it can get back to the same record that was current
before the requery.

The standard way to deal with this is to save the current
record pk field before the requery and then search for that
record after the requery:

Dim lngKey As Long
lngKey = Me.idfield

Me.Requery

With Me.RecordsetClone
.FindFirst "idfield = " & lngKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
 
G

Guest

I have a similar setup with a customer information system.

I put the primary key control directly on the first page of the tab control
on the main form, not inside a subform. I use a text box in the header of the
main form to display the primary key, so it's always visible when paging
through the tabs.

Requerying the main form is not necessary with this setup.
 
J

Jeff

Thank you both for your help,

The code from Marsh got it working without altering the form.

Appreciate the help,

Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top