Not wanting filter used to display

[Megacitizen]

I'm wanting to run a query where all team members of any one particular team are identified, with names & nationalities being displayed in a pop-up form. Obviously I will need to use the field "SchoolID" as the filter to identify those people to be displayed, but I don't want that field itself to be displayed in the form.

When I go into query design, I can select all the fields I want to use, but when I de-select the "SchoolID" checkbox in the "Show" criteria, the field itself disappears from the query. Is this normal.

I'm using an A2000 program upgraded to A2007

 

[boblarson]

That check box means Show In Query. If you don't want it on the form, delete the control bound to that field from the form or set the control's Visible property to No.

 

[Megacitizen]

That check box means Show In Query. If you don't want it on the form, delete the control bound to that field from the form or set the control's Visible property to No.

Can't find the Visible property in the Property sheet - probably not looking in the right place, knowing me

Have tried running the query but when I click on the Team button, I get the legend "Microsoft Access cannot find the object 'Team Members'", then the query opens the entire Team members list, not just the list for the team concerned.

This is my code, there probably quite a few lines missing that will help

Private Sub Team_Members_Click()
On Error GoTo Err_Team_Members_Click
Dim stDocName As String
stDocName = "Team Members"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Team_Members_Click:
Exit Sub
Err_Team_Members_Click:
MsgBox Err.Description
Resume Exit_Team_Members_Click

End Sub

Any help much appreciated

 

[Galaxiom]

The field should not disappear from the query design when you uncheck the Show box provided you have some criteria included. Otherwise it disappears when you save the query because the field would be irrelevant. Is this the issue?

Maybe the query is corrupted. Try rebuilding the query from a fresh start. (Copy the text of the sql view to a new query to save work.)

Visible is in the Format tab of the control's properties.

 

[Megacitizen]

The field should not disappear from the query design when you uncheck the Show box provided you have some criteria included. Otherwise it disappears when you save the query because the field would be irrelevant. Is this the issue?

Maybe the query is corrupted. Try rebuilding the query from a fresh start. (Copy the text of the sql view to a new query to save work.)

Visible is in the Format tab of the control's properties.

That seems to be the issue here.

I've also tried rebuilding the query just incase it was corrupted, but no change.

I would upload a copy of my db onto here but I'm having a bit of difficulty trying to compress it to size. My system says it can't compress as the zip file is already in use, but the file in the zip folder is empty. It's all left me somewhat

 

[Galaxiom]

So the field is disappearing when you save the query even though you have a criteria set for that field? What criteria have you entered?

 

[Megacitizen]

From my Team Member table, I have FirstName LastName and Citizenship, and from my School or Ministry Table, I have SchoolID. It is this last criteria that filters my team members and the one I don't want being displayed on the popup form.

 

[Megacitizen]

Managed to get a zipped version - it wouldn't zip when in browsing mode - so I've attached a copy here.

I do now have a bit of a timeframe to get the project finished as I leave in a couple of weeks time to fly home to the UK. I would like something to present to my Ministry before I go.

Any help much appreciated.

And thanks to all who have helped in the past - drinks all round at the virtual bar, if it's still operating

View attachment Megacities Stats.zip

 

[mahenkj2]

As you say you may be running out of time, I try if it can be of help to you.

When I go into query design, I can select all the fields I want to use, but when I de-select the "SchoolID" checkbox in the "Show" criteria, the field itself disappears from the query. Is this normal.

I think it is normal. In some cases you can not show the field chosen in the query but in that case you should once more select the field in the query if you want to show that field in the query but your case is opposite.

As bob advised, you still can select this particular field in the query and make the field invisible on the form.

I have tried to do it in your dB and attached. Basically I made a query from your two tables and then made a form based on this query. The studentID field I made invisible as you can see in design view.

You can set the criteria which you wish to set in the studentID field in the query.

I do not know if it is as simple as I understand.

 

[Megacitizen]

Seems to work ok when I open the query manually, although it only currently shows one record. However I now need to link it to the Team button so that it opens when that is clicked.

Would I be right in thinking I have to create a new button and link it to the query in question, or can I link the query and the existing button?

btw, as an aside, I managed to set the button's visibility to No the other day, but realised that that wasn't what I wanted. I've since changed it back to Yes and saved. However, every time I close the form and come back to it later, the visibility has automatically reset to No and I have to go back into design to reset to Yes.

 

[mahenkj2]

I set StudentID as 1 in criteria of query, that is why it shows only one record. You should put your criteria.

 

[Megacitizen]

I set StudentID as 1 in criteria of query, that is why it shows only one record. You should put your criteria.

Most probably my fault :banghead: I should have reiterated that I require the SchoolID to be the criteria. That would filter all students into their respective teams so that only those members of the team referred to in the main form would be displayed in the popup. I don't need the school info showing in the popup if it's already on the main form, hence my question in the OP

 

[mahenkj2]

In the attached, in the form Team Member, if you click in StudentID (ContactID as label), a pop up form shall open which will display filtered information.

Is this what you want.

regards,

 

[Megacitizen]

In the attached, in the form Team Member, if you click in StudentID (ContactID as label), a pop up form shall open which will display filtered information.

Is this what you want.

regards,

Sorry for the delay in getting back, a weekend got in the way.

Thanks for your efforts so far but this isn't what I'm after. What I require is for the form to pop up when I click on the Team button which will appear just below the Nations field on the mainform once I've trained our system to save the visible property of said button in Formview as YES. It keeps defaulting to NO :banghead:even after saving.

I also want the pop up to display ALL members of the team featured on the main form only, not each individual member 1 by 1 or every individual in the entire list. EG, if Team A has 10 members, Team B has 5 and Team C has 12, and I'm on Team A's page of the mainform, when I click the Team Button, the pop up will appear displaying the 10 members of Team A and no-one else. When I move to Team B's page and do the same, the 5 members of Team B will appear and so on.

 

[Megacitizen]

Latest update :-

I have managed to get the Team Member Query to open and display all the individuals by name and nationality on the db when the Team Members button is clicked. (I'm also displaying the School ID in the query, although this is the column I want hidden from view on the pop up). However, I only want it (the query) to show the members of the team displayed on the Mainform, not EVERYBODY.

What code do I require to filter out those individuals not part of the team in question?

 

[mahenkj2]

The dB which you posted earlier did not show up any query and the work all done was based on guesswork, so it may be worthwhile if you can tell where is the query which you are working upon so that one can reproduce the problem.

PS:Also forgot to mention that where is the Team button, is this a proposed button or already existing but not working.

 

[Megacitizen]

The dB which you posted earlier did not show up any query and the work all done was based on guesswork, so it may be worthwhile if you can tell where is the query which you are working upon so that one can reproduce the problem.

PS:Also forgot to mention that where is the Team button, is this a proposed button or already existing but not working.

I have managed to sort the Team Members button out. It wasn't showing in Form View because I had accidently hidden it and then couldn't stabilise the visibility when I tried to reverse the selection. I set up a new button and designed a macro to run the query from it. It is this query that I now need refining.

I need the query to go to the Team Members table and select all the team members from the team that is diplayed on the Main Form. At the moment it's displaying ALL individuals. From what I've gleaned of t'interweb, I can run queries where I name each team or school in the query, but if I end up with 100+ teams/schools, that's a lot of queries to set up.

I'm sure there is a line of code somewhere that will link my query to the relevant info on the Main Form.

My problem could be that the Main Form is displaying the Team/School Name whilst the query is selecting the SchoolID, but surely that should be correct.

 

[mahenkj2]

Happy to hear that you are progressing.

 

[Megacitizen]

Still struggling to get my query to not display people NOT in the school or team I'm reviewing.


This is the code I'm trying to use:-

SELECT TeamMember.FirstName, TeamMember.LastName, TeamMember.[Nation of Citizenship], TeamMember.SchoolID
FROM TeamMember;
GROUP BY SchoolID;

When I try to run the above, I get the legend "the LEVEL clause includes a reserved word or an argument that is misspelt or missing, or the punctuation is incorrect".

Where am I going wrong?

 

[mahenkj2]

You are using SchoolID as a PK in one table and you want to filter that in other table where it is FK. In that case, use this criteria in the query at SchoolID field.

[forms]![statistics form]![schoolID]

At Statistics form, I inserted a button which runs this query.

revert if ok.