Form Requery

I have a command button on a form to create new records for the data shown on the form. When the code finished I want the form to have the new record available. I have this code at the end of the button code:

Forms("frmDocumentDetail").Requery

It does not work. The records do not update unless I exit the form or do something like hit the sort records button.

TimD

Answer this question

Form Requery

Bill Robertson - MSFT
No sure this is the answer but try adding

Forms("frmDocumentDetail").refresh

Forms("frmDocumentDetail").repaint

after the requery.
Gentry Bieker
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.
Igornachov
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.)
larsbg

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.
Gjergji Stasa
Bound to a table.
Admin Matthew
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
Tom C.
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
E_STR

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.
Rajeev Mehta
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.
Zvuk
Nope, did not work.

Thanks for the response.
Labutin

Hello,

Try calling the method directly on the form instance.

Me.Requery
David Hanneman

Where do you call Forms("frmDocumentDetail").Requery
mikesql
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.
evali
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
Form Requery

Answer this question