Filter subform based on user

[gori1084]

Ok, I think the code you gave me works just fine but for some reason I am having two issues:

I can't access the query builder in the subform, it's only available in the main form and the main form has no region field in the table it's pulling the info from. The only way to add the UserRegion (an unbound field) is to go and type it in manually in the query builder of the main form and when I do it shows up with [expr] before it. I then added the criteria you gave me in the criteria field but it's not working at all.

The bottom line is that the subform doesn't seem to have the SQL functionality like the main form does so I'm kinda stuck and on the verge of beginning an head smashing game with the wall )

[rpeare]

make a copy of your database, take out any private info and put enough data into it so I have something to work with, then zip it up and upload it to this site. I don't want to have to create a database from scratch.

[gori1084]

Dummy DB.zip

Hope this helps, you will see an error as you log in but that's to be expected since the macro breaks there. I moved the important pieces above so it should let you in no problem.

Please select 556494 from the username and use cesare as password. Thank you so much, I feel like i lost the war but i'm also in bits at this point.

B

[gori1084]

Hi, I've tried again just like you said but it's refusing it to do it. I really don't get it

[rpeare]

You haven't done anything I suggested other than inserted one line of code and expected it to work :P

On top of that you didn't put the code where I said, you put it after you closed your login form when the code relies on that form still being open.

I should note you are also using a BOUND form as your login screen which is..... well... think of it this way, when you type in a password one of two things is going to happen you will if the password is not exact it will change the password without notifying the user, if they subsequently type in the old 'correct' password they will never be able to get back in because the nature of a bound form is that it is directly tied to the data source.

I have modified your database I gave up trying to modifying your existing forms and created two new forms SponsorsNew and CounterpartiesNew so you can see how to work this a bit more efficiently.
I also modified the login form to function correctly using an UNBOUND form.

Dummy DB.zip

[gori1084]

OMG thank you so much. I had found a way around this somehow but it was nowhere near as elegant and functional as your solution. I don't know how to thank you.
I might have mentioned it but I'm a hedge fund analyst and not a programmer so I have my difficulties coding because I'm almost fully illiterate on the topic. I admire your knowledge and would like to start building some of my own. If you don't mind, can you recommend any resources for me to learn Access VBA and basic SQL?

[rpeare]

I honestly haven't read any source material other than to try to find ideas to solve a problem. That was back when the web isn't the resource it is today and you can pretty much find anything you're after by web crawling. Just don't get locked into thinking you have to find a very specific, narrow, solution for your problem. Think about the items you read in terms of what you can do with them as a tool. There are a lot of questions I see on this forum where the person has an idea of how they want something work and are thinking of it rigidly in terms of what a spreadsheet can do, or what a word processor can do or what a calculator can do, thinking about access in those terms is not helpful. Think about the concept first then think about how you might apply it to your situation.