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
Wend

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
Wend

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.

http://www.vba-programmer.com

For instance:

http://www.vba-programmer.com/VB_Code/Creating_and_Using_Collections.txt
http://www.vba-programmer.com/VB_Code/URL_from_ShellExecute.txt
http://www.vba-programmer.com/Access_Code/Access_Command_Line_Switches.txt

Matthew.