Requery, but return to the current record

[Macsmasher]

Running Access 07.

I have Form1, from which I launch Form2 with the same data and always goes
to the same record as Form1. I make changes to that data on Form2. When I
close it, I want to requery Form1 (refresh doesn't do it). But the problem
with Requery is that you are then at the beginning of the recordset rather
than the current record on which you're working.

I want to Requery Form1 when closing Form2, and remain at that current
record rather than the beginning of the records. I'm sure it's easy, but I
can't seem to do it.

Thanks in advance,
Larry

 

[Jeff Boyce]

You've described "how" you are trying to do something (start with form1, put
the same data in form2, edit it in form2, close form2, and return to form1,
to see the edited data).

Now, how about describing "why"? Why use two forms instead of just one?
What will having the second form allow you to do that you can't do with just
one form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Macsmasher]

Why isn't the issue. But maybe a more detailed explaination would help since
"all" the data isn't the same.

Form1 is a sales opportunity. Form2 is for creating a quote that is related
to the sales opportunity using the key field OpportunityID to link the
tables. When a new quote is created on Form2 for that opportunity, I need
the data on Form1 to reflect that because on Form1 there is a txtBox that
displays the Quote Number. Requery does update the data on Form1, but as I
said, takes you to the first record in the set.

 

[Macsmasher]

FYI, this works great. The code lives in the cmdClose button on Form2.
frmOpportunityDetails = Form1:

With Forms!frmOpportunityDetails
Dim intCurrent As Integer
intCurrent = .OpportunityID
.Filter = ""
.FilterOn = False
.Requery
.Recordset.FindFirst "OpportunityID = " & intCurrent
End With

DoCmd.Close acForm, Me.Name, acSaveNo

It's important to note that without first clearing the Filter, FindFirst
will not work.

-Larry