A couple of caveats:
When we enter this form, it's usually through PM Select Checks. We first tried to set the Checkbook ID in the After Open event, but the field was still disabled then, the native code hadn't finished processing. As a kludge, I put the code in the Comment Got Focus event, because after the form had fully opened that's the field that had the focus.
Be sure to add the Comment and Checkbook ID fields to Visual Basic, and be sure to set a reference to .NET in the VBA
I'd love your comments!
Option Explicit
Dim cn As New ADODB.Connection
Private Sub Comment_AfterGotFocus()
' ADO Connection
Set cn = UserInfoGet.CreateADOConnection
'Use a client-side cursor so that a recordset count can be obtained later.
cn.CursorLocation = 3
'set the database to the currently logged in db
cn.DefaultDatabase = UserInfoGet.IntercompanyID
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String
If Me.CheckbookID = "" Then
sqlstring = "select CHEKBKID from PM40100 where UNIQKEY = 1"
' ADO Command
cmd.ActiveConnection = cn
' adCmdText
cmd.CommandType = 1
' Command
cmd.CommandText = sqlstring
' Pass through SQL
Set rst = cmd.Execute
If Not (rst.EOF And rst.BOF) Then
CheckbookID.Value = rst("CHEKBKID")
End If
rst.Close
End If
' Close ADO Connection
cn.Close
Set cn = Nothing
End Sub