I going to suggest that you turn this around. It will be easier to code....
How about you have a form b that shows the "list", and the you click on what
record to edit....
When you close formA, you will be returned to that nice list..ready to do
battle with the next record.
further, by forcing the user to close the details form when done, the user
gets a real nice sense of closure. It is like closing the door on a car, and
then locking the doors.
So, the users views the record, possibility edits the record, and then they
close the form. The closing of the form not only saves the record, but also
gets the form out of the way, and the user is not staring right back at the
nice list of records....
Take a look at the following form for an idea:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
And, there is some more ideas here:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
If you are going to code a form to search, then you can try the following:
Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).
In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.
Then, in the txtLastName after update event you simply stuff the results
into that sub-form.
dim strSql as string
strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"
me.MySubFormname.Form.RecordSource = strSql.
the above code is how that search form example works.
The code behind the button to open up the form to the ONE record is also
very easy:
docmd.Openform "frmViewDetails",,,"id = " & me!id
You can see it is only ONE line of code to open the form..and send it to the
record of our choice....
If you make the details form modal (setting found in the "other" tab), then
the user will have to close the form to return to the continues "list"
form...
