Requery Issues

[bibbyd01]

Hi all

I have a form which inserts a comment in to a table. As part of the vba I have the below, which I think should refresh the data to include the comment that's just been inserted in to the table, but it doesn't appear to work (I was sure it did when I looked 2 days ago).

[Forms]![frm_WIPcomments].Requery
[Forms]![frm_short_WIP].Requery

I have a work around which I can use which involves reopening the forms, but thought there would be an easier way?

 

[ypma]

I am no expert but would think you would need to save the record not requery the form! perhaps in the comments [Event Procedure] after update .would give you the desired result !

Private Sub commments AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub

 

[bibbyd01]

I didn't explain that too well. To add a comment, the user opens a specific form which then adds the correct details I need to a table, before closing the add comments form. The user then returns to the comments form and I want the newly inserted comments to appear on that form. I don't think that afterupdate would refresh the info on the comments form, but I'm not expert either!

 

[ypma]

Thank you for getting back to me, you did not say if you had tried the save record in after update of the comments field. I am not too clear as to what the scenario is, is there any chance you could upload the data base baring in mind the data protection act.

 

[bibbyd01]

Below is the VBA code I'm using to update the comment. I would have to make a lot of deletions/changes to upload the database but I don't think it would help. All I need to do is refrseh the data in the forms frm_WIPcomments and frm_short_WIP.

Private Sub Command30_Click()
Dim err As Integer
Dim cnn2 As ADODB.Connection
Dim tblWIPcomments As ADODB.Recordset
Dim strCnn As String

'Check that all fields are filled in
txt_addcomment_comment.SetFocus
If txt_addcomment_comment = "" Then
err = err + 1
MsgBox "Please insert a comment!" & err
End If

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn2 = New ADODB.Connection
mydb = "C:\FS and WS management.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn2.Open strCnn

' Open contact table.
Set tblWIPcomments = New ADODB.Recordset
tblWIPcomments.CursorType = adOpenKeyset
tblWIPcomments.LockType = adLockOptimistic
tblWIPcomments.Open "tblWIPcomments", cnn2, , , adCmdTable


'get the new record data
tblWIPcomments.AddNew
tblWIPcomments!tblWIPcomments_ord = txt_addcomment_ord
tblWIPcomments!tblWIPcomments_commentdate = txt_addcomment_date
tblWIPcomments!tblWIPcomments_comment = txt_addcomment_comment
tblWIPcomments!tblWIPcomments_interiminv = cbx_addcomment_interiminv
tblWIPcomments!tblWIPcomments_finalinv = cbx_addcomment_finalinv
tblWIPcomments!tblWIPcomments_time = txt_addcomment_time
tblWIPcomments!tblWIPcomments_User = txt_addcomment_user
tblWIPcomments.Update


'refreshes other forms
[Forms]![frm_WIPcomments].Requery
[Forms]![frm_short_WIP].Requery

' Show the newly added data.
MsgBox "New comment on service order " & tblWIPcomments!tblWIPcomments_ord & " has been successfully added, and the form will now close"


'close connections
tblWIPcomments.Close
cnn2.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

'Closes open form
DoCmd.Close acForm, "frm_addwipcomment", acSaveYes

End Sub

 

[ypma]

Ok, I am now getting out of my depth .But one last input, you mentioned in your first post you have a work around in that you close and then open the form. Even though the comment is added the record is not save until you close the form. Is it worth trying to insert a save record command before the 'refreshes other forms. Code in your script. I will have to bow out and leave it to others. Sorry to have wasted your time
Regards Bob

 

[ypma]

I found this on the web and forward it as a possible answer to your question.

Me.Refresh saves the current record that you're working on. It will also retrieve any changes (but not additions or deletions) to any records shown in the current form. Any calculations on the form (unbound fields) are recalculated. Refresh does NOT reload the recordset. You do not lose your position in the form (you stay on the current record). Any new records added by other users will not be shown.
If you want to open another form or, especially, a report that contains the data on the current form, you need to issue a Me.Refresh command. I do this in my tutorial when we create the invoice based on the current order. You need a Me.Refresh to save the data to the table so that it will print correctly. You couldn't use Requery because it will put you back on record 1 of the recordset, which might not be what you want.


Article Source: http://EzineArticles.com/2258010

 

[bibbyd01]

Hi Bob

I want to view the deletions on the form, so this wouldn't work for this situation. I'm sure it's something to do with requery, but, in all honesty, it's not critical to the operation of the database so I'll keep plugging away and seeing if I can work out how to do this