![]() ![]() Duplicate the record in form and subform. Provided by Allen Browne, November 2. The example below shows how to duplicate the record in the main form, and also the related records in the subform. Change the highlighted names to match the names of your fields, table, and subform control. To use the code as is, add a command button to the Orders form in Northwind. ![]() This is likely the dumbest question that you will see on this forum this week, but I have looked at this all day and cannot figure it out. I have created a form that.
The code. Private Sub cmd. Dupe_Click(). Dim str. Sql As String. Dim lng. ID As Long. If Me. Dirty Then. Me. Dirty = False. If Me. New. Record Then. Msg. Box "Select the record to duplicate.". With Me. Recordset. Clone. . Add. New. Customer. ID = Me. Customer. ID! Employee. ID = Me. Employee. ID! Order. Date = Date. Update. . Bookmark = . Last. Modified. lng. ID = ! Order. ID. If Me.[Orders Subform]. Form. Recordset. Clone.Record.Count > 0 Then.Sql = "INSERT INTO [Order Details] ( Order.ID, Product.ID, Quantity, Unit.Price, Discount ) " & _.SELECT " & lng.How To Update Blackberry Smart Card Reader . Steinmetz Opel Astra Gtc Screensaver Pictures . ID & " As New. ID, Product. ID, Quantity, Unit. Price, Discount " & _. FROM [Order Details] WHERE Order. ID = " & Me. Order. ID & "; ". DBEngine(0)(0). Execute str. Sql, db. Fail. On. Error. Else. Msg. Box "Main record duplicated, but there were no related records.". End If. Me. Bookmark = . Last. Modified. End With. Msg. Box "Error " & Err. Number & " - " & Err. Description, , "cmd. Dupe_Click". Resume Exit_Handler. End Sub. Explanation. The code first saves any edits in progress, and checks that the form is not at a new record. The Add. New assigns a buffer for the new record. We then copy some sample fields from the current form into this buffer, and save the new record with Update. Ensuring the new record is current (by setting the recordset's bookmark to the last modified one), we store the new primary key value in a variable, so we can use it in the related records. Then, we check that there are records in the subform, and duplicate them with an append query statement. The query selects the same child records shown in the subform, and appends them to the same table with the new Order. ID. If you are not sure how to create this query statement for your database, you can see an example by mocking up a query and switching to SQL view (View menu, in query design.)So why did we use Add. New in the main form, but an append query statement to duplicate the subform records? Add. New gives us the new primary key value, which we needed to create the related records. The append query creates all related records in one step. We are able to move to the new record in the main form without having to Requery. Form. Refresh Method (Access)The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment. Syntaxexpression. Refreshexpression A variable that represents a Form object. Return Value. Nothing. Using the Refresh method is equivalent to clicking Refresh on the Home tab. Microsoft Access refreshes records automatically, based on the Refresh Interval setting on the Advanced tab of the Access Options dialog box, available by clicking the Microsoft Office button. Access Options. ODBC data sources are refreshed based on the ODBC Refresh Interval setting on the Advanced tab of the Access Options dialog box. You can use the Refresh method to view changes that have been made to the current set of records in a form or datasheet since the record source underlying the form or datasheet was last refreshed. In a Microsoft Access database, the Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method.When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source. Microsoft Excel Help Left Function In Excel on this page. In a Microsoft Access project (.Refresh method requeries the database and displays any new or changed records or removes deleted records from the table on which the form is based.The form is also updated to display records based on any changes to the Filter property of the form. Note. Example. The following example uses the Refresh method to update the records in the underlying record source for the form Customers whenever the form receives the focus: Private Sub Form_Activate(). Me. Refresh. See also. Concepts. Form Object.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |