423,473 Members | 2,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

Jumping to a specific record in the parent form

P: 1
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:

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

Expand|Select|Wrap|Line Numbers
  1. Sub Commit_Record_and_Close()
  2.  
  3. Dim strCriteria As String
  4.  
  5. Dim rst As DAO.Recordset
  6.  
  7. ' Insert values including Member_Id into source table HERE.
  8. '
  9.     Forms("Form1").SetFocus
  10.  
  11.     Set rst = Forms("Form1").RecordsetClone
  12.  
  13.     strCriteria = "[Member_Id] = " & CStr(lng_Member_Id) ' Member_Id previously set.
  14.  
  15.     rst.FindFirst strCriteria
  16.  
  17.     Forms("Form1").Bookmark = rst.Bookmark
  18.  
  19.     Forms("Form1").Requery
  20.  
  21.     Forms("Form1").Controls("cbo_Selector").Requery ' This is a drop down that contains Member_Id
  22.  
  23.     ' The following 2 lines were inserted in desparation because I couldn't get Form1 to requery.
  24.  
  25.     DoCmd.OpenForm "Form1", acDesign
  26.  
  27.     DoCmd.OpenForm "Form1", acNormal, , strCriteria
  28.  
  29.     Me.SetFocus ' Form2
  30.  
  31.     DoCmd.Close acForm, Me.Caption, acSavePrompt ' Form2
  32.  
  33.  
  34. End Sub
Aug 2 '18 #1
Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,283
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. '
  4. '>> You should have the above two lines in all of your modules and form codes.
  5. '>> These can be set by default under the VBE>Menu>Tools>Options
  6. '
  7.  
  8.  
  9. Sub Commit_Record_and_Close()
  10.  
  11.   Dim strCriteria As String
  12.   Dim rst As DAO.Recordset
  13.   '
  14.   ' Insert values including Member_Id into source table HERE.
  15. '
  16. '>> You have no code that would insert anything into a record set
  17. '>> at this point. I am guessing that you omitted this code for
  18. '>> clarity?
  19. '
  20.   '
  21. '>> Make sure that  your form is actually loaded
  22. '>> - unless you set the second form as dialog users tend to close things unexpectedly
  23.   If CurrentProject.AllForms("Form1").IsLoaded Then
  24.     Forms("Form1").SetFocus
  25.     Forms("Form1").Requery
  26.   Else
  27.     DoCmd.OpenForm FormName:="form1", View:=acNormal
  28.   End If
  29. '
  30. '>> You have to get your form to requery prior to setting your recordset based on the form's recordset
  31.   Set rst = Forms("Form1").RecordsetClone
  32.   strCriteria = "[Member_Id] = " & CStr(lng_Member_Id) ' Member_Id previously set.
  33.   '
  34.   rst.FindFirst strCriteria
  35.   '
  36. '>> YOU MUST check to see if there is a matching record before you attempt to move to that record.
  37.   If Not rst.NoMatch Then
  38.     Forms("Form1").Bookmark = rst.Bookmark
  39.     Forms("Form1").Controls("cbo_Selector").Requery ' This is a drop down that contains Member_Id
  40. '
  41. '>> sometimes a form will not update the interface so we'll force a redraw
  42.     Forms("Form1").Repaint
  43.     '
  44. '>> Remove these once we have things happy
  45.     ' The following 2 lines were inserted in desparation because I couldn't get Form1 to requery.
  46.     'DoCmd.OpenForm "Form1", acDesign
  47.     'DoCmd.OpenForm "Form1", acNormal, , strCriteria
  48. '
  49.     Me.SetFocus ' Form2
  50.   Else
  51.     MsgBox Prompt:="The expected record for " & vbCrLf & _
  52.       strCriteria & vbCrLf & "was not found" & vbCrLf & _
  53.       "Please contact the person responsible for this database", _
  54.       Buttons:=vbCritical + vbOKOnly, Title:="Error: Missing Record"
  55.   End If
  56.   '
  57. '>> RULE OF THUMB - If you Open it Close it, If you Set it Clear it
  58.   If Not rst Is Nothing Then
  59.     rst.Close
  60.     Set rst = Nothing
  61.   End If
  62. '>> You should close the record set BEFORE closing the record source
  63. 'The form caption will not work here, you have to have the form's actual name :)
  64.     DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSaveNo   ' Form2
  65. '
  66. 'Error trapping?
  67. End Sub
Aug 2 '18 #2

PhilOfWalton
Expert 100+
P: 1,353
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
Aug 2 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.