Run Append Query after update (1 Viewer)

urobee · Aug 28, 2019

Hy,
I have two table: "Master" and "Temp"
The form save the records in to "Temp" table.
I made the append Query it works fine when I run it manually.
Is there any option to run the append query automatically after form update/or before close to save the records to the "Master" table too?
Thanks!

arnelgp · Aug 28, 2019

why not use Master table directly?

urobee · Aug 28, 2019

I need the "Temp" table to collect data until it reach 30 records then I make a report and clear the table and start it again. In the "Master" table I collect all the data without clear any records.
---------

EDIT:
I made an event on close form with macro builder so i was able to add the query i want to use.

arnelgp · Aug 28, 2019

so the form is for data entry only?
add a button that will save your 30 entries to Master table and at the same time
clear the Temp table:
on the buttons (caption maybe "Save to Master and Clear"), add code to it's Click Event:

Code:

Private Sub yourButtonName_Click()
    Dim i as Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = Currentdb
    Set rs = db.Openrecordset("Master", dbOpenDynaset) 
    With Me.RecodsetClone
        .MoveFirst
        While Not .EOF
            rs.AddNew
            For i = 0 to .Fields.Count -1
               rs.Fields(.Fields(i).Name) = .Fields(i)
            Next
            rs.Update
            .MoveNext
        Wend
        .MoveLast
        While Not .BOF
           .Delete
           .MovePrevious
        Wend
    End With
End Sub

urobee · Aug 28, 2019

wow, great! Thank You!

arnelgp · Aug 29, 2019

have you already tried it?
youre welcome!

urobee · Aug 29, 2019

arnelgp said:
so the form is for data entry only?
add a button that will save your 30 entries to Master table and at the same time
clear the Temp table:
on the buttons (caption maybe "Save to Master and Clear"), add code to it's Click Event:

Code:

Private Sub yourButtonName_Click() Dim i as Integer Dim db As DAO.Database Dim rs As DAO.Recordset Set db = Currentdb Set rs = db.Openrecordset("Master", dbOpenDynaset) With Me.RecodsetClone .MoveFirst While Not .EOF rs.AddNew For i = 0 to .Fields.Count -1 rs.Fields(.Fields(i).Name) = .Fields(i) Next rs.Update .MoveNext Wend .MoveLast While Not .BOF .Delete .MovePrevious Wend End With End Sub

Hy,
today I've tried to use your code but i got this error message: "Method or data member not found" and this part is selected: Me.RecodsetClone
Access 2010 and here are the activated references:

arnelgp · Aug 29, 2019

I don't know if it exists in a2010. you could replace it with Me.Recordset.Clone or just Me.Recordset.

Minty · Aug 29, 2019

@Arne - Its a typo missing an r - RecordsetClone

arnelgp · Aug 29, 2019

good eyes.
there is also another one:

With Me.RecodsetClone

should be:

With Me.RecordsetClone

**
I tested it before posting. something is eating my "r".

Run Append Query after update (1 Viewer)

urobee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

urobee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

urobee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

urobee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread