0

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??

mokko kolo
  • 1

2 Answers2

0

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")
Heinzi
  • 167,459
  • 57
  • 363
  • 519
0

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Gustav, thx you for the code proposal, but there is sql in it. I can't use it because "YourCopyTable" is different every time. The form is intended to be universal and supports about 16 different tables. With Dlookup I import selected cell from table, but when I close the form I want to update the boxes. Maybe there is some function inverse to Dlookup? – mokko kolo Jan 14 '22 at 17:52
  • This makes no sense. Somehow you must tell which tables to use - "a given table" doesn't come by magic or random. Perhaps you use an unbound form? If so, bind it to the table, and it will update automatically without all this code, but you still have to specify the table name as source. – Gustav Jan 14 '22 at 18:44
  • Thx for your interest in the topic. In the form there is a ComboBox and in it the number (ID) of the document, example "TT-HJ-26154". The letters HJ are the name of the table. I want to update some of the newly entered data only (information about the document of correction, revision date, notes, etc..) in the HJ table. When using sql update, for example, I need to have 16 different sql strings. – mokko kolo Jan 14 '22 at 20:19
  • So you have 16 tables, identical apart from their names? Read up on _normalisation_ and redesign to have one table only. Also, it sounds like you miss knowledge about the core functions and features of Access, forms, and tables. Go for a _tutorial_ before proceeding, as _one bound form_ can do this with zero code, indeed not 16 queries. – Gustav Jan 14 '22 at 22:02
  • 1
    Gustav - You were right, one table improves the work, but I had to add additional columns: one with the document type and the other with the numbering of the new documents. the form with document type filter buttons now works smoothly. The only problem I had was adding a new record with the same number (number without duplicates) of the document by 2 people at the same time: Err.Number 3022 but I managed to solve the error handling (the second person gets the number higher by 1). THX! – mokko kolo Jan 22 '22 at 15:13