Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-09-2017, 11:31 PM   #1
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Creating a filter on a form

I created a simple query which used two separate tables and was able to filter the results that I wanted.

However, I want to be able to edit the results in a form, so of course I can't used a query to do this.

I was wondering if it's possible to create a form that filters the form based on a different table.

I would rather do this without having to program as I really don't know all the syntax to program and I'm not really sure I have the time to learn everything.

Thanks for any help!

jonobugs is offline   Reply With Quote
Old 04-09-2017, 11:38 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

You can base a form on a query and providing the query design doesn't make it read only, the form will be editable

Colin
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jonobugs (04-11-2017)
Old 04-10-2017, 02:39 AM   #3
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Thanks Ridders, but how can I create a query that doesn't make the form read only when I am using two different tables?

jonobugs is offline   Reply With Quote
Old 04-10-2017, 03:14 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

It depends on the structure of the tables & the relationship between them
In most cases, there won't be a problem i.e. the result will allow editing in your form

Try creating a suitable query & see if it does allow editing
If so, use as the form record source.

If not, post the query design & I'll advise further

Colin
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jonobugs (04-10-2017)
Old 04-10-2017, 06:32 AM   #5
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Thanks for taking a look at my problem, Ridders. I'm not sure what reasons will cause a query which will allow editing or not. Anyway, I took a snapshot of the query I created which does not allow editing. It does however, show me the results that I want.



It's a bit strange, but I can view the file if I open the image in a new tab. In any case, I uploaded the file and attached it.
Attached Images
File Type: png Screenshot 2017-04-10 23.24.39.png (86.7 KB, 63 views)

Last edited by jonobugs; 04-10-2017 at 10:27 AM. Reason: link didn't work
jonobugs is offline   Reply With Quote
Old 04-10-2017, 10:21 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Hi again

There's clearly something wrong with your screenshot as it didn't work here or in the Dropbox link you sent me.

Try changing the query to SQL view then copy & paste the text displayed using #CODE tags
isladogs is offline   Reply With Quote
Old 04-10-2017, 10:26 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Further to your question about read only queries, there is an excellent explanation on Allen Browne's website: http://allenbrowne.com/ser-61.html

I'd thoroughly recommend that website, as do many other users of this forum, for clear & wide ranging advice. Unfortunately he's now retired but the website is still active.

By the way. there is in theory practically no limit to the number of tables you can include in a query without it becoming read only though the more you include the greater the chances that it won't be editable.

For example, I have several editable queries with 8 linked tables .... though that is fairly unusual!

Colin

isladogs is offline   Reply With Quote
Old 04-10-2017, 10:33 AM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Me again...

Oddly, I can now open the screenshot in this forum.
The problem may be because the StudentID isn't the PK field in either table

Things to try:
1. Change the query properties to Unique Records
2. I assume you have another table with StudentID as the PK field
Add that to the query & link the other 2 tables to that field

Failing that, send me the SQL view as requested earlier

Colin
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jonobugs (04-10-2017)
Old 04-10-2017, 10:38 AM   #9
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Thanks again. I'll give that a try tomorrow. It's late here and I'm off to bed.
jonobugs is offline   Reply With Quote
Old 04-10-2017, 05:42 PM   #10
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Hi Colin,

I read the page that you recommended which was helpful understanding some things, but as far as I know, my read only query has not been caused by the reasons listed.

I changed the query properties to Unique Records = Yes, and I added the Student Table, which properly links the two tables. Unfortunately, I still have the same read only query.

Here is the latest SQL view

SELECT DISTINCTROW tblRanking.RankInfo, tblRanking.DateFinished, tbl9thRankCompletion.Rank9ID, tbl9thRankCompletion.StudentID, tbl9thRankCompletion.DEA, tbl9thRankCompletion.ProConPink, tbl9thRankCompletion.PeerGreeting, tbl9thRankCompletion.NakayoshiSquare, tbl9thRankCompletion.FamilyMembers, tbl9thRankCompletion.PCC2, tbl9thRankCompletion.PCC1, tbl9thRankCompletion.PinkVerb, tbl9thRankCompletion.Vocabulary, tbl9thRankCompletion.Numbers, tbl9thRankCompletion.Colours, tbl9thRankCompletion.ABCs, tbl9thRankCompletion.BasicAnswers, tbl9thRankCompletion.Notes, *
FROM (tblStudent INNER JOIN tbl9thRankCompletion ON tblStudent.StudentID = tbl9thRankCompletion.StudentID) INNER JOIN tblRanking ON tblStudent.StudentID = tblRanking.StudentID
WHERE (((tblRanking.RankInfo)=4) AND ((tblRanking.DateFinished) Is Null));
Attached Images
File Type: jpg qry9thRankCompletion.jpg (95.4 KB, 43 views)
jonobugs is offline   Reply With Quote
Old 04-10-2017, 10:51 PM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Ok ca you upload a stripped down version of your database for me to look at
isladogs is offline   Reply With Quote
Old 04-11-2017, 05:20 AM   #12
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Hi Colin, I suppose I could do that, but it will take a bit because I would need to remove the data. I'm not so sure it would work very well without any data though.

I don't think I found the answer, but I read that I could change the recordset type to "Dynaset (inconsistent updates)".

That seems to work, but I really don't know what that does, or if it's a good idea. I would rather fix the database if I have a bad design, so I don't run into big problems down the road.

Okay, so I uploaded the database, but I had to take out a lot of material to make it small enough. Also, I had to delete all the sensitive data.

I think I ended up destroying a few joins, but the main problem I had still remains, which is the part I'm trying to figure out anyway.

The file you want to run is "qry9thRankCompletion". Hopefully you can figure out any problems. Unfortunately, through my deletions, the one to many relationship from the Student table to the ranking table was removed, so I don't know if that will affect things a lot.
Attached Files
File Type: accdb StudentCOPY.accdb (892.0 KB, 24 views)

Last edited by jonobugs; 04-11-2017 at 06:09 AM. Reason: addition of data
jonobugs is offline   Reply With Quote
Old 04-11-2017, 10:19 AM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Hi

With the data you sent me, there appears to be only one record per student in the table tbl9thRankCompletion.

If that's the case, change the PK field to StudentID (
Then remove the Rank9ID field if not needed for any other reason.

The query qry9thRankCompletion is then editable as is the form
So is the query qry9thRankCompletionCalculation

In fact by doing that change, you don't need the junction table tblStudents for the query so its SQL can be simplified again to:

Code:
SELECT DISTINCTROW tblRanking.RankInfo, tbl9thRankCompletion.Rank9ID, tbl9thRankCompletion.StudentID, tbl9thRankCompletion.DEA, tbl9thRankCompletion.ProConPink, tbl9thRankCompletion.PeerGreeting, tbl9thRankCompletion.NakayoshiSquare, tbl9thRankCompletion.FamilyMembers, tbl9thRankCompletion.PCC2, tbl9thRankCompletion.PCC1, tbl9thRankCompletion.PinkVerb, tbl9thRankCompletion.Vocabulary, tbl9thRankCompletion.Numbers, tbl9thRankCompletion.Colours, tbl9thRankCompletion.ABCs, tbl9thRankCompletion.BasicAnswers, tbl9thRankCompletion.Notes
FROM tbl9thRankCompletion INNER JOIN tblRanking ON tbl9thRankCompletion.StudentID = tblRanking.StudentID
WHERE (((tblRanking.RankInfo)=4) AND ((tblRanking.DateFinished) Is Null));
or better still:

Code:
SELECT DISTINCTROW tblRanking.RankInfo, tbl9thRankCompletion.*
FROM tbl9thRankCompletion INNER JOIN tblRanking ON tbl9thRankCompletion.StudentID = tblRanking.StudentID
WHERE (((tblRanking.RankInfo)=4) AND ((tblRanking.DateFinished) Is Null));
where the .* indicates all fields in tbl9thRankCompletion

However, if you can't change the table PK field as described, its could be a little more tricky to solve
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jonobugs (04-11-2017)
Old 04-11-2017, 05:46 PM   #14
jonobugs
Newly Registered User
 
Join Date: Apr 2013
Location: Japan
Posts: 70
Thanks: 45
Thanked 1 Time in 1 Post
jonobugs is on a distinguished road
Re: Creating a filter on a form

Wow, that worked like a charm Colin!

I guess I need to think a tiny bit more when I make these tables!

For future information, is it possible to explain a bit more why the query wasn't editable? I now know it has something to do with the PK, but I guess I just don't know enough about the structures of DBs to know why that matters. After looking at the other website information, it seems that it has to do with type of relationship that the tables have with each other.

Thanks again for your help! This sort of thing has popped up more than once, so now I know to look carefully at my PK and the other foreign keys.
jonobugs is offline   Reply With Quote
Old 04-12-2017, 01:11 PM   #15
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,028
Thanks: 115
Thanked 3,015 Times in 2,742 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Creating a filter on a form

Sorry forgot to reply to this.
I can't really improve on Allen Browne's list of reasons but in your case I think is was the one to many relationship that caused the problem

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jonobugs (04-13-2017)
Reply

Tags
filter , form , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a textbox in a form to filter a combobox Pooks_35 Modules & VBA 3 09-16-2014 10:02 AM
creating keyboard shortcut for Filter by form talktime Forms 3 02-01-2013 01:41 AM
Creating A Filter Report Form A Query. (Thanks For Looking.) djshrew Reports 0 07-25-2007 06:23 AM
[SOLVED] Creating a report based on form filter ben.r Reports 1 09-05-2005 07:00 AM
Help needed creating a filter form. Chunk Forms 1 02-19-2005 02:49 PM




All times are GMT -8. The time now is 12:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World