opening database connections and recordsets efficently

[locutus]

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

[Wokawidget]

Instead of txt boxes, why not load the options from the database into a drop down combo???

Woka

[alex_read]

If you write the app in Access itself using the vba forms, the database itself is always open (you can use the CurrentDB method call) and call on it's tables directly whic'll probably be more efficient if you can do it this way...

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.

I'd connect to the database probably in the form_activate & store any records & column information I need into a dynamic array which is declared at the form or project level. Any checks of the textbox data can then be performed against the array so as not needing to connect to the database & it's tables again....

[locutus]

can you tell me the difference between the form load and the form activate event , this is something I am not to clear on either.

Thanks for all the assistance
Rgds, Locutus

[TheBionicOrange]

Form Load only processes once (when a form is loaded). (Form.Activate processes immediately after this)

Form Activate processes every time a form.Show is processed.
This will only be once if you unload the form each time, but if you do a form.Hide it stays in memory, so consequently a form.show will make it appear (and ONLY run the form.activate).

[alex_read]

can you tell me the difference between the form load and the form activate event , this is something I am not to clear on either.

Form Load - the form is loaded into memory so it can process events

Form Activate - occurs after the form is painted on the screen so the users can see it, and is more or less like the setfocus event of a textbox....

[Wokawidget]

If the form is Open, and you select another form, then click back onto the orginal form, the the Active Event is fired. I have never used this event, ever. I always use Form_Load.

Woka

[alex_read]

That's okay Woka, but I'd always put this line in forst then:

Me.visible = true

If you're connecting to a database etc, sometimes it can be slow on the formload & the user can sometimes end up with a pure white window box, or a blank screen whilst the starting event code is working away ...

[Wokawidget]

Why?

I have a sub in a module:

Public Sub LoadUser(MyVal plngUserID As Long)
Dim frmNew   As frmUser
   Screen.MousePointer = vbHourglass
   Set frmNew = New frmUser
   Load frmNew 'here all the combos and stuff are loaded
   frmNew.UserID = plngUserID 'This is when its populated with the user data (this is a property) If the ID = 0 Then this indicates a NEW USER
   frmNew.Show
   Set frmNew = Nothing
   Screen.MousePointer = vbDefault
End Sub

I NEVER EVER put Me.Visible = True in the Form_Load event...
Where do I get the white screen with the code above???

Woka

[Wokawidget]

This means that ANYWHERE in my app if I have ANY user data that is being displayed I can right click then click Edit on the menu, and then run that sub...

Woka