Form Requery

Form Requery

• Hongtao Liao

  How do I tell the form to execute it I have hundreds of records that the form cycles through. I would not want it to execute for every record change.
  
  

• valeneo

  I'm sorry about that Tim...

  I did a very basic example and got the same error as your having. Me.Requery does work. So its really a case of notifying the document form when the new record form is finished saving.

  You can do this by passing the Document form to the New Record form and then have the New Record form call the Requery method.

  'code in the document form
  Dim frmNewRec As New Form_NewRecord

  Private Sub cmdOpen_Click()
  'set the new records parent form and display the new record form
  frmNewRec.ParentForm = Me
  frmNewRec.Visible = True
  End Sub

  That code is in the Document form that displays the New Record form. It passes itself to the New Record form using a property. The following is in the New Record form. Once the save button is pressed the Document form that was set is requeried.

  'this is the code in the new record form
  Dim frmParent As Form

  Public Property Get ParentForm() As Variant
  ParentForm = frmParent
  End Property

  Public Property Let ParentForm(ByVal vNewValue As Variant)
  Set frmParent = vNewValue
  End Property

  Private Sub cmdSave_Click()
  'save the record and requery the parent
  frmParent.Requery
  End Sub
  

  Give that a go.

  
  
  

• tori K

  I made the New HLD form modal and popup. I put a msgbox right after the form call. The msgbox popped up witht the form which told me the requery had already executed.
  
  
  
  

• kmitty

  Hi Tim, I think the problem is the recordset used in the form has become out of sync with the information in the table. When you close the form and then reopen it your refreshing the forms recordset.

  Requery should of worked but I think you need to call it from within the form. If you set the new record form to be a popup with modal then execution will stop while the form is displayed. Once the form is closed you can refresh the data.

  Private Sub comNewHLD_Click()
  NewHLD ' Creates a form to create a new record, if new record form is popup with modal then code won't continue until its closed.
  Me.Requery 'once form is closed then requery the document form
  End Sub

  If that doesn't work then you might need to requery the forms recordset directly.

  Like this...

  Private Sub comNewHLD_Click()
  NewHLD ' Creates a form to create a new record, if form is modal then code won't continue until the form is closed.
  Dim rst As DAO.Recordset
  Set rst = Me.Recordset
  rst.Requery
  End Sub

  Also I don't think you want to close the Document form just leave it open. Show the New Record form as a pop up dialog with modal and on it's save button save your record and close. Once execution goes back to the Document form then requery the data.

  Let me know if that makes sense and if it works. This is fixable.

  
  
  

• Telexer

  Bound to a table.
  
  

• mbtrost

  Sure,
  I have a form that displays records from a table. There is a sub form linked to a related table for sub documents. The data tracks a development documentation process. As documents get updated we create a new version of the top document and one or more of the sub documents. I restricted the user from creating a new record and placed a Command button on the form to run a function to control the process. It prompts the user for the required fields and creates a new record. The document name is an indexed no dupes field. On exit of this create form it returns to the main calling form.
  
  Command Button code (short version) from the main form:
  
  Private Sub comNewHLD_Click()
  NewHLD ' Creates a form to create a new record
  ' This was not in the original try
  DoCmd.Close acForm, "frmDocumentDetail", acSaveNo
  End Sub
  
  
  Save button from called form:
  
  Private Sub Command13_Click()
  
  Dim strMessage As String
  
  strMessage = ""
  
  If Len(txtHLD.Value) < 24 Then
  strMessage = strMessage & "New HLD Document Id is too short!" & vbCrLf
  End If
  If IsNull(txtHLD.Value) Then
  strMessage = strMessage & "New HLD cannot be blank" & vbCrLf
  End If
  If IsNull(txtTitle.Value) Then
  strMessage = strMessage & "Title cannot be blank" & vbCrLf
  End If
  If IsNull(cboType.Value) Then
  strMessage = strMessage & "Type cannot be blank" & vbCrLf
  End If
  If IsNull(txtFDSVersion.Value) Then
  strMessage = strMessage & "FDS Version cannot be blank" & vbCrLf
  End If
  If IsNull(txtRelease.Value) Then
  strMessage = strMessage & "Release cannot be blank" & vbCrLf
  End If
  If IsNull(txtComplexity.Value) Then
  strMessage = strMessage & "Complexity cannot be blank" & vbCrLf
  End If
  
  If strMessage <> "" Then
  MsgBox (strMessage)
  Else
  Dim db As Database
  Dim rsDocumentDetail As DAO.Recordset
  Dim strDocumentDetail As String
  
  strDocumentDetail = "tblDocumentDetail"
  
  ValidateUserVars
  
  Set db = CurrentDb
  
  Set rsDocumentDetail = db.OpenRecordset(strDocumentDetail, dbOpenDynaset)
  
  With rsDocumentDetail
  .AddNew
  ![DocumentId] = txtHLD.Value
  ![Title] = txtTitle.Value
  ![ObjectType] = cboType.Value
  ![FDSVersion] = txtFDSVersion.Value
  ![ReleaseNo] = txtRelease.Value
  ![Complexity] = txtComplexity.Value
  If Mid(![DocumentId], 2, 2) = "WF" Then
  ![FunctionalArea] = Mid(![DocumentId], 2, 3)
  Else
  If Mid(![DocumentId], 2, 2) = "AM" Then
  [!FunctionalArea] = "SD"
  Else
  ![FunctionalArea] = Mid(![DocumentId], 2, 2)
  End If
  End If
  ![Status] = "HLD Not Started"
  ![FlagCurrentVersion] = True
  .Update
  End With
  
  rsDocumentDetail.Close
  db.Close
  Set rsDocumentDetail = Nothing
  Set db = Nothing
  strMessage = "Saved!"
  MsgBox strMessage, vbOKOnly, "Saved"
  ' I had the Requery code here before. No joy.
  DoCmd.Close acForm, Me.Name, acSaveNo
  ProtectFormCode
  End If
  
  Dim stDocName As String '
  Dim stLinkCriteria As String '
  stDocName = "frmDocumentDetail"
  DoCmd.OpenForm stDocName, , , stLinkCriteria
  End Sub
  

• CRickert

  Hello,

  Try calling the method directly on the form instance.

  Me.Requery

  
  
  

• PiperNavajo

  OK, that worked. Thank you. I really appreciate the time you have put into this.
  
  Now...
  
  When I went to execute the New Record a second time I got an error. I was closing the New Record form. I changed it to Me.Visable =False, but when I clcik it the second time the data is still there. I can clear the fields upon save, but I would like to close that form. How would I do that
  
  
  
  

• Roger_MS

  Where do you call Forms("frmDocumentDetail").Requery

  
  
  

• mspence

  At the end of the button code.
  
  My current work around is to call a function to execute the code. The first thing the function does is close the form and the last thing is to open the form. This works, however it sets the records back to the beginning.
  
  I have tried to Requery in multiple places with no joy. (e.g. On Dirty, On Change, etc.)
  
  
  
  

• Kuba B

  No sure this is the answer but try adding

  Forms("frmDocumentDetail").refresh

  Forms("frmDocumentDetail").repaint

  after the requery.

  
  

• Sambuddha

  Nope, did not work.
  
  Thanks for the response.
  
  
  
  

• Bob Willer

  Excellent Tim got a good idea now, one final question, are the controls on the main form bound to fields of a query/table or do you populate their values yourself with code
  
  

• thea.umali

  Hi Tim,

  Could you expand a little on the steps your codes doing, start from the beginning of the process i.e displaying a form that lets user enter data. You don't need to be that detailed. Just give me an overview so I can picture what your program does during the process. Maybe post some code as I'm not 100% sure how the systems working.

  
  
  
• Form Requery