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.SolvedTony2021Jan 21, 2025Steel Contributor38Views1like4Comments#Error in unbound text box
Hello Experts, I am trying to display nothing instead of #Error being displayed in an unbound text box. Its only showing on the new record row. The below is not working. It still displays #Error on the new record line. =IIf(IsError(DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID])),"",Nz(DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID]),0)) Do you see where I am wrong? thank you.SolvedTony2021Jan 20, 2025Steel Contributor46Views2likes5CommentsVBA 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 programSolvedGerodoJan 19, 2025Copper Contributor27Views0likes2CommentsAutofill 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 tableSolvedfishylizardJan 14, 2025Copper Contributor38Views0likes3CommentsHow to Uninstall Microsoft Access Database Engine 2016 by COMMAND LINE
Hi Team, I would like to uninstall Microsoft Access Database Engine 2016 by using command line. However, It always show a bellow dialog. Could you please show me how to uninstall it by using command line? Thank you, LyLyJan 14, 2025Copper Contributor125Views0likes8CommentsBug in Microsoft Access's Number Filters for Less Than & Greater Than options
Dear Microsoft Office 365 Development Team, I am submitting this bug report regarding an issue I have encountered with the Number Filters in Microsoft Access within Office 365. Specifically, the "Less Than..." and "Greater Than..." filters do not behave according to basic mathematical principles. Here are the details of the issue: When applying the "Less Than..." filter and entering a value (e.g., 250), the filter includes records equal to 250 in the result. The correct behavior should be to display records with values strictly less than 250, i.e., from 1 to 249. Similarly, when applying the "Greater Than..." filter and entering a value (e.g., 250), the filter also includes records equal to 250. The expected behavior should be to show records with values strictly greater than 250, i.e., from 251 to 500. It appears that the filter logic is not following the expected mathematical rules for "less than" and "greater than" comparisons. Could you kindly investigate this issue and confirm once a fix is available? I would also appreciate it if you could provide details about when the new, fixed version of Microsoft Access will be released. Thank you for your attention to this matter. Kind regards,Solved68Views0likes2CommentsContinuous 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 processorcrispinflowerJan 03, 2025Copper Contributor68Views0likes6CommentsMS 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. ThanksSolvedHenryDJan 02, 2025Copper Contributor126Views0likes9CommentsAccess freezes when I copy a record
Windows 11 Office 365 Access Suddenly, when I copied a record in the database Access froze as if it didn't have enough memory. I tried compact and repair, repairing Office, uninstalling and reinstalling Office 365, exporting the database into CSV and importing back as text into a new table in a clean database, after that didn't work, I reduced the number of entries by 80% and imported as text into a fresh table. It still froze as soon as I control-C for 45 to 50 seconds each time. A completely different Access database is experiencing a shorter delay after each control-C Has anyone encountered this error or know a reputable contractor who specializes in such file specific issues?SolvedArt_MeniusDec 19, 2024Brass Contributor45KViews5likes112CommentsReport 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).AkwangdiingDec 18, 2024Copper Contributor100Views0likes8Comments
Resources
Tags
- access1,611 Topics
- office 365355 Topics
- 2016195 Topics
- developer183 Topics
- Access Web Database100 Topics
- Access Web App57 Topics
- 201351 Topics
- sharepoint49 Topics
- 201042 Topics
- admin40 Topics