Creating a filter on a form (1 Viewer)

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
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!
 

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
Thanks Ridders, but how can I create a query that doesn't make the form read only when I am using two different tables?
 

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
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.
 

Attachments

  • Screenshot 2017-04-10 23.24.39.png
    Screenshot 2017-04-10 23.24.39.png
    86.7 KB · Views: 91
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
Thanks again. I'll give that a try tomorrow. It's late here and I'm off to bed.
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
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));
 

Attachments

  • qry9thRankCompletion.jpg
    qry9thRankCompletion.jpg
    95.4 KB · Views: 76

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
Ok ca you upload a stripped down version of your database for me to look at
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
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.
 

Attachments

  • StudentCOPY.accdb
    892 KB · Views: 60
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
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.
 

isladogs

MVP / VIP
Local time
Today, 01:27
Joined
Jan 14, 2017
Messages
18,186
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
 

jonobugs

Registered User.
Local time
Yesterday, 18:27
Joined
Apr 15, 2013
Messages
70
Thanks for the information. I guess I need to put a bit more thought when I'm making a table. I suppose not all tables need to have an automatic generated PK!

Thanks again for helping me. Your advice was greatly appreciated.

Jonathan
 

Users who are viewing this thread

Top Bottom