On 08 Jun 2007 20:34:47 GMT, Rich P <rp*****@aol.comwrote:
>>>
Is is possible to use an ADO recordset to populate an unbound continuous
Subform?
<<
No.
I've been informed from another source which has provided ideals that is
working in my code. Saying it simple we disagree with your answer.
See code below. Note the class has more code than necessary because I
was experimenting with different approaches. The solution has (1) the
form setting the recordset = to the opened ADO recordset in the class
module after passing it the form with (2) the appropriate controls for
the recordset 'Control Source' = to the field name.
(1) Set pfrm.Recordset = grsAcl 'see Sub FillContinuousFormOut
(2) on form property Control Source of 'field name' (ie. COID, etc)
--
Regards,
Greg Strong
Unbound form code with Textboxes Control source = Recordset field name
-------------------------------------------------------------->
Option Compare Database
Option Explicit
Public objADOform As FormADOReadWrite
Dim frm As Form
Dim strFrmName As String
Dim lngRcdNumber As Long
Private Sub Form_Close()
Set frm = Nothing
objADOform.CloseCnnADOcl
objADOform.Cleanup
End Sub
Private Sub Form_Open(Cancel As Integer)
strFrmName = Me.Name
Set frm = Forms(Me.Name)
Set objADOform = New FormADOReadWrite
objADOform.OpenCnnADOcl
objADOform.SetSQLRecordSet strFrmName
objADOform.FillContinuousFormOut frm
End Sub
Private Sub txtFocus_GotFocus()
'set focus to run code
Me!fsbRcdNavgtADO.SetFocus
Me!fsbRcdNavgtADO.Form!txtSubNavFocus.SetFocus
End Sub
--------------------------------------------------------------<
Class module FormADOReadWrite:
-------------------------------------------------------------->
Option Compare Database
Option Explicit
Private gcnnADOcl As ADODB.Connection
Private grsAcl As ADODB.Recordset
Private mobjSubFrmNav As SubFrmRecdNavigate
Private glngRecdCount As Long, mlngMoveNextRecordNo As Long, _
mlngMovePrevRecordNo As Long
Property Let RecordSetCount(lngRecNo As Long)
'grsAcl.MoveFirst
'grsAcl.MoveLast
lngRecNo = grsAcl.RecordCount
'grsAcl.MoveFirst
glngRecdCount = lngRecNo
End Property
Property Get RecordSetCount() As Long
RecordSetCount = glngRecdCount
End Property
Property Let MoveNextRecordNo(lngPrevRecdNo As Long)
'Debug.Print "MoveNext Let / Prev Recd No: "; lngPrevRecdNo & " Recd
Cnt: " & glngRecdCount
If lngPrevRecdNo + 1 <= glngRecdCount Then
lngPrevRecdNo = lngPrevRecdNo + 1
mlngMoveNextRecordNo = lngPrevRecdNo
Else
mlngMoveNextRecordNo = lngPrevRecdNo
End If
End Property
Property Get MoveNextRecordNo() As Long
MoveNextRecordNo = mlngMoveNextRecordNo
End Property
Property Let MovePreviousRecordNo(lngNextRecdNo As Long)
If lngNextRecdNo - 1 0 Then
lngNextRecdNo = lngNextRecdNo - 1
mlngMovePrevRecordNo = lngNextRecdNo
Else
mlngMovePrevRecordNo = lngNextRecdNo
End If
End Property
Property Get MovePreviousRecordNo() As Long
MovePreviousRecordNo = mlngMovePrevRecordNo
End Property
Sub OpenCnnADOcl()
Set gcnnADOcl = New ADODB.Connection
With gcnnADOcl
.ConnectionString = SetConStr(1)
.Open
End With
End Sub
Sub CloseCnnADOcl()
Set grsAcl = Nothing
With gcnnADOcl
.Close
End With
End Sub
Sub SetSQLRecordSet(ByVal strFormName As String)
Dim strSQL As String
Select Case strFormName
Case "frmCoToDo"
'pull all co's w/ ToDo = -1
strSQL = "SELECT " & gstrTblSpc & ".TBLCOMPANYINFO.COID, " &
gstrTblSpc & ".TBLCOMPANYINFO.CONAME, " & gstrTblSpc &
".TBLCOMPANYINFO.DIV, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSADDR, " &
gstrTblSpc & ".TBLCOMPANYINFO.PHYSCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PHYSSTATE, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSZIP, "
& gstrTblSpc & ".TBLCOMPANYINFO.PHYSCTY, " & gstrTblSpc &
".TBLCOMPANYINFO.MAILADDR, " & gstrTblSpc & ".TBLCOMPANYINFO.MAILCITY, "
& gstrTblSpc & ".TBLCOMPANYINFO.MAILSTATE, " & _
gstrTblSpc & ".TBLCOMPANYINFO.MAILZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.CPUBRAND, " & gstrTblSpc & ".TBLCOMPANYINFO.CPUMODEL, "
& gstrTblSpc & ".TBLCOMPANYINFO.CPULANG, " & gstrTblSpc &
".TBLCOMPANYINFO.TELENUMBER, " & gstrTblSpc &
".TBLCOMPANYINFO.YEARESTAB, " & gstrTblSpc &
".TBLCOMPANYINFO.DISTRIBTYPE, " & gstrTblSpc &
".TBLCOMPANYINFO.OWNERTYPE, " & gstrTblSpc & ".TBLCOMPANYINFO.LOCCOUNT,
" & gstrTblSpc & ".TBLCOMPANYINFO.PRIMARYSIC, " & _
gstrTblSpc & ".TBLCOMPANYINFO.SIC2, " & gstrTblSpc &
".TBLCOMPANYINFO.SIC3, " & gstrTblSpc & ".TBLCOMPANYINFO.SIC4, " &
gstrTblSpc & ".TBLCOMPANYINFO.MINSALES, " & gstrTblSpc &
".TBLCOMPANYINFO.MAXSALES, " & gstrTblSpc & ".TBLCOMPANYINFO.SQUAREFEET,
" & gstrTblSpc & ".TBLCOMPANYINFO.IMPORTS, " & gstrTblSpc &
".TBLCOMPANYINFO.LocalTollNo, " & gstrTblSpc &
".TBLCOMPANYINFO.NATLTOLLNO, " & gstrTblSpc & ".TBLCOMPANYINFO.FAXNO, "
& gstrTblSpc & ".TBLCOMPANYINFO.PRODUCTS, " & _
gstrTblSpc & ".TBLCOMPANYINFO.BRANDS, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTNAME, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTADDR, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTSTATE, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTTELENO, " & gstrTblSpc & ".TBLCOMPANYINFO.WEBURL,
" & gstrTblSpc & ".TBLCOMPANYINFO.EMAIL, " & gstrTblSpc &
".TBLCOMPANYINFO.TODO " & _
"FROM " & gstrTblSpc & ".TBLCOMPANYINFO " & _
"WHERE (((" & gstrTblSpc & ".TBLCOMPANYINFO.TODO)=-1)) "
& _
"ORDER BY " & gstrTblSpc & ".TBLCOMPANYINFO.COID"
'Debug.Print "sql frmCoToDo"
Case "frmContactInfo"
strSQL = "SELECT " & gstrTblSpc & ".CONTACTID, " &
gstrTblSpc & ".CONTACTDATE, " & gstrTblSpc & ".EXECID, " & gstrTblSpc &
".TYPEID, " & gstrTblSpc & ".NOTES, " & gstrTblSpc & ".COMPLETED, " &
gstrTblSpc & ".COMPLETEDDATE"
Debug.Print "sql frmContactInfo"
Case "frmTest"
strSQL = "SELECT " & gstrTblSpc & ".TBLCOMPANYINFO.COID, " &
gstrTblSpc & ".TBLCOMPANYINFO.CONAME, " & gstrTblSpc &
".TBLCOMPANYINFO.DIV, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSADDR, " &
gstrTblSpc & ".TBLCOMPANYINFO.PHYSCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PHYSSTATE, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSZIP, "
& gstrTblSpc & ".TBLCOMPANYINFO.PHYSCTY, " & gstrTblSpc &
".TBLCOMPANYINFO.MAILADDR, " & gstrTblSpc & ".TBLCOMPANYINFO.MAILCITY, "
& gstrTblSpc & ".TBLCOMPANYINFO.MAILSTATE, " & _
gstrTblSpc & ".TBLCOMPANYINFO.MAILZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.CPUBRAND, " & gstrTblSpc & ".TBLCOMPANYINFO.CPUMODEL, "
& gstrTblSpc & ".TBLCOMPANYINFO.CPULANG, " & gstrTblSpc &
".TBLCOMPANYINFO.TELENUMBER, " & gstrTblSpc &
".TBLCOMPANYINFO.YEARESTAB, " & gstrTblSpc &
".TBLCOMPANYINFO.DISTRIBTYPE, " & gstrTblSpc &
".TBLCOMPANYINFO.OWNERTYPE, " & gstrTblSpc & ".TBLCOMPANYINFO.LOCCOUNT,
" & gstrTblSpc & ".TBLCOMPANYINFO.PRIMARYSIC, " & _
gstrTblSpc & ".TBLCOMPANYINFO.SIC2, " & gstrTblSpc &
".TBLCOMPANYINFO.SIC3, " & gstrTblSpc & ".TBLCOMPANYINFO.SIC4, " &
gstrTblSpc & ".TBLCOMPANYINFO.MINSALES, " & gstrTblSpc &
".TBLCOMPANYINFO.MAXSALES, " & gstrTblSpc & ".TBLCOMPANYINFO.SQUAREFEET,
" & gstrTblSpc & ".TBLCOMPANYINFO.IMPORTS, " & gstrTblSpc &
".TBLCOMPANYINFO.LocalTollNo, " & gstrTblSpc &
".TBLCOMPANYINFO.NATLTOLLNO, " & gstrTblSpc & ".TBLCOMPANYINFO.FAXNO, "
& gstrTblSpc & ".TBLCOMPANYINFO.PRODUCTS, " & _
gstrTblSpc & ".TBLCOMPANYINFO.BRANDS, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTNAME, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTADDR, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTSTATE, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTTELENO, " & gstrTblSpc & ".TBLCOMPANYINFO.WEBURL,
" & gstrTblSpc & ".TBLCOMPANYINFO.EMAIL, " & gstrTblSpc &
".TBLCOMPANYINFO.TODO " & _
"FROM " & gstrTblSpc & ".TBLCOMPANYINFO " & _
"WHERE (((" & gstrTblSpc & ".TBLCOMPANYINFO.TODO)=-1)) "
& _
"ORDER BY " & gstrTblSpc & ".TBLCOMPANYINFO.COID"
'Debug.Print "sql frmCoToDo"
Case "frmTest_r2"
strSQL = "SELECT " & gstrTblSpc & ".TBLCOMPANYINFO.COID, " &
gstrTblSpc & ".TBLCOMPANYINFO.CONAME, " & gstrTblSpc &
".TBLCOMPANYINFO.DIV, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSADDR, " &
gstrTblSpc & ".TBLCOMPANYINFO.PHYSCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PHYSSTATE, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSZIP, "
& gstrTblSpc & ".TBLCOMPANYINFO.PHYSCTY, " & gstrTblSpc &
".TBLCOMPANYINFO.MAILADDR, " & gstrTblSpc & ".TBLCOMPANYINFO.MAILCITY, "
& gstrTblSpc & ".TBLCOMPANYINFO.MAILSTATE, " & _
gstrTblSpc & ".TBLCOMPANYINFO.MAILZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.CPUBRAND, " & gstrTblSpc & ".TBLCOMPANYINFO.CPUMODEL, "
& gstrTblSpc & ".TBLCOMPANYINFO.CPULANG, " & gstrTblSpc &
".TBLCOMPANYINFO.TELENUMBER, " & gstrTblSpc &
".TBLCOMPANYINFO.YEARESTAB, " & gstrTblSpc &
".TBLCOMPANYINFO.DISTRIBTYPE, " & gstrTblSpc &
".TBLCOMPANYINFO.OWNERTYPE, " & gstrTblSpc & ".TBLCOMPANYINFO.LOCCOUNT,
" & gstrTblSpc & ".TBLCOMPANYINFO.PRIMARYSIC, " & _
gstrTblSpc & ".TBLCOMPANYINFO.SIC2, " & gstrTblSpc &
".TBLCOMPANYINFO.SIC3, " & gstrTblSpc & ".TBLCOMPANYINFO.SIC4, " &
gstrTblSpc & ".TBLCOMPANYINFO.MINSALES, " & gstrTblSpc &
".TBLCOMPANYINFO.MAXSALES, " & gstrTblSpc & ".TBLCOMPANYINFO.SQUAREFEET,
" & gstrTblSpc & ".TBLCOMPANYINFO.IMPORTS, " & gstrTblSpc &
".TBLCOMPANYINFO.LocalTollNo, " & gstrTblSpc &
".TBLCOMPANYINFO.NATLTOLLNO, " & gstrTblSpc & ".TBLCOMPANYINFO.FAXNO, "
& gstrTblSpc & ".TBLCOMPANYINFO.PRODUCTS, " & _
gstrTblSpc & ".TBLCOMPANYINFO.BRANDS, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTNAME, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTADDR, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTSTATE, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTTELENO, " & gstrTblSpc & ".TBLCOMPANYINFO.WEBURL,
" & gstrTblSpc & ".TBLCOMPANYINFO.EMAIL, " & gstrTblSpc &
".TBLCOMPANYINFO.TODO " & _
"FROM " & gstrTblSpc & ".TBLCOMPANYINFO " & _
"WHERE (((" & gstrTblSpc & ".TBLCOMPANYINFO.TODO)=-1)) "
& _
"ORDER BY " & gstrTblSpc & ".TBLCOMPANYINFO.COID"
'Debug.Print "sql frmCoToDo"
Case "frm_Test_ContinuousForm"
strSQL = "SELECT " & gstrTblSpc & ".TBLCOMPANYINFO.COID, " &
gstrTblSpc & ".TBLCOMPANYINFO.CONAME, " & gstrTblSpc &
".TBLCOMPANYINFO.DIV, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSADDR, " &
gstrTblSpc & ".TBLCOMPANYINFO.PHYSCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PHYSSTATE, " & gstrTblSpc & ".TBLCOMPANYINFO.PHYSZIP, "
& gstrTblSpc & ".TBLCOMPANYINFO.PHYSCTY, " & gstrTblSpc &
".TBLCOMPANYINFO.MAILADDR, " & gstrTblSpc & ".TBLCOMPANYINFO.MAILCITY, "
& gstrTblSpc & ".TBLCOMPANYINFO.MAILSTATE, " & _
gstrTblSpc & ".TBLCOMPANYINFO.MAILZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.CPUBRAND, " & gstrTblSpc & ".TBLCOMPANYINFO.CPUMODEL, "
& gstrTblSpc & ".TBLCOMPANYINFO.CPULANG, " & gstrTblSpc &
".TBLCOMPANYINFO.TELENUMBER, " & gstrTblSpc &
".TBLCOMPANYINFO.YEARESTAB, " & gstrTblSpc &
".TBLCOMPANYINFO.DISTRIBTYPE, " & gstrTblSpc &
".TBLCOMPANYINFO.OWNERTYPE, " & gstrTblSpc & ".TBLCOMPANYINFO.LOCCOUNT,
" & gstrTblSpc & ".TBLCOMPANYINFO.PRIMARYSIC, " & _
gstrTblSpc & ".TBLCOMPANYINFO.SIC2, " & gstrTblSpc &
".TBLCOMPANYINFO.SIC3, " & gstrTblSpc & ".TBLCOMPANYINFO.SIC4, " &
gstrTblSpc & ".TBLCOMPANYINFO.MINSALES, " & gstrTblSpc &
".TBLCOMPANYINFO.MAXSALES, " & gstrTblSpc & ".TBLCOMPANYINFO.SQUAREFEET,
" & gstrTblSpc & ".TBLCOMPANYINFO.IMPORTS, " & gstrTblSpc &
".TBLCOMPANYINFO.LocalTollNo, " & gstrTblSpc &
".TBLCOMPANYINFO.NATLTOLLNO, " & gstrTblSpc & ".TBLCOMPANYINFO.FAXNO, "
& gstrTblSpc & ".TBLCOMPANYINFO.PRODUCTS, " & _
gstrTblSpc & ".TBLCOMPANYINFO.BRANDS, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTNAME, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTADDR, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTCITY, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTSTATE, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTZIP, " & gstrTblSpc &
".TBLCOMPANYINFO.PARENTTELENO, " & gstrTblSpc & ".TBLCOMPANYINFO.WEBURL,
" & gstrTblSpc & ".TBLCOMPANYINFO.EMAIL, " & gstrTblSpc &
".TBLCOMPANYINFO.TODO " & _
"FROM " & gstrTblSpc & ".TBLCOMPANYINFO " & _
"WHERE (((" & gstrTblSpc & ".TBLCOMPANYINFO.TODO)=-1)) "
& _
"ORDER BY " & gstrTblSpc & ".TBLCOMPANYINFO.COID"
'Debug.Print "sql frmCoToDo"
End Select
'Debug.Print strSQL
Call OpenRecordSetA_ADO(gcnnADOcl, strSQL)
End Sub
Sub OpenRecordSetA_ADO(cnnOpened As ADODB.Connection, _
pstrSQLa As String)
Set grsAcl = New ADODB.Recordset
'Debug.Print pstrSQLa
With grsAcl
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open pstrSQLa, cnnOpened, adOpenDynamic, adLockOptimistic
End With
End Sub
Sub FillContinuousFormOut(pfrm As Form)
Set pfrm.Recordset = grsAcl
End Sub
Sub ChgeParentFormControls(parentForm As Form, intButton As Integer)
Debug.Print "Parent Name: "; parentForm.Name & " Button #: ";
intButton
End Sub
Private Sub Class_Initialize()
Set mobjSubFrmNav = New SubFrmRecdNavigate
Set mobjSubFrmNav.Parent = Me
'Debug.Print "Parent Initialize event to set parent class"
End Sub
Sub Cleanup()
Set mobjSubFrmNav.Parent = Nothing
Set mobjSubFrmNav = Nothing
End Sub
Sub MoveNextRecord(lngRcdNoNext As Long)
'move to next record
'Debug.Print "Move Next Sub / lngRcdNoNext: "; lngRcdNoNext & "
glngRecdCount: " & glngRecdCount
'Debug.Print "Company Name B4: " & grsAcl!CONAME
If lngRcdNoNext + 1 <= glngRecdCount Then
grsAcl.MoveNext
'Debug.Print "Company Name After: " & grsAcl!CONAME
End If
End Sub
Sub MovePreviousRecord(lngRcdNoPrev As Long)
If lngRcdNoPrev - 1 0 Then
grsAcl.MovePrevious
End If
End Sub
Sub MoveFirstRecord()
If Not grsAcl.BOF Then
grsAcl.MoveFirst
End If
End Sub
Sub MoveLastRecord()
If Not grsAcl.EOF Then
grsAcl.MoveLast
End If
End Sub
--------------------------------------------------------------<