the Dlookup function retrieves single data from a table. Is there a function that works the opposite of this? Exports data selected from the form to a given table. It cannot be anything from SQL. Opposite to Dlookup??

CodePudding user response：

Sure. If you insist on avoiding SQL:

1. Create a new update query in the designer.
2. Use =Forms!MyFormName!MyControl as the value (obviously substituting MyFormName and MyControl with the correct values).
3. Execute the update query (manually, or in code with CurrentDb.Execute "nameOfMyQuery")

CodePudding user response：

You can use the RecordsetClone as source and DAO to copy the records.

Then, record the selected records with the mouse, and call a function similar to this:

Option Compare Database
Option Explicit

Public SubSelHeight As Integer
Public SubSelTop    As Integer

Public Function GetSelectedFormRecords()
     
     Dim Index      As Long
     Dim Form       As Form
     Dim Records    As DAO.Recordset
     Dim Copyset    As DAO.Recordset
          
     ' Get the form and its recordset.
     Set Form = Me  ' or a subform: Me!NameOfSubformControl.Form
     Set Records = Form.RecordsetClone

     Set Copyset = CurrentDb.OpenRecordset("Select * From YourCopyTable")
     
     ' Move to the first record in the recordset.
     Records.MoveFirst
     ' Move to the first selected record.
     Records.Move SubSelTop - 1
         
     For Index = 1 To SubSelHeight
        ' MsgBox Records!Id.Value
        ' Copy record.
        Copyset.AddNew
            Copyset.Field1.Value = Records.FieldX.Value
            Copyset.Field2.Value = Records.FieldY.Value
            Copyset.Field3.Value = Records.FieldZ.Value
            ' More fields.
        Copyset.Update
        Records.MoveNext
     Next
     Records.Close
     Copyset.Close

End Function


Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Const EmpiricMaxX   As Single = 255
    
    Debug.Print "Mouse X:", X
    
    If X < EmpiricMaxX Then
        ' Mouse click on record selector.
        MsgBox "Select"
    End If

End Sub


Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

    SubSelTop = Me.SelTop
    SubSelHeight = Me.SelHeight
    
End Sub