Creating new record in linked form

I have done this before, without using any coding (just the property sheet in
Access), but can't remember how to do it. Hope someone can help.

I have two forms linked. I have a main form, with a button on it to the
second form. I used the Wizard so that only the related records on the second
form show up. That works fine. Here's my problem: If I am creating a brand
new customer on my main form, and click on the button to enter related data
on the second form, the info does not show up. I have running off of a query
with the Key field joined, so that's where the problem is.... it doesn't
exist in the second form yet, so the record is not in the query. I know there
is another, easy way to do this.
?????
Thanks.

When you add a new record in a form, it is not yet in the underlying tables.
It only exists in the form's recordset. To force it to update the data into
the table, you have to requery the form. When you do, the form recordset
will then go back to the first record in the form recordset. There is a way
to make the form appear to stay on the same record. You first need to save
the primary key field of the current record, requery the form, then use the
FindFirst method to return to the record. You can do that in the click event
of the command button where you open the other form. This example assumes
your table has an autonumber primary key.

Dim lngPrimeKey As Long

If Me.Dirty Then
lngPrimeKey = Me.Recordset![PrimeKey]
Me.Requery
With Me.RecordsetClone
.FindFirst "[PrimeKey] = " & lngPrimeKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Docmd.OpenForm "TheOtherForm",...
--
Dave Hargis, Microsoft Access MVP


"4charity" wrote:

I have done this before, without using any coding (just the property sheet in
Access), but can't remember how to do it. Hope someone can help.

I have two forms linked. I have a main form, with a button on it to the
second form. I used the Wizard so that only the related records on the second
form show up. That works fine. Here's my problem: If I am creating a brand
new customer on my main form, and click on the button to enter related data
on the second form, the info does not show up. I have running off of a query
with the Key field joined, so that's where the problem is.... it doesn't
exist in the second form yet, so the record is not in the query. I know there
is another, easy way to do this.
?????
Thanks.