Marcus Hall

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PtrSafe Access adjustment for 64 bit ======

2020-02-26


Compile error 64-bit

Change this

    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

to this and it will compile on both 32-bit and 64-bit

<code>
#If Win64 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#Else
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If
</code>
From <https://stackoverflow.com/questions/6255750/how-do-i-convert-this-program-to-work-on-a-64-bit-machine> 


2020-02-20

Compile error


 
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long
    
====== Can t find project or library ======
<WRAP center round box >


        dtFMSProcessingDate = Date
        
        
        MsgBox VBA.Date

From <https://stackoverflow.com/questions/31575414/access-vba-date-function-not-working> 

Also

2021-10-09

TRIM() not found? Compile error: Can't find project or library in Excel 2007

From <https://social.msdn.microsoft.com/Forums/office/en-US/608c32c1-3d47-47f4-a6ff-893b4695db20/trim-not-found-compile-error-cant-find-project-or-library-in-excel-2007?forum=exceldev> 

 
Answer: **On the VBA window go to Tools->References and uncheck the MISSING references.**

    ClosedXML - Create Excel files in .Net
	• Proposed as answer byTim Johnson at AptosFriday, June 6, 2014 5:13 PM
	• Unproposed as answer byTim Johnson at AptosFriday, June 6, 2014 5:13 PM
    Thursday, January 20, 2011 4:54 AM

From <https://social.msdn.microsoft.com/Forums/office/en-US/608c32c1-3d47-47f4-a6ff-893b4695db20/trim-not-found-compile-error-cant-find-project-or-library-in-excel-2007?forum=exceldev> 


</WRAP>


        

====== How to display Access query results without having to create temporary query? ======


From: http://stackoverflow.com/questions/17328092/how-to-display-access-query-results-without-having-to-create-temporary-query

<code>
Const cstrQueryName As String = "TemporaryQuery"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlStr As String

'*Logic to create SQL query... variable to hold query is called sqlStr*
' Apparently you have that piece worked out.  I'll use a simple query ...
sqlStr = "SELECT * FROM Dual;"

Set db = CurrentDb
If Not QueryExists(cstrQueryName) Then
    Set qdf = db.CreateQueryDef(cstrQueryName)
Else
    Set qdf = db.QueryDefs(cstrQueryName)
End If
qdf.sql = sqlStr
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery cstrQueryName
If you still want to later discard the saved query, do this ...

If QueryExists(cstrQueryName) Then
    DoCmd.DeleteObject acQuery, cstrQueryName
End If
This is the helper function for the above code ...

Public Function QueryExists(ByVal pName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim blnReturn As Boolean
    Dim strMsg As String

On Error GoTo ErrorHandler

    blnReturn = False ' make it explicit
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pName)
    blnReturn = True

ExitHere:
    Set qdf = Nothing
    Set db = Nothing
    QueryExists = blnReturn
    Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 3265 ' Item not found in this collection.
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure QueryExists"
        MsgBox strMsg
    End Select
    GoTo ExitHere

End Function
</code>


====== SaveAsText() in Access to export all code ======

From: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development

<code>
script in VBScript, that uses the undocumented Application.SaveAsText() in Access to export all code, form, macro and report modules.

Updated to use OpenAccessProject() if it sees a .adp extension, else use OpenCurrentDatabase()

----------------------------------------------------------------------------------------------
' Usage:
'  CScript decompose.vbs <input file> <path>

' Converts all modules, classes, forms and macros from an Access Project file (.adp) <input file> to
' text and saves the results in separate files to <path>.  Requires Microsoft Access.
'

Option Explicit

const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3

' BEGIN CODE
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

dim sADPFilename
If (WScript.Arguments.Count = 0) then
    MsgBox "Bitte den Dateinamen angeben!", vbExclamation, "Error"
    Wscript.Quit()
End if
sADPFilename = fso.GetAbsolutePathName(WScript.Arguments(0))

Dim sExportpath
If (WScript.Arguments.Count = 1) then
    sExportpath = ""
else
    sExportpath = WScript.Arguments(1)
End If


exportModulesTxt sADPFilename, sExportpath

If (Err <> 0) and (Err.Description <> NULL) Then
    MsgBox Err.Description, vbExclamation, "Error"
    Err.Clear
End If

Function exportModulesTxt(sADPFilename, sExportpath)
    Dim myComponent
    Dim sModuleType
    Dim sTempname
    Dim sOutstring

    dim myType, myName, myPath, sStubADPFilename
    myType = fso.GetExtensionName(sADPFilename)
    myName = fso.GetBaseName(sADPFilename)
    myPath = fso.GetParentFolderName(sADPFilename)

    If (sExportpath = "") then
        sExportpath = myPath & "\Source\"
    End If
    sStubADPFilename = sExportpath & myName & "_stub." & myType

    WScript.Echo "copy stub to " & sStubADPFilename & "..."
    On Error Resume Next
        fso.CreateFolder(sExportpath)
    On Error Goto 0
    fso.CopyFile sADPFilename, sStubADPFilename

    WScript.Echo "starting Access..."
    Dim oApplication
    Set oApplication = CreateObject("Access.Application")
    WScript.Echo "opening " & sStubADPFilename & " ..."
    If (Right(sStubADPFilename,4) = ".adp") Then
        oApplication.OpenAccessProject sStubADPFilename
    Else
        oApplication.OpenCurrentDatabase sStubADPFilename
    End If

    oApplication.Visible = false

    dim dctDelete
    Set dctDelete = CreateObject("Scripting.Dictionary")
    WScript.Echo "exporting..."
    Dim myObj
    For Each myObj In oApplication.CurrentProject.AllForms
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acForm, myObj.fullname, sExportpath & "\" & myObj.fullname & ".form"
        oApplication.DoCmd.Close acForm, myObj.fullname
        dctDelete.Add "FO" & myObj.fullname, acForm
    Next
    For Each myObj In oApplication.CurrentProject.AllModules
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acModule, myObj.fullname, sExportpath & "\" & myObj.fullname & ".bas"
        dctDelete.Add "MO" & myObj.fullname, acModule
    Next
    For Each myObj In oApplication.CurrentProject.AllMacros
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acMacro, myObj.fullname, sExportpath & "\" & myObj.fullname & ".mac"
        dctDelete.Add "MA" & myObj.fullname, acMacro
    Next
    For Each myObj In oApplication.CurrentProject.AllReports
        WScript.Echo "  " & myObj.fullname
        oApplication.SaveAsText acReport, myObj.fullname, sExportpath & "\" & myObj.fullname & ".report"
        dctDelete.Add "RE" & myObj.fullname, acReport
    Next

    WScript.Echo "deleting..."
    dim sObjectname
    For Each sObjectname In dctDelete
        WScript.Echo "  " & Mid(sObjectname, 3)
        oApplication.DoCmd.DeleteObject dctDelete(sObjectname), Mid(sObjectname, 3)
    Next

    oApplication.CloseCurrentDatabase
    oApplication.CompactRepair sStubADPFilename, sStubADPFilename & "_"
    oApplication.Quit

    fso.CopyFile sStubADPFilename & "_", sStubADPFilename
    fso.DeleteFile sStubADPFilename & "_"


End Function

Public Function getErr()
    Dim strError
    strError = vbCrLf & "----------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & _
               "From " & Err.source & ":" & vbCrLf & _
               "    Description: " & Err.Description & vbCrLf & _
               "    Code: " & Err.Number & vbCrLf
    getErr = strError
End Function
</code>

====== Replacing the Calendar Control in Access 2010 Applications ======

Calendar control

From: http://msdn.microsoft.com/en-us/library/office/gg251104%28v=office.14%29.aspx

    Microsoft Access has included a Calendar control since it first added support for ActiveX 
    controls, in Access 2.0. The calendar control, MSCAL.OCX, is not included with Access 2010.

    Consider the following pros and cons of each option:
    Built-in date picker
        Simple to use (requires only setting the ShowDatePicker property).
        No customization available.
        Works only with TextBox controls bound to a Date field in a table.
    DatePicker Control
        Reasonably easy to use.
        Allows more customization than the built-in date picker.
        Can also be used to select times.
        Can be bound to a data source.
        Depends on the Microsoft Custom Controls DLL, which introduces serious versioning issues. Your
        end-users may not have the same version of the DLL installed as you do, and this requires 
        updating their DLL to the latest version.
        Only appears as a drop-down, and cannot appear as an open calendar.
    Calendar Form
        Appears only as an open calendar (both a pro and a con).
        Completely customizable, but requires some work in the designer and in code.
        Easy to use─merely drag onto a form and write the code.
        Hooking up events requires additional code.
        Binding is not available without writing code.

====== Alternatives to SendKey ======

From: http://www.pcreview.co.uk/forums/alternative-sendkeys-keystrokes-f9-wait-yes-t3162974.html

    DoCmd.GoToControl "Balance"
    
From: http://www.access-programmers.co.uk/forums/showthread.php?t=47212

    By the way, in that code snippet, you might be wondering why moving the focus to the txtField field
    involves two steps:
    1- Me.subForm.SetFocus
       and then,
    2- Me.subForm.Form.Controls("txtField").SetFocus


====== num lock keeps shutting off ======

From: http://answers.microsoft.com/en-us/windows/forum/windows_7-performance/my-num-lock-keeps-shutting-itself-off-by-itself/f50053ac-826b-4a35-acc7-16fe7c431f77

From: http://support.microsoft.com/kb/179987

    Multiple Sendkeys
    
From: http://bytes.com/topic/access/answers/943432-sendkeys-turns-off-numlock

     SendKeys statement activates, the NumLock status automatically turns OFF

====== How to solve missing MSCAL.OCX reference in Microsoft Access 2010 ======

From: http://community.spiceworks.com/how_to/show/5517-how-to-solve-missing-mscal-ocx-reference-in-microsoft-access-2010

From: https://social.msdn.microsoft.com/Forums/office/en-US/fd1d449e-d39c-4c0b-99ea-ed561d8906ce/mscalocx-microsoft-calendar-control-conversion-to-mscomct2ocx?forum=exceldev

    However note, that the solution specified is a work-around for the above problem. Microsoft has stated 
    that the Calendar control feature is deprecated in Access 2010 and has suggested a number of options 
    for replacing it. See the link referenced at the bottom of this page for more details
    
    Centerport
  
  
    I. Click on the Windows Start menu and select 'Run' 
    II. In the Run command window enter:
       regsvr32 %SystemRoot%\syswow64\mscal.ocx 
    III. Select OK to execute the command  
====== Forms: Date Picker without ActiveX ======

From: http://access.mvps.org/access/forms/frm0057.htm

Forms: Date Picker without ActiveX; I have a form with a date field or a block of code that needs to
input a date and I want to the user to have a calendar view to select
that date from. I don't want to use any ActiveX components.

(A) Use the Access Date Picker.


====== wildcards for SQL ======

From: http://msdn.microsoft.com/en-us/library/office/aa140104(v=office.10).aspx

The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character. There's no single-digit wildcard available through ADO that equates to DAO's pound sign wildcard (#); however, character lists and ranges behave the same.
====== split for openargs ======

http://movefirstblog.wordpress.com/2008/07/09/microsoft-access-parse-openargs-seperating-each-openarg-value/

====== visual-basic-combo-box-tutorial ======

[[http://www.vb6.us/tutorials/visual-basic-combo-box-tutorial]]

[[http://www.mvps.org/access/api/api0002.htm]]


For an action query you might do something like:

Dim strSQL As String
strSQL = "Delete * From MyTable Where ID =" & Me.txtID
CurrentDB.Execute strSQL

You can also use DoCmd.RunSQL:
DoCmd SetWarning False
DoCmd.RunSQL strSQL
DoCmd SetWarning True

Or you might build a recordset:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

strSQL = "Select * From MyTable"
Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

[[http://www.databasejournal.com/features/msaccess/article.php/3505836/Executing-SQL-Statements-in-VBA-Code.htm]]