Append and Add Results From A Filtered Form Into Table (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 07:22
Joined
Jun 7, 2018
Messages
100
Hi guys!

Im sure the answer is somewhere, but after a day of googling, I still need to ask. Please help :)

What I want to do:
I have a form, with filters on the headers, and I want to send the filtered results (records) to a table. Not overwrite, but add them.

So what I did is copying the data to the table, but Unfiltered!

This is my code so far:


Code:
Private Sub Command14_Click()
Dim rs As DAO.Recordset
Dim tblRS As DAO.Recordset
Dim strSql As String
Dim varRecCnt, cntr
strSql = Forms!calculation.RecordSource

Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveLast
rs.MoveFirst
varRecCnt = rs.RecordCount
Set tblRS = CurrentDb.OpenRecordset("chart")
          
For cntr = 1 To varRecCnt
     tblRS.AddNew
     tblRS("employee") = rs("employee")
     tblRS("mistakes") = rs("mistakes")
     tblRS("lines") = rs("lines")
     tblRS("mistake_perc") = rs("mistake_perc")
     tblRS("datestamp") = rs("datestamp")
    
     tblRS.Update
     rs.MoveNext
Next cntr
tblRS.Close
Set tblRS = Nothing
rs.Close
Set rs = Nothing
End Sub

The filters I used on the form headers, are the ones like this for example
Code:
DoCmd.GoToControl "employee"
DoCmd.RunCommand acCmdFilterMenu
:

Hope someone knows what to do :) Thanks in advance!!!
 

vba_php

Forum Troll
Local time
Today, 09:22
Joined
Oct 6, 2019
Messages
2,884
I don't believe there is anyway to "see" the filtered records in a table when you are displaying them in a form because the form's source is always the same source....the definition of the table object, and the "filter" is simply a property of the form object. thus, they are not associated in anyway. however, you can certainly do this. but the question probably is not , "how do I do this?", but rather, coming from a solution provider, "why are you doing this?". It's kind of unusual, and personally I don't think I've ever seen this asked before. what's the reason, if I may ask?
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:22
Joined
Aug 11, 2003
Messages
11,696
You are lacking your filter in the recordsource... filter is a seperate property get it from your form, add it to the sql and presto...

Also alternatively you can open the same recordset on the form using Me.Recordset

Notes:
1)
Command14_Click
Please give your buttons legable names Command14 isnt very helpfull.

2)
Dim varRecCnt, cntr
Declaring variables as variants where you want integers here, is a bad habit that you need to break

3)
Instead of
Code:
Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveLast
rs.MoveFirst
...          
For cntr = 1 To varRecCnt
     rs.MoveNext
Next cntr
More conventional, more practical way is
Code:
Set rs = CurrentDb.OpenRecordset(strSql)
Do while not rs.EOF
     rs.MoveNext
Loop

I hope this helps you along in your project.
 

MushroomKing

Registered User.
Local time
Today, 07:22
Joined
Jun 7, 2018
Messages
100
It's kind of unusual, and personally I don't think I've ever seen this asked before. what's the reason, if I may ask?

Hi! Thanks for your answer!

Our management, wants to make daily reports based on the data they filter out in the form :)
That's it really. So they filter some data, copy it to the table with the button, and an Excel file will fetch the data from that table on a daily basis to create some charts.
 

vba_php

Forum Troll
Local time
Today, 09:22
Joined
Oct 6, 2019
Messages
2,884
Hi! Thanks for your answer!

Our management, wants to make daily reports based on the data they filter out in the form :)
That's it really. So they filter some data, copy it to the table with the button, and an Excel file will fetch the data from that table on a daily basis to create some charts.
did you try mailman's solution? it looks like it might work, and he's good at it anyway. can you try that before attempting some strange workaround? that's a bout all I do. but it's usually more complicated than necessary. so give his solution a try if you will please. thanks. then say something afterwards if it doesn't work.
 

MushroomKing

Registered User.
Local time
Today, 07:22
Joined
Jun 7, 2018
Messages
100
Thanks mailman!!!

I corrected the 3 points. But fail to add a filter to the sql that filters my form results.
Could you advise me on how to do that? Thanks!!!! :) Already helps alot
 

MushroomKing

Registered User.
Local time
Today, 07:22
Joined
Jun 7, 2018
Messages
100
strSql = Forms!calculation.RecordSource
Me.FilterOn = True

But that's A shot in the dark. Im still trying to find it
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:22
Joined
May 21, 2018
Messages
8,463
Your are making it hard, as stated
Rs = me.recordset
Will return the filter set of records.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 15:22
Joined
Aug 11, 2003
Messages
11,696
Me.FilterOn is to apply the filter, the filter is stored in a simular property.

Using Me.Recordset is definately a good way to go as well, as I allready wrote a few posts ago, thanks for re-itterating @MajP
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:22
Joined
May 21, 2018
Messages
8,463
Using Me.Recordset is definately a good way to go as well, as I allready wrote a few posts ago, thanks for re-itterating
@namliam
That is why I am wondering the OP is trying it the hard way, since the simple answer was provided.
 

MushroomKing

Registered User.
Local time
Today, 07:22
Joined
Jun 7, 2018
Messages
100
Your are making it hard, as stated
Rs = me.recordset
Will return the filter set of records.

Awesome! thanks for your help :). That indeed did what I needed it to do.

FINAL SOLUTION

Code:
Set rs = Me.Recordset

Thanks everyone! Always a pleasure here :)
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:22
Joined
Aug 11, 2003
Messages
11,696
Just for an educational part... Filters on the form are stored in Me.Filter :)

The fun part is you can actually keep those filters and "build a query" so you dont need to copy your data into a seperate table :(
Copying data is always a bad idea.
 

Users who are viewing this thread

Top Bottom