Access - SQL Server: cannot generate SSPI context
Why not have both? 

That login screen you're seeing is SQL server rejecting the login parameters. 


Create a OU and label it something like SQLAccess. Add all the appropriate users to the OU.
From there goto SSMS, add the OU to the appropriate logins.
Set your permissions
Create a DSN(Or use current). When it says use Network ID for authentication, make sure it's checked/bubbled. It uses the current user's credentials. 


With the above steps done, when your enduser opens the access file, SQL will shake behind the scenes and thus allow your custom login screen to pop... Only showing 1 login screen. Also should a user ever leave the company, just remove them from the OU and you're golden(or deactivate the account)

In theory you could develop a DSN for every specific users and specify the login with a SQL login but that is tedious, unless of course you do a universal login. I don't recommend it for obvious reasons.