MS Access - Specify Max Length for TextBox or ComboBox on a Form

You should limit both events:

• KeyPress() for user typing
• Change() for copy/paste

Public Sub LimitKeyPress(ByRef pControl As Control, pMaxLen As Long, ByRef KeyAscii As Integer)

    On Error GoTo GesErr

'   -------------------------------------------------------------------------
'   -- GESTIONE MAX LENGTH IN UNA TEXTBOX/COMBO UNBOUND 1/2: KeyPress
'   -------------------------------------------------------------------------
'   Via:          http://allenbrowne.com/ser-34.html
'
'   Utilizzo:     Nella KeyPress():
'                   Private Sub Testo0_KeyPress(KeyAscii As Integer)
'                       LimitKeyPress Me.ActiveControl, cMaxLen, KeyAscii
'                   End Sub
'
'   NB:           Ricordarsi di lanciare anche la LimitChange()
'   -------------------------------------------------------------------------
    
    With pControl
        If Len(pControl.Text) - .SelLength >= pMaxLen Then
            If KeyAscii <> vbKeyBack Then
                KeyAscii = 0
            End If
        End If
    End With
    
    Exit Sub
    
GesErr:
    SysGesErr , StringFormat("LimitKeyPress({0}, {1}, {2})", pControl.name, pMaxLen, KeyAscii)
    
End Sub

Public Sub LimitChange(ByRef pControl As Control, pMaxLen As Long)

    On Error GoTo GesErr

'   -------------------------------------------------------------------------
'   -- GESTIONE MAX LENGTH IN UNA TEXTBOX/COMBO UNBOUND 2/2: Copy/Paste
'   -------------------------------------------------------------------------
'   Via:          http://allenbrowne.com/ser-34.html
'
'   Utilizzo:     Nella Change():
'                   Private Sub Testo0_Change()
'                       LimitChange Me.ActiveControl, cMaxLen
'                   End Sub
'
'   NB:           Ricordarsi di lanciare anche la LimitKeyPress()
'   -------------------------------------------------------------------------

    With pControl
        If Len(.Text) > pMaxLen Then
            .Text = Left(.Text, pMaxLen)
            .SelStart = pMaxLen
        End If
    End With
    
    Exit Sub

GesErr:
    SysGesErr , StringFormat("LimitChange({0}, {1})", pControl.name, pMaxLen)
    
End Sub

Using in the Form:

Private Sub Testo0_KeyPress(KeyAscii As Integer)
    LimitKeyPress Me.ActiveControl, cMaxLen, KeyAscii
End Sub

Private Sub Testo0_Change()
    LimitChange Me.ActiveControl, cMaxLen
End Sub

Via: http://allenbrowne.com/ser-34.html

MS Access VBA Bug - Numlock key keeps turning off with SendKeys

The SendKeys() function that is built-in VBA has really a side effect that causes NumLock to be deactivated.
But you can use a workaround and call another implementation of the same function that is a part of WScript component (a part of Windows operating system).

If you create a new Sub with the same name, it shadows the "system" SendKeys:

Public Sub SendKeys(pString, Optional pWait As Boolean = True)
   
    '-- There is a well known bug in all versions of Access involving SendKeys switching off the Numlock key.
    Dim WshShell

    Set WshShell = CreateObject("WScript.Shell")
    WshShell.SendKeys pString, pWait
    Set WshShell = Nothing

End Sub

https://stackoverflow.com/questions/25977933/sendkeys-is-messing-with-my-numlock-key-via-vba-code-in-access-form

VBA: Save text file UTF-8 encoded

Dim fsT As Object
Set fsT = CreateObject("ADODB.Stream")

fsT.Type = 2 'Specify stream type - we want To save text/string data.
fsT.Charset = "utf-8" 'Specify charset For the source text data.
fsT.Open 'Open the stream And write binary data To the object

fsT.WriteText "special characters: äöüß"
fsT.SaveToFile sFileName, 2 'Save binary data To disk

Found here.

VBA - Outlook_Export_Tasks.bas

'--##2021-10-28 - Boso -                    FullFilePath  //  utf-8
Option Explicit

Dim fs As Scripting.FileSystemObject
Dim txt

Private Sub ACapo()

    txt.WriteText vbCrLf
    
End Sub

Public Function parseString(s) As String

    Dim res As String

    If IsNull(s) Then
        res = ""
    Else
        res = ""
        res = res & Chr(34)
        res = res & Replace(s, Chr(34), Chr(34) & Chr(34))
        res = res & Chr(34)
    End If
    
    parseString = res

End Function

Private Sub ScriviRiga(taskFolder As Outlook.Folder)

    Dim olnameSpace As Outlook.NameSpace
    Dim tasks As Outlook.Items
    Dim x As Long
    Dim tsk As Outlook.TaskItem
    Dim s As String
    Dim wDataCrea As String
    Dim wDataScad As String

    Set tasks = taskFolder.Items

    Set tasks = taskFolder.Items

    For x = 1 To tasks.Count
        Set tsk = tasks.Item(x)

        If Not tsk.Complete Then
            Debug.Print Space(4) & Left(tsk.Subject, 50)
            
            With tsk
                wDataScad = ""
                If .DueDate <> DateSerial(4501, 1, 1) Then
                    wDataScad = .DueDate
                End If
                
                wDataCrea = ""
                If .CreationTime <> DateSerial(4501, 1, 1) Then
                    wDataCrea = .CreationTime
                End If
                
                s = taskFolder.Name & "|" & wDataCrea & "|" & wDataScad & "|" & parseString(.Subject) & "|" & parseString(.Body)
                txt.WriteText s
                ACapo
                
'                If LCase(.Subject) Like LCase("*Port of Earth*") Then Stop
            End With
            
        End If
    Next x

    Set olnameSpace = Nothing
    Set tasks = Nothing
    Set tsk = Nothing
    
End Sub

Private Sub EnumerateFolders(ByVal oFolder As Outlook.Folder)
    
    Dim folders As Outlook.folders
    Dim Folder As Outlook.Folder
    Dim foldercount As Integer
    
    On Error Resume Next
    Set folders = oFolder.folders
    foldercount = folders.Count
    
    'Check if there are any folders below oFolder
    If foldercount Then
        For Each Folder In folders
            
            If LCase(Folder.FolderPath) Like "*task*" Then
                Debug.Print Folder.Name
                ScriviRiga Folder
                EnumerateFolders Folder
            End If
            
        Next
    End If


    Set folders = Nothing
    Set Folder = Nothing

End Sub

Private Sub InitTXT(FullFilePath As String)
    
    Dim s As String
    
    Set txt = CreateObject("ADODB.Stream")

    With txt
        .Type = 2               ' Specify stream type - we want To save text/string data.
        .Charset = "utf-8"      ' Specify charset For the source text data.
        .Open                   ' Open the stream And write binary data To the object
    End With
    
    s = "FolderName|CreationTime|DueDate|Subject|Body"
    txt.WriteText s
    ACapo

End Sub


Sub Main()
    
    Dim colStores As Outlook.Stores
    Dim oStore As Outlook.Store
    Dim oRoot As Outlook.Folder
    Dim FullFilePath As String

    
    Set fs = New Scripting.FileSystemObject
    
    FullFilePath = "C:\Users\Boso\Downloads\BakToDo\Tasks.csv"
    
    InitTXT FullFilePath
    
    
    On Error Resume Next
    Set colStores = Application.Session.Stores
    
    For Each oStore In colStores
        Set oRoot = oStore.GetRootFolder
        Debug.Print (oRoot.FolderPath)
        EnumerateFolders oRoot
    Next
    
    txt.SaveToFile FullFilePath, 2  'Save binary data To disk
    txt.Close
    
    Set txt = Nothing
    Set fs = Nothing
    
    Set colStores = Nothing
    Set oStore = Nothing
    Set oRoot = Nothing
    
    
    Debug.Print
    Debug.Print "** FINE"
    Debug.Print
    Debug.Print "File creato: " & FullFilePath
    
End Sub

MS Access - Export All VBA Code

'--##2020-02-28 Boso -               ExportAllCode
Option Compare Database
Option Explicit


Public Enum vbext_ComponentType
    vbext_ct_Document = 100 '(&H64)

  'The component is a standard module.
  vbext_ct_StdModule = 1  '&H1

  'The component is a class module.
  vbext_ct_ClassModule = 2  '&H2

  'The component is a form.
  vbext_ct_MSForm = 3  '&H3

  'The component is a resource file.
  vbext_ct_ResFile = 4  '&H4

  'The component is a Visual Basic form.
  vbext_ct_VBForm = 5  '&H5

  'The component is an MDI form.
  vbext_ct_VBMDIForm = 6  '&H6

  'The component is a property page.
  vbext_ct_PropPage = 7  '&H7

  'The component is a UserControl object.
  vbext_ct_UserControl = 8  '&H8

  'The component is a DocObject.
  vbext_ct_DocObject = 9  '&H9

  'The component is a RelatedDocument object.
  vbext_ct_RelatedDocument = 10  '&HA

  'The component is a base class.
  vbext_ct_ActiveXDesigner = 11  '&HB
End Enum

Public Sub ExportAllCode()
    
    Dim wRoot As String
    Dim fs As New Scripting.FileSystemObject
    Dim C 'As VBComponent
    Dim ext As String
    Dim wExportFile As String
    Dim msg As String
    Dim wSubDir As String
    Dim wFullPath As String
    
    
    
    '--------------------------------------------------------------------------
    '-- initFolder
    '--------------------------------------------------------------------------
    With fs
        wRoot = .BuildPath(CurrentProject.Path, "ExportAllCode - " & .GetBaseName(CurrentProject.Name))
        
        If .FolderExists(wRoot) Then
            msg = StringFormat("La directory \n\n{0}\n\n esiste già e verrà eliminata: continuare?", wRoot)
            If MsgBox(msg, vbExclamation + vbYesNo + vbDefaultButton2, "ExportAllCode") = vbNo Then Exit Sub
            
            .DeleteFolder wRoot, Force:=True
        End If
        
        .CreateFolder wRoot
    End With
    
    
    '--------------------------------------------------------------------------
    '-- Export
    '--------------------------------------------------------------------------
    ext = ""
    wSubDir = ""
    
    For Each C In Application.VBE.VBProjects(1).VBComponents
    
        '-- DETERMINA SUB-DIR ED ESTENSIONE
        Select Case C.Type
            Case vbext_ct_ClassModule
                ext = ".cls"
                wSubDir = "Classes"

            Case vbext_ct_Document
                ext = ".cls"
                
                If C.Name Like "Report*" Then
                    wSubDir = "Reports"
                ElseIf C.Name Like "Form*" Then
                    wSubDir = "Forms"
                Else
                    wSubDir = "Other Documents"
                End If

            Case vbext_ct_MSForm
                ext = ".frm"
                wSubDir = "MS Forms"
                
            Case vbext_ct_StdModule
                ext = ".bas"
                wSubDir = "Modules"
                
        End Select
        
        
        '-- ESPORTA CODICE
        If ext <> "" Then
            With fs
                wFullPath = .BuildPath(wRoot, wSubDir)
                If Not .FolderExists(wFullPath) Then fs.CreateFolder wFullPath
            End With
            
            wExportFile = C.Name & ext
            wExportFile = Replace(wExportFile, "?", "§")
            wExportFile = fs.BuildPath(wFullPath, wExportFile)
            
            Debug.Print C.Name
            C.Export wExportFile
        End If
    Next C
    
    
    '-- FINE
    Debug.Print
    Debug.Print StringFormat("** FINITO - Esportato in {0}", wRoot)
    
    '-- APRE LA DIRECTORY
    ShellExecute 0, vbNullString, wRoot, vbNullString, vbNullString, 1       ' 1 = SW_SHOWNORMAL
    
    Set C = Nothing
    Set fs = Nothing
    
End Sub

Il provider 'Microsoft.ACE.OLEDB.12.0' non è registrato nel computer locale. (System.Data)

In SQL Management Studio, facendo una "Importazione/Esportazione guidata":

Impossibile completare l'operazione.

Il provider 'Microsoft.ACE.OLEDB.12.0' non è registrato nel computer locale. (System.Data)

Installare questi driver (32/64 bit in funzione della versione di SQL Management Studio!)

Questa sembra la versione precedente (non testata).

---

Nuovi URL per donwload:

32-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine.exe

64-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_X64.exe

Office 2007/2003 e MSCOMM32.ocx

Dopo l'aggiornamento KB969898 ci sono problemi ad usare il controllo da Access, per il momomento l'unica soluzione che ho trovato è rimuovere l'aggiornamento in oggetto. Il problema si presenta con il messaggio:

ENG: 'Object doesn't support this property or method'
ITA: 'Metoto o proprietà non supportati dall'oggetto'

Info dettagliate sul perché e il percome.

Trovato qui.

---

Soluzione alternativa:

Internet Explorer was blocking the MSCOMM control. It likely does so for other activex controls that are installed from unsigned (won't pay a fee) projects.

HKEY_LOCAL_MACHINE\Software\Microsoft\InternetExplorer\ActiveXCompatibility\{648A5600-2C6E-101B-82B6-000000000014}

modify the compatibility flag to a value of 0.

Trovato qui.

Access - evitare l'errore di modifica contemporanea di record

Una form di Access legata ad una tabella SQL via ODBC visualizza questo errore durante il salvataggio:

"Modifica contemporanea di record - Durante la corrente sessione di modifica il record è stato modificato da un altro utente. Salvando le proprie modifiche si sovrascriveranno i cambiamenti dell'altro utente"

Con questo trucco si dovrebbe* evitare l'errore:

ALTER TABLE Table1
ADD Timestamp

La sintassi per la ALTER TABLE (Transact-SQL) dice:

column_name

For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.

Sostanzialmente aggiunge una colonna di tipo timestamp che si chiama [timestamp] che si aggiorna in automatico.

____

* si dovrebbe = l'ho usato una volta e ha funzionato. =)

The 10/20/30 Rule of PowerPoint

It’s quite simple: a PowerPoint presentation should have ten slides, last no more than twenty minutes, and contain no font smaller than thirty points. While I’m in the venture capital business, this rule is applicable for any presentation to reach agreement: for example, raising capital, making a sale, forming a partnership, etc.

• Ten slides. Ten is the optimal number of slides in a PowerPoint presentation because a normal human being cannot comprehend more than ten concepts in a meeting—and venture capitalists are very normal. (The only difference between you and venture capitalist is that he is getting paid to gamble with someone else’s money). If you must use more than ten slides to explain your business, you probably don’t have a business. The ten topics that a venture capitalist cares about are:
  1. Problem
  2. Your solution
  3. Business model
  4. Underlying magic/technology
  5. Marketing and sales
  6. Competition
  7. Team
  8. Projections and milestones
  9. Status and timeline
  10. Summary and call to action
• Twenty minutes. You should give your ten slides in twenty minutes. Sure, you have an hour time slot, but you’re using a Windows laptop, so it will take forty minutes to make it work with the projector. Even if setup goes perfectly, people will arrive late and have to leave early. In a perfect world, you give your pitch in twenty minutes, and you have forty minutes left for discussion.
• Thirty-point font. The majority of the presentations that I see have text in a ten point font. As much text as possible is jammed into the slide, and then the presenter reads it. However, as soon as the audience figures out that you’re reading the text, it reads ahead of you because it can read faster than you can speak. The result is that you and the audience are out of synch.
  The reason people use a small font is twofold: first, that they don’t know their material well enough; second, they think that more text is more convincing. Total bozosity. Force yourself to use no font smaller than thirty points. I guarantee it will make your presentations better because it requires you to find the most salient points and to know how to explain them well. If “thirty points,” is too dogmatic, the I offer you an algorithm: find out the age of the oldest person in your audience and divide it by two. That’s your optimal font size.

So please observe the 10/20/30 Rule of PowerPoint. If nothing else, the next time someone in your audience complains of hearing loss, ringing, or vertigo, you’ll know what caused the problem. One last thing: to learn more about the zen of great presentations, check out a site called Presentation Zen by my buddy Garr Reynolds.

Trovato qui.

Inserire testo random in Word / Lorem Ipsum generator

You need to write the same function in Word as;

=rand()

On pressing enter, you will see the auto-fill paragraph.

 

Another place holding text filler which has been widely used in web designing and other prototypes is;

Lorem ipsum dolor sit amet, consectetur adipisicing elit……

For filling Word document with this placeholder filler, you need write it as

=lorem()

 

 

Note:

• Testato in Word 2007
• Alle funzioni si può passare un parametro numerico (la lunghezza del testo??).

Trovato qui.