Create a GUID in MS Access

I have many databases that contain code to create a GUID that I use for renaming and importing files. Microsoft just put out a security update that blocked Scriptlet.TypeLib from being accessed. Well here’s another way…

Add this to the top of your module up where the Option statements are
Private Type GUID_TYPE
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type
Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (Guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (Guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr

Below is the actual function. Works like a charm
Function modIE_GetGUID()
On Error GoTo PROC_ERR
Dim strGuid As String
Dim retValue As LongPtr
Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
retValue = CoCreateGuid(Guid)
If retValue = 0 Then
strGuid = String$(guidLength, vbNullChar)
retValue = StringFromGUID2(Guid, StrPtr(strGuid), guidLength)
If retValue = guidLength Then
' valid GUID as a string
modIE_GetGUID = Mid$(strGuid, 2, 36) ' removes the braces from the output
End If
End If
Exit Function
MsgBox "Error: " & Err.Number & ". " & Err.Description
End Function

Get ThinOptics

ThinOptics have changed my life. Ever been somewhere and you can’t read your phone or even a menu at your favorite restaurant because you left your glasses behind. With ThinOptics they stick to the back of your phone. Tiny reading glasses right there when you need it most. I always have my phone on me – now I always have a pair of glasses. Brilliant idea. Super deal as well. Replacement glasses are FREE – yup free – for life.

HIGHLY recommend. Check them out. The link below gets you a 15% discount.

Check out ThinOptics

Too Many Excel Styles?

From time to time I get excel files that have what seems to be 10,000 styles embedded in it. I’ve found the more styles the slower Excel works and in some cases errors actually occur. Just recently I wasn’t able to apply a style at all – even through I had 1000’s available in the selector.

I found this tool that clears all of these styles back to the basics. Once I ran this tool I had no more issues with the spreadsheet. Enjoy.

XLS Styles Tool

Test Parameter Values for ADODB Connection

If you’re running ADODB to run a stored procedure on say SQL Server.  It’s helpful to know if the parameters you’re expecting to be passed actually are.  Add the For Each code block below just prior to the .Execute and you’ll see the param values in the immediate window

 Set cmd = New ADODB.Command
 With cmd
 .ActiveConnection = CurrentProject.Connection
 .CommandText = "spInsertOrUpdate_OPTJurisCode"
 .Parameters.Append .CreateParameter("RETURN_VALUE", adDouble, adParamReturnValue)
 .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, lngID)
 .Parameters.Append .CreateParameter("@SourceID", adInteger, adParamInput, CInt(gblSource))
 .Parameters.Append .CreateParameter("@ActionType", adVarChar, adParamInput, 4, gblactionType)
 .Parameters.Append .CreateParameter("@StateID", adInteger, adParamInput, , Me.cmbState)
 .Parameters.Append .CreateParameter("@City", adVarChar, adParamInput, 75, Me.txtCity)
 .Parameters.Append .CreateParameter("@Zip", adVarChar, adParamInput, 7, Me.txtZip)
 .Parameters.Append .CreateParameter("@JurisCode", adVarChar, adParamInput, 50, Me.txtJurisCode)
 .CommandType = adCmdStoredProc
 .CommandTimeout = 0
 'Test Param Values
 For Each prm In cmd.Parameters
 Debug.Print prm.Name & " : " & prm.Value
 End With


Refer to a Subform from another form

This took a while to figure out but Art Polk came to the rescue.

I had a popup form that when a button was pressed I needed to update the subform of the form which created the popup I was currently in.  Basically, I used a popup to edit one of the records in a data grid.  My objective was that once the update was processed the grid would refresh displaying the change…



That’s it