Forum Widgets
Latest Discussions
Return 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.
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 program
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
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 processorMS Access Email Report
Hi I have a Form that I can email as a report. There is an email address on the report that is automatically used as the 'sent to' address within Outlook. Is it possible to lookup a cc email address that is not on the form and add this to outlook also. I am using linked Sharepoint tables for some of the dropdown inputs on the form, and all data is saved to Sharepoint. I am using the embedded macro function as below. Thanks
Report Generation
Dear Experts , I am Kind new to access I Have been working on this project for few weeks now and I am stuck . as you will see from the attached file , I have a data entry form who enters data into "Data" table . the table has 22 columns , but for report generation I am using only few columns as filtering criteria ( Visit Date, Gender ,Age Group, Group ,Visit Type and Diagnosis). when you look at the report form you will notice its a matrix table composed of diagnosis columns and 12 other columns comprising the criteria for filtering, and also 2 date textboxes for start and end date and a command button to trigger the counting process. I want you help in : 1- report generation by counting the number of each diagnosis against each criteria and display the result in the specified textbox ( for example , the No of malaria cases who are " GPOC", :Male" ,"New Visit",">=5" . and finally calculate the total at the end (See report).
