Pop Up form filters underlying form

[janerussel]

I have a modal pop up form for creating a new employee. The form requires FName, LName fields and has an auto number field for the unique record. When the record is saved and the form is closed, I want to go to the new record in the main form which is the last record in the dataset. How can I pass this command to the main form from the pop-up form on closing.

 

[TheAceMan1]

How are ya janerussel . . .

Since you say the popup has an autonumber field I'm preceding on the premise that the popup form is bound. Try the following in the popup forms [blue]AfterUpdate[/blue] event ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim frm As Form, Cri As String
   
   Set frm = Forms![[purple][b]MainFormName[/b][/purple]]
   
   frm.Requery
   Cri = "[[purple][b]AutonumberFieldName[/b][/purple]] = [red][b]'[/b][/red]" & Me![[purple][b]AutonumberFieldName[/b][/purple]] & "[red][b]'[/b][/red]"
   frm.Recordset.FindFirst Cri
   
   Set frm = ""[/BLUE]

If the autonumber is numeric remove the two single quotes in red [red]'[/red]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[janerussel]

I get this error
compile error:
Type mismatch

the "" on this line is highlighted.
Set frm = ""

 

[TheAceMan1]

janerussel . . .

Hmmmm . . . post the code as you have it! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[janerussel]

Here it is. The VolID is an autonumber.
Thank you for your help.

Private Sub Form_AfterUpdate()
Dim frm As Form, Cri As String

Set frm = Forms![frmMain]

frm.Requery
Cri = "[VolID] = " & Me![VolID] & ""
frm.Recordset.FindFirst Cri

Set frm = ""

End Sub

 

[PHV]

Replace this;
Set frm = ""
with this
Set frm = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

PHV . . .

Ya beat me to it!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[janerussel]

Sorry, but that doesn't work. The Main form goes to the first record. If I could just get the main form to go to the last record it would work, since the new record is always the last record.

 

[TheAceMan1]

janerussel . . .

Realize . . . if the recordsource of the mainform is a query or SQL that has an ODRER BY clause, that record may not be the last in the mainform. As far as the code is concerned it doesn't matter, as we'll find it where ever it is in the mainform . . . via [blue]VOID[/blue].

Since you say it didn't work we'll try a little more brut force. Copy and overwrite the popups AfterUpdate event with the following:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim frm As Form, Cri As String
   
   Set db = CurrentDb
   Set frm = Forms!frmMain
   frm.Requery
   Cri = "[VOID] = " & Me!VOID
   
   Set rst = db.OpenRecordset(frm.RecordSource, dbOpenDynaset)
   rst.FindFirst Cri
   
   If rst.NoMatch Then
      MsgBox "Record Not Found!"
   Else
      frm.Bookmark = rst.Bookmark
   End If
   
   Set frm = Nothing
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[janerussel]

Hi Thanks again but now I get this error message.
Run time error 3622;
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL server table that has an IDENTITY column.