Jumping to a specific record in the parent form

[tcarnahan1]

Hi,

I have an Access form ("Form1") with a button that opens another form ("Form2"). Form2 is not a subform. Form2 has a number of text controls that are unbounded. There is a button called "Commit". When it is clicked, it takes the values in the unbounded controls and inserts them into various tables. One of these is inserted into the source table as Member_Id.

Once the values are inserted, the following is supposed to happen:

1) Focus is set on Form1.
2) The Form1 Current record is set to the Member_Id that was inserted into its data source table.
3) Form2 closes leaving Form1 with the focus displaying the Member_Id record previously inserted.

I am able to do the record insertion, but I can't close Form2. Would anyone have an idea of what I need to do?

Here is the code I used:

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''

Sub Commit_Record_and_Close()
 
Dim strCriteria As String
 
Dim rst As DAO.Recordset
 
' Insert values including Member_Id into source table HERE.
'
    Forms("Form1").SetFocus
 
    Set rst = Forms("Form1").RecordsetClone
 
    strCriteria = "[Member_Id] = " & CStr(lng_Member_Id) ' Member_Id previously set.
 
    rst.FindFirst strCriteria
 
    Forms("Form1").Bookmark = rst.Bookmark
 
    Forms("Form1").Requery
 
    Forms("Form1").Controls("cbo_Selector").Requery ' This is a drop down that contains Member_Id
 
    ' The following 2 lines were inserted in desparation because I couldn't get Form1 to requery.
 
    DoCmd.OpenForm "Form1", acDesign
 
    DoCmd.OpenForm "Form1", acNormal, , strCriteria
 
    Me.SetFocus ' Form2
 
    DoCmd.Close acForm, Me.Caption, acSavePrompt ' Form2
 
 
End Sub

[zmbd]

OK
The following, although written in the VBE, is essentially "air code" because I don't have your database.

Take a look at the code, back up your database, cut and paste this into your form's code, DOUBLE CHECK MY CODE for errors by trying to compile the data code from the debug menu.

+I'm sure that I've missed something here; however, impossible to tell without your help.

Option Compare Database
Option Explicit
'
'>> You should have the above two lines in all of your modules and form codes.
'>> These can be set by default under the VBE>Menu>Tools>Options
'
 
 
Sub Commit_Record_and_Close()
 
  Dim strCriteria As String
  Dim rst As DAO.Recordset
  '
  ' Insert values including Member_Id into source table HERE.
'
'>> You have no code that would insert anything into a record set
'>> at this point. I am guessing that you omitted this code for
'>> clarity?
'
  '
'>> Make sure that  your form is actually loaded
'>> - unless you set the second form as dialog users tend to close things unexpectedly
  If CurrentProject.AllForms("Form1").IsLoaded Then
    Forms("Form1").SetFocus
    Forms("Form1").Requery
  Else
    DoCmd.OpenForm FormName:="form1", View:=acNormal
  End If
'
'>> You have to get your form to requery prior to setting your recordset based on the form's recordset
  Set rst = Forms("Form1").RecordsetClone
  strCriteria = "[Member_Id] = " & CStr(lng_Member_Id) ' Member_Id previously set.
  '
  rst.FindFirst strCriteria
  '
'>> YOU MUST check to see if there is a matching record before you attempt to move to that record.
  If Not rst.NoMatch Then
    Forms("Form1").Bookmark = rst.Bookmark
    Forms("Form1").Controls("cbo_Selector").Requery ' This is a drop down that contains Member_Id
'
'>> sometimes a form will not update the interface so we'll force a redraw
    Forms("Form1").Repaint
    '
'>> Remove these once we have things happy
    ' The following 2 lines were inserted in desparation because I couldn't get Form1 to requery.
    'DoCmd.OpenForm "Form1", acDesign
    'DoCmd.OpenForm "Form1", acNormal, , strCriteria
'
    Me.SetFocus ' Form2
  Else
    MsgBox Prompt:="The expected record for " & vbCrLf & _
      strCriteria & vbCrLf & "was not found" & vbCrLf & _
      "Please contact the person responsible for this database", _
      Buttons:=vbCritical + vbOKOnly, Title:="Error: Missing Record"
  End If
  '
'>> RULE OF THUMB - If you Open it Close it, If you Set it Clear it
  If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing
  End If
'>> You should close the record set BEFORE closing the record source
'The form caption will not work here, you have to have the form's actual name :)
    DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSaveNo   ' Form2
'
'Error trapping?
End Sub

[PhilOfWalton]

I'm sure there must be a reason for using 2 forms to enter your data, but I would be interested to find out the reason.

You mention that the data has to be entered into various tables. Usually the form is based on a query that includes those tables.

Don't forget that the data entered on a form isn't saved until one of several things happen like
1) Moving to a different record,
2) Closing the form,
3) Using a dedicated command button to "Save" the record.

Saving the record can be preceded by asking for confirmation that the data is OK.

Phil