Use of CurrentDb.OpenRecordset in VBA

See also questions close to this topic

• loop through folder & sub-folder to open an excel file with file not found

  I want vba loop through folders & sub-folders and open the excel file with a variable name that I pre-defined. File name will be PIF_08.31.2018 based on this month but next month file name will be PIF_09.30.2018. So I want vba to look in folder and sub-folder and open the file that I specified.

  I got an error for which it says file is not found but clearly it is in the sub-folder. I am suspecting my codes didn't go into any folders or didn't go into the subfolders.

  Structure of my folders are Main -> Year (i.e. 2018) -> Month (i.e. 08 August) and in each month folder, I have 2 files and I want to open the one that I specified.

  Sub test()
  Dim FileSystem As Object
  Dim HostFolder As String
      HostFolder = "S:\Main\"
      Set FileSystem = CreateObject("Scripting.FileSystemObject")
  DoFolder FileSystem.GetFolder(HostFolder)
  End Sub
  
  
  Sub DoFolder(Folder)
  Dim SubFolder
  Dim CurrFile
  Dim fname
  Dim folderpath
  Dim File       
  fname = "PIF_" & Format(WorksheetFunction.EoMonth(Now, -2), "MM.D.YYYY") & ".xlsx"
      'PIF_08.31.2018
      For Each SubFolder In Folder.subfolders
          DoFolder SubFolder
      Next   
      For Each File In Folder.Files
               If File.Name = fname Then
                              Workbooks.Open (File.Path)
                       End If
       Next
  End Sub
  

  

  
• Get Outlook Shared Calendar without Delegation VBA/C#

  As the GetSharedDefaultFolder method description states:

  This method is used in a delegation scenario, where one user has delegated access to another user for one or more of their default folders (for example, their shared Calendar folder).

  Is there a way to get shared calendar appointment items without being a delegate of the person?

  For example, I could do an File -> Open -> Open User's Folder to see people's busy/free status for about anyone or mail box. But apparently this permission level won't enable the GetSharedDefaultFolder() method.
• Loop through Controls on a Userform to hide specific controls

  I have looked through numerous posts on this and come up with the solution below, but I am wondering if there is a more efficient way to code this.

  The cases are based on a ComboBox value 0 to 8 that the user chooses and then Labels and TextBoxes will show up based on the number in the ComboBox.

  All of the Labels and TextBoxes I want to hide or show start with "b" and then the number 1 through 8 as you can see in the code below. I appreciate any suggestions to improve the efficiency of the loops I have running.

   Select Case LendStart.lsNumBorr.Value
      Case Is = 0
          For Each ctrl In LendStart.Controls
              If Left(ctrl.Name, 1) = "b" Then
                  ctrl.Visible = False
              End If
          Next
      Case Is = 1
          For Each ctrl In LendStart.Controls
              If Left(ctrl.Name, 2) = "b1" Then
                  ctrl.Visible = True
                      If Left(ctrl.Name, 2) = "b2" Or Left(ctrl.Name, 2) = "b3" Or Left(ctrl.Name, 2) = "b4" Or Left(ctrl.Name, 2) = "b5" Or Left(ctrl.Name, 2) = "b6" Or Left(ctrl.Name, 2) = "b7" Or Left(ctrl.Name, 2) = "b8" Then
                          ctrl.Visible = False
                      End If
              End If
          Next
      Case Is = 2
          For Each ctrl In LendStart.Controls
              If Left(ctrl.Name, 2) = "b1" Or Left(ctrl.Name, 2) = "b2" Then
                  ctrl.Visible = True
                      If Left(ctrl.Name, 2) = "b3" Or Left(ctrl.Name, 2) = "b4" Or Left(ctrl.Name, 2) = "b5" Or Left(ctrl.Name, 2) = "b6" Or Left(ctrl.Name, 2) = "b7" Or Left(ctrl.Name, 2) = "b8" Then
                          ctrl.Visible = False
                      End If
              End If
          Next
   End Select
  
• Attaching multiple objects to one email using EmailDatabaseObject macro command

  Attempting to simplify some reports my company runs through access, I'm trying to find a way to attach multiple tables to one email automatically using the EmailDatabaseObject macro command. An example of one of the other macros I use to attach one table to an email as an excel document is below:

  The current macro I'm building is
  
  Unlike the first one, it results in two tables. I'm not sure if there is a way to send these in a similar way as double attachments on one email, but if anyone knows a way to do so, it would be MUCH appreciated.

  THANKS!
• Hospital database: finding instances of Health Card Numbers appearing on more than one Health Record

  In Ontario, each resident is assigned one Health Card Number (HCN). At our hospital, patients are assigned a Medical Record Number (MRN).

  I'm wanting to find instances where one HCN appears on multiple MRN's (this can be caused by clerks creating a new MRN even when one already exists for a patient/HCN).

  I want the output to list just one example of each 'duplicate' MRN created, with other data to allow for good, quick decisions to be made about whether to merge the records or not (eg: birthdate, name, address fields, email address).

  Tables I have include:

  • list of MRN, Name, Birthdate
  • a table which links the MRN to a 'unique record number' (Urn) used in the following tables
  • list of Urn and HCN
  • list of Urn and Patient address

  I'm using Microsoft Access, and have set up the relations. I've tried some different code, but could use some help. I'm still learning SQL and can't get it right with this yet.

  This is the baseline code Access generates:

  SELECT [HCN, Mri Urn].[Edited HCN], [MRN, Name, DOB].[Medical Record],
     [MRN, Name, DOB].Name, [MRN, Name, DOB].Birthdate, 
     [Pat Address, Adm Urn].[Pat City], [Pat Address, Adm Urn].[Pat Street],
     [Pat Address, Adm Urn].[Pat Street2], [Pat Address, Adm Urn].[Pat Email],
     [Pat Address, Adm Urn].[Pat Home Phone]
  FROM [Pat Address, Adm Urn] 
  INNER JOIN ([MRN, Name, DOB] 
  INNER JOIN ([HCN, Mri Urn] 
  INNER JOIN [ADM's Mri Urn-Adm Urn link] 
     ON [HCN, Mri Urn].Urn = [ADM's Mri Urn-Adm Urn link].[Mri Urn]) 
     ON [MRN, Name, DOB].[Medical Record] = [ADM's Mri Urn-Adm Urn link].[Mri Urn]) 
     ON [Pat Address, Adm Urn].Urn = [ADM's Mri Urn-Adm Urn link].Urn
  ORDER BY [HCN, Mri Urn].[Edited HCN], 
     [MRN, Name, DOB].[Medical Record], 
     [MRN, Name, DOB].Name, 
     [MRN, Name, DOB].Birthdate, 
     [Pat Address, Adm Urn].[Pat City], 
     [Pat Address, Adm Urn].[Pat Street], 
     [Pat Address, Adm Urn].[Pat Street2], 
     [Pat Address, Adm Urn].[Pat Email], 
     [Pat Address, Adm Urn].[Pat Home Phone];
  
• How to add the Table Name of the changed record along with typical AuditTrail in MS Access

  I am using Audit Trail which works great. The code I have is:

  Sub AuditChanges(IDField As String, UserAction As String)
  On Error GoTo AuditChanges_Err
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim ctl As Control
  Dim datTimeCheck As Date
  Dim strUserID As String
  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  datTimeCheck = Now()
  strUserID = Environ("USERNAME")
  Select Case UserAction
      Case "EDIT"
          For Each ctl In Screen.ActiveForm.Controls
              If ctl.Tag = "Audit" Then
                  If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = Screen.ActiveForm.Name
                          ![Action] = UserAction
                          ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                          ![FieldName] = ctl.ControlSource
                          ![OldValue] = ctl.OldValue
                          ![NewValue] = ctl.Value
                          .Update
                      End With
                  End If
              End If
          Next ctl
      Case Else
          With rst
              .AddNew
              ![DateTime] = datTimeCheck
              ![UserName] = strUserID
              ![FormName] = Screen.ActiveForm.Name
              ![Action] = UserAction
              ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
              .Update
          End With
  End Select
  AuditChanges_Exit:
  On Error Resume Next
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing
  Exit Sub
  AuditChanges_Err:
  MsgBox Err.Description, vbCritical, "ERROR!"
  Resume AuditChanges_Exit
  

  End Sub

  The only issue I have is that I have several tables all of which are using autonumbers as primary keys. Audit trail logs the same RecordID for both my CompanyT and my EmployeeT; for example, and I can't differentiate which was updated. I need to log the actual table name where the RecordID was sourced. In other words if RecordID 123 was updated, I want Audit Trail to tell me that was a primary key from EmployeeT table.

  How could I tweak the code above to capture the table name?