16 December 2005

VBA: Reading from a recordset in an already loaded form

You can read directly from a recordset by using:

Call recordset.MoveFirst
While Not recordset.EOF
Debug.Print ("id=" & recordset!InstalmentRecID)
Call recordset.MoveNext

The movenext etc will actually make the selected item on the screen move.
To avoid this, you can use:

Set clone = recR.clone

Call clone.MoveFirst

While Not clone.EOF
Debug.Print ("id=" & clone!InstalmentRecID)
Call clone.MoveNext

Set clone = Nothing

which means that nothing is moved, and its faster.

09 December 2005

Creation Object Pattern in VBA

In clsNameClass:

Private m_name As String

Public Function getName() As String
getName = m_name
End Function

Public Sub setName(name As String)
m_name = name
End Sub

In CreationObject module:

Public Function newClsName(name As String) As clsNameClass
Set newClsName = New clsNameClass
Call newClsName.setName(name)
End Function

Then use like:

Dim fred As clsNameClass
Set fred = newClsName("heyho")
Debug.Print (fred.getName())

with this pattern, you almost get constructors in VB.

Useful snippets for VB/VBA programmers

This is a useful site containing snippets of code for VB & VBA programmers.


For instance: