critique the on click code, please! (1 Viewer)

[umair434]

Hi,


I am using the following code to add comments from a subform to a table. The subform is populated by a combo box:

Private Sub Command9_Click()

DoCmd.GoToRecord acDataForm, "frmMain", acNewRec
DoCmd.OpenQuery "qry - Archived"
DoCmd.OpenQuery "delete_qry_Final_Details"
DoCmd.OpenQuery "qry_Final_Details"
Me.frmsubform.Requery
Me.Combobox.Requery
Me.Combobox.Value = Null
End Sub


the problem is when I save the comments. and click on the next value of combo box, the subform doesn't populate. But if i close the form, open again - then it works.


what am I missing here? Thanks!


frmMain is the main form.
qry - Archived is used to archive the comments
delete_qry_Final_Details deletes the table the subform is based on
qry_Final_Details appends the table again

delete/append is used to ensure that already commented trailers do not appear in the combo box.

any other idea to approach this issue?

Thanks!

 

[ypma]

Without looking too deeply into the script if the coding works on closing the form , it could imply that the comment is saved on close ,i would insertT into your script a command to save the current record using :
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Just a thought

 

[umair434]

Hi! Im using the code on the on click button. the problem with that is that it saves the comments and runs all the queries everytime users presses this button to save the comments. Is there any alternative? or saving the comments can be faster and the queries are run as well?

Thanks!

 

[ypma]

Hi, sorry that did not work, I do not to pretend to be an expert and hope someone else can give you some better advice. How about entering the save record command after the update in the comments field? My reasoning is that you say that your script works fine after you have closed the form and then re-open so if we could automate that procedure could that be a short term solution?