Filter results in query based on a name from a form

[Summit]

Hello,

So I first started using Access in March of this year so I am definitely a 'noob' when it comes to this stuff.

What I want is from a form (which is a summary form for each user) I want them to be able to access a query but only see their own results. I already have it so when you press a button the form, the query opens up, I just cannot figure out how to make it so only the users results (their names are on the form) appears in the query.

I would have posted screenshots but I cannot as I do not have 10 posts.

Thank you for your help

EDIT: Just saw the post saying to zip the file for pictures.

 

[Beetle]

In the criteria row of the FullName column in your query, place a reference to the Control (text box) on your form, i.e. -

[Forms]![YourForm]![YourTextBox]

you'll need to use the actual names of your form and text box of course.

 

[Summit]

That worked thank you. However, how do I now go into the query to view everyone's data myself without the filter applying?

EDIT: Now when I search another users name on the form and press the button that has the filter applied, nothing shows up in the query. It works for the default name that appears, but now when i search another name on the form.

 

[Beetle]

That worked thank you. However, how do I now go into the query to view everyone's data myself without the filter applying?

In that case you probably want to just apply a Filter in your form, rather than using criteria directly in the query.

Now when I search another users name on the form and press the button that has the filter applied, nothing shows up in the query. It works for the default name that appears, but now when i search another name on the form.

Sounds like your button is already coded to apply a filter. Can you post the code for the command button?

 

[Summit]

In that case you probably want to just apply a Filter in your form, rather than using criteria directly in the query.

How would I go about doing that for what I want? Sorry, I don't know how to do coding yet. As I mentioned I just started using Access in March.

Sounds like your button is already coded to apply a filter. Can you post the code for the command button?

I used the Macro builder, so I'm not sure how to view the code. However all I did was put in minimize window and open query (View datasheet, data mode read only).

Thanks again for your help

 

[Beetle]

If you can you zip a copy of your db with just a few lines of dummy data and post it here I an look at it.

 

[Summit]

Sure thing

 

[Beetle]

I took a look at your db and need to clarify something. I got the impression from your previous posts that you wanted to be able to enter a name in the Full Name text box on your Individual Summary form and then "search" for records that match that name in the query. The problem here is that you have that text box (Full Name) bound to the Full Name field in the underlying table, so any value that you enter there is going to overwrite the existing name for that record. When you are using a control (textbox, etc.) on a form for the purposes of searching for records, that control needs to be unbound (i.e. it's Control Source is not bound to a table field).

If you're not trying to "search" for a name, and just want to select a record on your form and then open the query based on that record, then it is OK for the text box to be bound to the field because you would not be entering any search criteria in that case.

In either case, the way you are doing this (minimizing the form and viewing the query directly) you're probably better off having two copies of this query with slightly different names. One would have the criteria and the other one wouldn't. The button on your main menu would open the full query and the button on your Individual Summary form would open the filtered query. If you want to then navigate to a different record on your form and view different results, you need to close the query first, then select a new record (or enter a new search name) on your form, then click the button again.

BTW - I would also suggest that you don't store the Full Name (First and Last) in the same field. These are separate attributes and should be stored in separate fields. You can always concatenate them back together as Full Name for display purposes if you need to. This would be done in a calculated field in a query or a calculated control on a form. In addition to this, you're using "lookup" fields in your tables. This can be problematic as well. Combo boxes ("lookups") are appropriate in forms but not tables. More info here.

 

[Summit]

Thank you for your help. I'm away for the weekend so I'll take a look at this once I return. I appreciate your time!

 

[Summit]

What I want to do in the Individual Summary form is what ever name is in the textbox I want to be able to search that name in the query and only see that persons results. I also want to be able to open the query and see all results (from the switchboard or just from clicking on the query). You mentioned creating the a second query and adding the criteria on that. I can see how that would work and seems like the easiest solution for my knowledge on how to use this program.

I should probably mention this because it's probably very important, but the overall goal of this database is to put it up on a website and have it so people can only view their own data. In addition there will be some people where we want them to be able to view and analyze all data. The goal might be a little ambitious for someone who has only started using this program a month ago, but I'm going to give it a shot.

When I first created the database I had last name and first name separated. It was working fine until I tried to sort data in the queries. I would sort by last name, however the first name would not stay matched up to their last name. That is why I combined last and first name into one field.

 

[Beetle]

I should probably mention this because it's probably very important, but the overall goal of this database is to put it up on a website and have it so people can only view their own data.

If you mean you plan on publishing this application to the web through Sharepoint, or a third party Sharepoint host, then yes, that is a very important point because your application in it's current state is not web compatible. All of the objects that you've created so far (tables, queries, forms, macros) are client side objects. None of them will publish to the web. You won't even be able to create web compatible objects in this database because you did not start with a web database to begin with.

To use the new web compatible options in A2010 you first need to start with a new blank web database (or a web template). All of the web objects are different, and don't have the same design options as client side objects. You can have client objects in a web database (except for tables, you can only have web tables in a web database), but those objects will not publish to the web. They will only be usable when the database is run through the Access client side front end.

 

[Summit]

I'm not the one looking into getting it on our website, but the site works with SQL. I've tried researching SQL but it just all goes way over my head. The person I am working with has used SQL before on her work's site.

As for the web compatibility, is it possible to just export everything into a web database? Or would I have to start from scratch (which I am willing to do). Past versions of this database I have exported into a web database and the compatibility check said everything was compatible (I know the version I gave you has several web compatibility errors). Besides, it may be best to start from scratch anyways because this database has gone through a lot of trial and error.

 

[Summit]

Hello,

Made it into a web database with a bunch of changes. The new data entry form is not complete as it doesn't actually enter any data yet.

 

[Beetle]

I think I should try to clarify some things for you. As I said in my previous post, an A2010 web database is allowed to have both web objects and client objects (by objects I mean queries, forms, reports, etc.). Client objects will only run in within Access itself, they will not publish to the web or run in a browser. Here is a screenshot from one of my apps;

Because of the fact that you can have both types of objects, the "Compatibility Checker" can be a bit misleading. Even if you have nothing but client queries and forms (such as in your case) it may still tell you that your database is compatible with the web. That's because client objects are not "incompatible" with a web database, but that doesn't mean that those objects will actually publish to the web and run in a browser.

In addition to that I think you may have a misunderstanding about how exactly an A2010 web database gets published to the web. You said that one of your coworkers has worked with "SQL". I'm guessing that you mean SQL Server. If your plan is to have your Access database use a SQL Server back end, and meanwhile your coworker is creating a web application (using different software like Visual Studio, ASP.Net, etc.) that is going to connect to that same back end, that would be one thing (although in that case you wouldn't necessarily need an Access web database).

However, if your intent is to publish your Access application to the web and have (some or all) of your forms available via a web browser, there are very specific requirements for that, and those requirements might mean that this whole project could be a non-starter. When you publish an A2010 database to the web, it gets published through Sharepoint Server (not SQL Server) and the tables become Sharepoint Lists. Furthermore, it can't just be any version of Sharepoint. It must be Sharepoint 2010 Enterprise edition with Access Services. If your organization does not use Sharepoint Server (or doesn't have the newest version) there are some third party Sharepoint hosting services out there that can publish Access web databases, but this could be completely outside the scope of what your (meaning you and/or the other people involved in this project) expectations were going into this. You might want to have a conversation amongst yourselves about some of these issues and what the expectations are for this application.

 

[Summit]

Ahh ok that makes more sense now. I've also been looking into Sharepoint 2010, and I do have it on my computer. I will do some more research, and I will talk to my coworker and discuss what we want to do. If it becomes too much for me to do, perhaps I'll seek out professional help (do you happen to know how much something like this would cost?).

Thank you again for your help, I really do appreciate it.