VBA code to check if a user is in a table
A shout to VBA Experts, I am looking for some guidance. I am a novice Access/VBA user I am self taught through forums and U-Tube. I have made a defect record program in Access, I came across a piece of VBA code as shown below to enable or disable buttons on a Log In Form if a user’s name is recorded in a table called DMUsers and the check box for active user is ticked. This allows the user access to the main menu. This works very well, but the problem I have is that if someone who’s user name is no in DMUsers tries to open the access program. The VBA Debugger screen appears when Access tries to load the Log In Form as shown below. I have tied various way to get around this without success. I know I need VBA to check if the StrUserName is listed in DMusers first, if then a message box if not . I would be most grateful for any guidance or help. This Access program sits on a drive on a server, _________________________ Private Sub Form_Load() Dim StrUserName As String 'The Eviron method can retrieve a lot of useful information about the computer with little coding required StrUserName = Environ("USERNAME") ' This retrieves the name of the current logged on user ' You can use this code to see if the user’s computer is on a domain or a workgroup Dim domain As String domain = Environ("USERDOMAIN") ' DLookup will lookup a table and return a value from the specified field ' It used as follows: DLookUp( Name of field to return, Name of table to search, Criteria) ' When using it, you should ensure that the result will return 0 or 1 record only. ' In this example, I have creaed a table called Settings and it has a single field called Domain ' In the table, I have set the name of the Domain that I am expecting this to be running on ' I am also converting value to lowercase (LCase) so it is case insensitive 'Assuming that the user is authenticated by virtue that they are on the domain and their name is in the database 'Then you can use DLookUp to set the status of buttons to restrict user functionality without needing a single password Command27.Enabled = DLookup("ActiveUser", "DMUsers", "[UserName] Like '" & StrUserName & "'") End Sub A Snap of the DMUser table below Snap of the VBA debug which appears when a user whose username is not entered in the DM Users table tries to open the programReturn Value where ID<ID (Nested Query)
Hello Experts, I need to return the [RATE] in tblFXRollsChild WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK) The below is what I have but I know the WHERE clause is wrong (returns 0 records). I dont know if I need to be using "TOP 1". do you see where I am wrong? SELECT T1.IDParentfk, T1.IDRollsPK, T1.Rate AS LastOfRate FROM tblFXRollsChild AS T1 WHERE (((T1.Rate)=(SELECT [RATE] FROM tblFXRollsChild AS T WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK))) ORDER BY T1.IDParentfk, T1.IDRollsPK; In the below tblFXRollsChild, I want to return the RATE for the record above but where there is not a record then return the RATE of the current record.
Autofill data
Hey, is there a way to autofill some values in access based on a combination of other fields? Like essentially with VLOOKUP in excel. Specifically, I'm trying to autofill Latitude and Longitude fields based on a combination of Bay (location) and a Waypoint number for where we set nets in a lake. I have a deployment table I'm trying to fill everything in, and the only reason I need Lat & Long in that table vs just having it in the Waypoint table, is sometimes we set nets not along a waypoint and I want to be able to enter those coordinates. Attached are pictures of part of the Deployment form and the Waypoint table
Dynamic group based on custom security attribute
Can anyone answer this question. Can or should i be able to create a Dynamic group filtering on a customer security attribute. Yes I know you can filter based on extenstionattribute1-15 however i have noted that accounts create in Entra don't appear to have the option to view extension attributes plus these come from an on prem created account. So the questions are: Can I create a dynamic group using a custom security attribute and if so how because the custom attributes don't show up in the Property options when creating the dynamic group query How can I add to the extension attributes for non on prem sync accounts (accounts created in Entra)
M365 email license & access management
I am using powershell to read the lastlogontime so that powershell can revoke the license if lastlogontime > 60days, now I have several questions: is the lastlogontime the right parameter to judge this email is alive or not? why unlicensed email accounts can send/receive emails while lastlogontime >60days and license revoked? are there any best practices from you guys to judge active/inactive of email, remove license & block access?
Can't use a SPN in a PowerBi dashboard to access SharePoint lists
Hoping you can help with an ongoing issue I have. I have a PowerBi dashboard I built using regular account to fetch some SharePoint lists and uploaded it to PowerBi for others to view Now in PowerBi portal I want to change the credential from my account to an SPN. I've read what feels like a thousand articles describing the process to create the SPN 99% all the same. Yet when I go into Powerbi portal, edit the semantic model for the dashboard, click edit credentials, select Service Principal put in the tenant ID the Service principal ID (yes using the app id, in fact I tried everything) the service principal key (the secret) and choose any privacy level it fails 100% of the time. Error is: Failed to update data source credentials: The credentials provided for the SharePoint source are invalid. Same error regardless of what privacy level I choose. I'm sure the secret is correct also. Just for fun I tried the Secret ID and the Object ID in place of the Application ID for the Service principal ID field. All failed same error. I'm sure the secret is correct also. The SPN has Graph sites.read.all, Graph user.read and SharePoint Sites.Read.All api permissions configured. All are consented. Everything seems right but gives me the error failed to retrieve oauth token 100% of the time. Am i missing something else? More API permissions maybe? Do i still need ot actually add the SPN to the Sharepoint site itself even though I has API permissions SharePoint Sites.Read.All? I've done days of research and all I find is lots of people with same or similar issue but not resolution. Is this a bug? Help me I'm desperate to get this fixed or I'm going to have to allow people to bypass MFA across my organization which I cant have.Is PIM any good?
I'm planning a PIM implementation and am trying to understand a few things about PIM and certain recommendations. I have a OnPrem\Entra hybrid environment. I have many servers hosted both on prem in the on prem AD and in Azure. In traditional on prem environments this segregation has typically been achieved using separate admin accounts. This give you some segregation and protection in case an account was compromised. I'll accept its not bullet proof but a lot of things would have to work in the right order for a bad actor to compromise a separate admin account I've read and heard MS guys (probably driving license sales) saying that's not the right way anymore and JIT is the right way. Which of course requires license. I'm looking for opinions or observations from experience for the following: Why is doing one account (possibly the regular user account in a Hybrid environment) with PIM better that having a regular and admin accounts? Why not have a separate admin account with PIM implemented on the admin account in Entra? I can't see how this would be less secure that just one account with PIM. One argument I heard was you can require MFA to activate the access. Well right now i just use CA policies to require MFA for any use of a role I have nominated (portal\cli\PowerShell etc). How is Entra JIT with one account better than still having a admin account have a requiring MFA for them to log onto any of the the admin portals to use their privileged access? Another concern I have is controlling who is assigned to the roles. Right now I can add them one by one to the role in PIM but our MSP (who does the bulk of the management) wants to add a group to each role assignment and then they add people to the group to inherit the assignment of the role. For many reasons I cant go into there are large numbers of people who are in the group admin role. This basically means any of them could elevate theirs or someone else access into a Entra role if I'm using groups to assign groups to role. What if they start nesting groups into other groups and suddenly Domain Users has been nested and has Global Admin? How do I police this?
Continuous forms display broken in various ways when use vertical scrollbar or Page Up/Down
I've been investigating this in the Northwind Traders Developer Edition 2.4 as it was affecting a business application and I wanted to see whether it was something we had done wrong or an Access bug. It seems to be an Access bug so writing it up here. I'm testing with an unaltered frmOrderList, and then with a copy with minor modifications to control anchoring. The symptoms are quite variable depending on whether I'm working on primary laptop monitor (DELL Precision 1920x1200 with 125% scaling) or a second monitor (DELL P2423 1920x1200 with 100% scaling). On the second monitor almost any scrollbar action results in a broken form display, where areas remain blank and partly reappear on hover/click, for example: Everything displays fine if the mouse wheel is used for scrolling; the problem arises only with the scrollbar and/or Page Up/Down. The wider the window/form, the worse this becomes, and the effects seems to be particularly bad if controls are anchored upper right, but in the above example all the controls in the list are anchored upper left. Sometimes the left side of the list updates and the right side does not, meaning that incorrect data is displayed to the user - this is particularly dangerous; here's an example using the same form, after anchoring some controls top right: The record for Robert Zare should say "No stock" but the right-hand columns have remained stuck showing data from the first rows in the form. Please could this be addressed asap? We have a lot of continuous sub-forms and this is making the applications very problematic to use. This issue doesn't appear to affect datasheet view - it's only Continuous Forms. Also, it's been like this for some time - I'd guess at least a year. Environment as follows: Microsoft® Access® for Microsoft 365 MSO (Version 2410) 64-bit (I had to remove build number to publish this post!) Edition Windows 11 Enterprise Version 23H2 OS build 22631.4602 Experience Windows Feature Experience Pack 1000.22700.1055.0 Processor 11th Gen Intel(R) Core(TM) i7-11850H @ 2.50GHz 2.50 GHz Installed RAM 32.0 GB (31.7 GB usable) System type 64-bit operating system, x64-based processor
