access

1604 Topics

MS 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
HenryD
Dec 20, 2024 Place Access
75Views
0likes
6Comments
Aggregate query question
Hola all! New to Access, building my own DB for race results from a series here in the US for a separate project. I have one table that has nothing but race results. Columns that matter: raceCode: indexed, duplicates OK, i.e., 1949-01 start: driver's starting position, used for averages over time finish: driver's finish, also used for averages driverName: First and last name of driver I'm attempting to build a query off this data that I can continue to update as I add new raw data to the table. Total starts is easy, that's a simple Count of driverName. However, trying to pick out Wins, top 5s, top 10s, etc, is driving me insane. If I'm looking for how many times the #1 ends up in finishes, or how many times a driver has a finish of <=5, what's the best way to write that without it throwing a data mismatch error?
Solved
mikedb3434
Dec 12, 2024 Place Access
66Views
0likes
1Comment
How to restore selected ribbon tab after report with a custom ribbon is closed?
We have a database which has two ribbons. A Switchboard ribbon with multiple tabs and a hidden PrintPreview ribbon used through report “Ribbon Name” property. The PrintPreview ribbon has startFromScratch="true". The tabs of Switchboard ribbon have multiple buttons use for opening a corresponding report. The problem is that each time a user opens and closes a report the Switchboard ribbon jumps back to first tab. Example: Switchboard has tab1, tab2. The tab1 has button1 and button2 to open report1 and report2 correspondently. The tab2 has button3 and button4 to open report3 and report4 correspondently All 4 reports have “Ribbon Name” property set to PrintPreview ribbon. User clicks report3 from tab2. The report3 is opens, Switchboard ribbon is hidden and PrintPreview ribbon is visible. User close report3. The Switchboard ribbon became visible and tab1 is an active tab. Now user need to click on tab2 again if he/she want to open report4 or even report3 again. If we extrapolate this situation for 17 tabs on Switchboard ribbon with each tab having ~20 reports we can see user frustration with moving focus back to first tab. Is where a way to remember which tab in Switchboard ribbon was active before opening PrintPreview ribbon and make it active after report is closed? Note: the only possible solution I found was with use of Ribbon Accessibility here www.wordarticles.com/Shorts/RibbonVBA/RibbonVBADemo.php It is a quite complicated and does not works under Access 365 too. Any help would be appreciated! Thank you!
Solved
eddypi123
Dec 10, 2024 Place Access
62Views
0likes
4Comments
Which data entry approach to take?
Hi I have a sub form, within a form, which contains combo boxes and text boxes. I want to use it to enter data, creating new records whilst also ideally potentially being able to edit existing records which have been created in the same sitting. I'm not sure which approach to take. At the moment, I'm using continuous form view, but my code (see below) is playing havoc with the existing records, e.g. CategoryCB\_AfterUpdate causes an update of the CategoryCB combo box to requery the Subcategory combo box (SubcategoryCB) resulting in the existing data being cleared. I'm pretty new to Access and I'd really appreciate guidance on what approach to take please. Private Sub Form\_Load() ' Initially show all subcategories, including CategoryID Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT ORDER BY SubcategoryName" Me.SubcategoryCB.ColumnCount = 3 ' Set the column count to 3 Me.SubcategoryCB.Requery End Sub Private Sub Form\_Current() ' Set the initial state of the CategoryManuallySelected flag CategoryManuallySelected = False If Me.NewRecord Then ' Disable the relevant text boxes on form load Me.MilesTravelledTB.Enabled = False Me.MonthsUsedTB.Enabled = False Me.AmountTB.Enabled = False Me.InvoiceNoTB.Enabled = False Me.DescriptionTB.Enabled = False End If End Sub Private Sub CategoryCB\_AfterUpdate() ' Clear the SubcategoryCB value and filter based on the selected Category Me.SubcategoryCB.Value = Null Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT WHERE CategoryID = " & Me.CategoryCB.Value & " ORDER BY SubcategoryName" Me.SubcategoryCB.Requery ' Clear relevant fields ClearRelevantFields ' Set the flag to indicate manual selection CategoryManuallySelected = True End Sub Private Sub SubcategoryCB\_AfterUpdate() If Not CategoryManuallySelected Then ' Access the CategoryID directly from the combo box Dim CategoryID As Integer CategoryID = Me.SubcategoryCB.Column(2) ' Update the CategoryCB with the corresponding category Me.CategoryCB.Value = CategoryID End If ' Enable relevant fields Me.InvoiceNoTB.Enabled = True Me.DescriptionTB.Enabled = True ' Update column visibility and clear relevant fields ClearRelevantFields UpdateColumnVisibility End Sub ' ClearRelevantFields subroutine definition Private Sub ClearRelevantFields() Me.MilesTravelledTB.Value = "" Me.MonthsUsedTB.Value = "" Me.AmountTB.Value = "" Me.InvoiceNoTB.Value = "" Me.DescriptionTB.Value = "" End Sub Private Sub UpdateColumnVisibility() Select Case Me.SubcategoryCB.Value Case 16 ' Subcategory for Miles Travelled Me.MilesTravelledTB.Enabled = True Me.MonthsUsedTB.Enabled = False Me.AmountTB.Locked = True Me.AmountTB.Value = "" Case 47 ' Subcategory for Months Used Me.MonthsUsedTB.Enabled = True Me.MilesTravelledTB.Enabled = False Me.AmountTB.Locked = True Me.AmountTB.Value = "" Case Else Me.MilesTravelledTB.Enabled = False Me.MonthsUsedTB.Enabled = False Me.AmountTB.Locked = False Me.AmountTB.Enabled = True Me.AmountTB.Value = "" End Select End Sub Private Sub MilesTravelledTB\_AfterUpdate() If IsNull(Me.MilesTravelledTB.Value) Or Me.MilesTravelledTB.Value = "" Then Me.AmountTB.Value = "" Else Dim miles As Integer miles = Me.MilesTravelledTB.Value If miles <= 10000 Then Me.AmountTB.Value = miles \* 0.45 Else Me.AmountTB.Value = (10000 \* 0.45) + ((miles - 10000) \* 0.25) End If End If End Sub Private Sub MonthsUsedTB\_AfterUpdate() If IsNull(Me.MonthsUsedTB.Value) Or Me.MonthsUsedTB.Value = "" Then Me.AmountTB.Value = "" Else Dim months As Integer months = Me.MonthsUsedTB.Value Me.AmountTB.Value = months \* 26 End If End Sub
Solved
RebeccaRead2024
Dec 01, 2024 Place Access
138Views
0likes
6Comments
Dlookup with <=
Hello Experts, I am having a hard time with not understanding why the dlookup is not returning what I think it should. I have the following: CommitBalDlook: Format(DLookUp("Balance","qryFacilityBal","ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] & " And nz([DateAmend],0) <=" & Format$(Nz([tblDraws].[FundingDate],0),"\#mm\/dd\/yyyy\#")),"Standard") so if [qryFacilityBal].[DateAmend] is <= to [tblDraws].[FundingDate] it should return the [qryFacilityBal].[Balance] if the dates are equal but it looks like it is returning the [qryFacility].[Balance] that is less than in the case where they are equal (please refer to the pic below). I also tried removing Nz on DateAmend but that did not change anything but there are no nulls anyways. Possibly the format on tblDraws.FundingDate has something to do with it (the "\#mm\/dd\/yyyy\#" but I dont think its the problem). here is a pic: DateAmend and FundingDate are both Short Date format in the above, you can clearly see that the pic on the right (where the dlookup is) is returning 2.34B where the way I understand it, it should return 2.23B since the dates are equal (10/24/23) on [DateAmend] and [FundingDate]. What is odd is that if I change the criteria from <= to just = then it does return 2.23B but then all the other records are null where there is not a match so I cant use =. Do you see anything wrong with my Dlookup? I am sure I am missing something. I have spent hours fiddling around on this but not getting anywhere. grateful for the help.
Solved
Tony2021
Nov 26, 2024 Place Access
151Views
0likes
11Comments
Transitioning From Access To Web Sites
I have a question for the individuals out there that are moving to a web interface from Access What it the best app/program to use to make the move from using Access to using web pages? I've been tasked with converting the Access app that I have been working with for years to make it a web "site", which would be converting the entire application - forms, reports, etc. so that it can be used via web browser instead of MS Access. I am in no means a website developer, but I'm always open to learning new things. Mind you, one of the databases has in excess of 12K lines of VBA. I want to be able to use some of the same techniques as what I've done with VBA and to be able to create a good user experience on the front end plus create some additional enhancements. Al of my searches have not yielded anything helpful. Thanks in advance. Jeff
Jeff Garrison
Nov 25, 2024 Place Access
2.1KViews
0likes
25Comments
Strange chraracters in the powershell command PS_GetOutputFile("netsh wlan show interfaces")
I´m creating a sub that lay out in a textbox of a form with strange chrs. my sub is as follow: Private Sub Form_Load() 'Redondear las esquinas del formulario Call UISetRoundRect(Me, 40, False) 'This sets an exact position using MoveSize if form´s parent is loaded, otherwiseccenter de form on screen If IsLoaded("frmDashBoard") Then DoCmd.MoveSize 15050, 2400 Else Call gfncCenterForm(Me) With Me 'Encabezado de la información de la red Wi-Fi If Not IsNull(Me.OpenArgs) Then Me!lblInfo.Caption = "INFORMACIÓN " & Me.OpenArgs 'Cargar la información de la red Wi-Fi !txtInfo.Value = PS_GetOutputFile("netsh wlan show interfaces") '!lblTitle.Caption = PS_GetOutputClipBoard("(Get-NetConnectionProfile).Name") End With End Sub Public Function PS_GetOutputFile(ByVal sPSCmd As String, _ Optional sTxtFile As String = vbNullString, _ Optional bDelTxtFile As Boolean = True) As String 'If no Text file was specified create one in the Temp folder If sTxtFile = vbNullString Then sTxtFile = Environ$("temp") & "\PSTemp.txt" 'Build the basic PowerShell command sPSCmd = "powershell -command " & sPSCmd 'Add the Out-File so the output generates a text file sPSCmd = sPSCmd & " | Out-File '" & sTxtFile & "' -Encoding Default" 'Run the PowerShell command CreateObject("WScript.Shell").Run sPSCmd, 0, True 'Retrieve the content of the generated Text file With CreateObject("Scripting.FileSystemObject") 'Read the contents of the text file into memory PS_GetOutputFile = .OpenTextFile(sTxtFile).ReadAll() 'Delete the text file if so desired If bDelTxtFile = True Then .DeleteFile sTxtFile End With End Function Public Function PS_GetOutputClipBoard(ByVal sPSCmd As String) As String 'Setup the powershell command properly sPSCmd = "powershell -command " & sPSCmd & "|clip" 'Execute the command which is being pushed to the clipboard CreateObject("WScript.Shell").Run sPSCmd, 0, True 'Get an instance of the clipboard to capture the save value With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .GetFromClipboard PS_GetOutputClipBoard = .GetText(1) End With End Function the result was: Hay 1 interfaz en el sistema: Nombre : Wi-Fi Descripci+¦n : Realtek 8822CE Wireless LAN 802.11ac PCI-E NIC GUID : Direcci+¦n : Tipo de interfaz : Principal Estado : conectado SSID : Redmi Note 12 Pro 5G AP BSSID Banda : 2,4-áGHz Canal: 6 Tipo de red : Infraestructura Tipo de radio : 802.11n Autenticaci+¦n : WPA2-Personal Cifrado : CCMP Modo de conexi+¦n : Conexi+¦n autom+ítica Velocidad de recepci+¦n (Mbps) : 144.4 Velocidad de transmisi+¦n (Mbps) : 144.4 Se+¦al : 100% Perfil : Redmi Note 12 Pro 5G MSCS de QoS configurado: 0 Asignaci+¦n de QoS configurada: 0 Asignaci+¦n de QoS permitida por la directiva : 0 Estado de la red hospedada: No disponible this character +¦ it suposed to be a vocal with acent ´cause is in spanish language, also could be ñáéíóú Could someone help me please!! Thanks in advance.
ixxo09
Nov 22, 2024 Place Access
95Views
0likes
3Comments
Bar Code label problems
Office 365. I have labels (reports) with fields that are printed in a bar code font (Code 128 or 3 of 9). After automatic updates, these bar codes do not print properly. Is this a known problem? If not, what is causing these repeated problems? Thanks in advance,
Maxine14062
Nov 18, 2024 Place Access
93Views
0likes
7Comments
Dsum
Hello experts, I need to make a running sum total on tblDrawsDetails.Amount but noting the where clause. Please see attached accdb and xls file. I have the following but it just returns the grand total of Amount but I need it to sum where the ID< ID RunningSum: (DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) SELECT tblDrawsDetails.ID, tblDrawsDetails.FacIDfk, tblDrawsDetails.Amount, tblDrawsDetails.DateRecd, (DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) AS RunningSum, Year([DateRecd])*12+DatePart('m',[DateRecd])-1 AS MonthYrSort, First(Format([Daterecd],"yyyy"", ""mmmm")) AS FundingDateGrp FROM tblDrawsDetails GROUP BY tblDrawsDetails.ID, tblDrawsDetails.FacIDfk, tblDrawsDetails.Amount, tblDrawsDetails.DateRecd, Year([DateRecd])*12+DatePart('m',[DateRecd])-1 ORDER BY Year([DateRecd])*12+DatePart('m',[DateRecd])-1;
Solved
Tony2021
Nov 15, 2024 Place Access
68Views
0likes
8Comments
Copilot, Access, and VBA
I don't know how many of you use Copilot to assist you with Access. I developed my own application using Copilot and it was extremely helpful. I recommend you give it a try to see if it can help you. Here is an article I wrote on it in case you're interested: https://medium.com/@jmsp4home/did-my-own-spending-income-tracking-software-using-microsoft-copilot-heres-why-and-how-it-went-1aa8b4814cca?source=friends_link&sk=64ebb3ecf7e5b19c05d1731a571b1001
JM4SP
Nov 12, 2024 Place Access
179Views
0likes
7Comments