Hi
I am writing a small application that will use a database ( msaccess ) I have a couple of forms and a code module.
I am trying to figure out the best place and way to open database connections and recordsets.

In the code module I have this small sub:

Public Sub SetDatabase()

'set BBTsystem.mdb as the database to be used
strAppDatabase = App.Path & "\BBT.mdb"
Set dbsBBT = Workspaces(0).OpenDatabase(strAppDatabase)

End Sub

Now in my form on the form activate event ( or should I use the form load event ? ) I have this call to the module sub:

Private Sub Form_Activate()
SetDatabase ******
TxtMaterial.SetFocus
TxtMaterial.Text = sScanData
TxtContainer.Enabled = False
TxtPcode.Enabled = False

End Sub

This will set the connection to the database.
When one of my text boxes changes or loses focus I want to go to the database table and check if the contents of the text box are valid against the table. So obviously I want to use a recordset.

Heres my code in the txtbox change event ( this data is feed straight into the textbox by a barcode scanner BTW )

Private Sub TxtMaterial_Change()
Dim rsMaterial As Recordset

If Len(TxtMaterial.Text) = 10 Then

Set rsMaterial = dbsBBT.OpenRecordset("MasterMaterial", dbOpenTable)

rsMaterial.Index = "Materialcode"
rsMaterial.Seek "=", sScanData
If rsMaterial.NoMatch Then
MsgBox "not here m8"
End If

MsgBox rsMaterial("MaterialCode")
MsgBox "move on "
TxtContainer.SetFocus
Else
MsgBox " the string aint long enough matey"
End If
End Sub

This form has ten lots of textboxes which I will eventually make in a control array , so I dont think I want to create this recordset ten times making my code wrong. Where should I create the recordset once and keep open for the life of the form ? should this be done on the form activate or in the code module then presumable after the recordset is created once I can keep using it.

Hope you can advise , Regards, Locutus