form filter via vba/macro (1 Viewer)

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Before we go any further, can you explain you data tables. What is the relationship between the two. You have both tables in the query but you haven't joined them.
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
It's just an example database to try some things. Will there be a difference if they've a relationship?
 

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Take a look at Form1. Hopefully, this is what you require. If not, post back. If you have any questions, post back.
Hope this helps :)
 

Attachments

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Sorry,I've been ill the last days.
That's excactly what I was looking for!
Thank you very much Bob!!!
You helped me a lot.

Best regards
Georg
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Just for my understanding:
-sfTabelle1 is the subform?
- I tried it on my main DB
changed the sfTabelle1 to my subform
changed the cmbTabelle1 to my method Name
changed the Column name
but it don't work :/
Is there a difference when there are relationships between tables?
 

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Just for my understanding:
-sfTabelle1 is the subform?
- I tried it on my main DB
changed the sfTabelle1 to my subform
changed the cmbTabelle1 to my method Name
changed the Column name
but it don't work :/
Is there a difference when there are relationships between tables?
Yes, sfTabelle1 was the name I gave to sub form.

The method used, with the combo called sfTabelle1two, is the one most commonly used.
Sorry to hear that you've been unwell. I trust you're feeling a little better now :)

Let see if we can get it working for you.
Can you post a copy of the db. Remove all sensitive data. Compact it and Zip it. then attach it.
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Thank you!
frm_all and frm_qry_all are the main parts.

The filter on frm_all "Kategorie" works but the others don't.
Just can't figure out why...
 

Attachments

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Hi Georg

Amended db attached.

I have adjusted the code for the combo boxes.
I have added a combo box that uses the Primary Key rather than a text value from the table which is the usual method.
Of course, I had to add the Primary Key of table tbl_Gruppe to the query so that I could add it to the sub form.
IMHO "ID" is a poor name for a Primary key, especially when used in so many tables "GruppeID" for example would be much better.
Having seen the db I presume that your first language is German. I must compliment you on your English. I did wonder why you'd "miss-spelt" your user name :ROFLMAO: :unsure: :ROFLMAO:.
Anyway, I hope this helps. As always, post back if you have questions.
 

Attachments

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Thank you very much bob! Everything work's perfect!
So my main difference was the column Code part?
Yeah, I should work on my names for tables,queries and forms 😂
You're Right my main language is german. I appreciate that compliment. So the english class was worth it. 😂
Sometimes it's hard to explain your problems in a other language but more people speak english so you can get more answers/solutions.

Really apperciate your help.
You solved all my problems!
If I've a problem again I'll hit you up :D

Best regards
Georg
 

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Thank you for your kind words.
Glad I was able to help.
BTW if you have a problem in the future, it would be better to "look me up" rather than "hit me up" :ROFLMAO: :LOL::eek:
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Everybody knows that germans are rough :D
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
Okay, I got a new Question :D

Code:
Private Sub Kontrollkästchen21_AfterUpdate()
Me!sf_qry_all.Form.Filter = "Nächster Termin = '" <= Date +30 ""
    Me!sf_qry_all.Form.FilterOn = True
    Me.Kombinationsfeld6 = Null
End Sub
I want to filter my Nächster Termin(Next Date).
I only want the Dates that are todays date +30 days.

Never ending Story
I'm really sorry :D
 

bob fitz

AWF VIP
Local time
Today, 18:37
Joined
May 23, 2011
Messages
3,972
Try:
Code:
Private Sub Kontrollkästchen21_AfterUpdate()
Me!sf_qry_all.Form.Filter = "[Nächster Termin] <= #" & Date +30 & "#"
    Me!sf_qry_all.Form.FilterOn = True
    Me.Kombinationsfeld6 = Null
End Sub
If that doesn't work, tell me which form this is.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:37
Joined
May 7, 2009
Messages
9,925
Code:
Private Sub Kontrollkästchen21_AfterUpdate()
Me!sf_qry_all.Form.Filter = "[Nächster Termin] >= #" & _
    Format(Date, "mm\/dd\/yyyy") & "# And [Nächster Termin] <=#" & _
    Format(Date + 30, "mm\/dd\/yyyy") & "#"
    Me!sf_qry_all.Form.FilterOn = True
    Me.Kombinationsfeld6 = Null
End Sub
 

georg7

Registered User.
Local time
Today, 10:37
Joined
Sep 3, 2019
Messages
71
And Maybe one of you guys know why qry_all.NächsterTermin is #Name? after I use a filter
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:37
Joined
May 7, 2009
Messages
9,925
i think there are more than 1 column on your "select query" (recordsource).
advise to use query, qry_all, and reset again all the textbox controlsource.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom