Expression On Load Event produced an error:

[miscluce]

A Problem occured while mS Access was communicating with the OLE server or Active X Control. I cant see why I have this problem??
It looks like the error is in my sql string but that same string works in another program.

my code:

Option Compare Database
Option Explicit

Dim rsContacts As ADODB.Recordset
Dim cnDb As ADODB.Connection
Dim strConnection As String


Private Sub Form_Load()
strConnection = "Provider=sqloledb;Data Source=MISFS;" & "Integrated Security=SSPI;Initial Catalog=Sample"

'create a new connection instance and open it using the connection string
Set cnDb = New ADODB.Connection
cnDb.Open strConnection

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset

'set various properties of the recordset
With rsContacts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tbl table using the existing connection
.Open "SELECT * FROM Contacts", cnDb
'disconnect the recor.dset
.ActiveConnection = Nothing
'sort the recordset
.Sort = "ContactID"
End With

'if the recordset is empty
If rsContacts.BOF And rsContacts.EOF Then
Exit Sub
Else
'move to the first record
rsContacts.MoveFirst
'populate the controls on the form
Call PopulateControlsOnForm
End If

'close the database connection and release it from memory
cnDb.Close
Set cnDb = Nothing

End Sub

Sub PopulateControlsOnForm()

'Populate the controls on the form with the values of the
'current record in the local disconnected recordset.
'Use the same field Contacts as the tblContacts table from
'which it was generated.
If Not rsContacts.BOF And Not rsContacts.EOF Then
Me.txtFullName = rsContacts!Name
Me.txtCompany = rsContacts!Company
Me.txtTitle = rsContacts!Title
Me.txtEMail = rsContacts!E_Mail
Me.txtAddress = rsContacts!Address
Me.txtCity = rsContacts!City
Me.txtZip = rsContacts!Zip_Code
Me.txtPhone = rsContacts!Phone
Me.txtFax = rsContacts!Fax
Me.txtCell = rsContacts!Cell
Me.txtState = rsContacts!State
Me.txtNotes = rsContacts!Notes
Me.txtCountry = rsContacts!Country

ElseIf rsContacts.BOF Then
'past beginning of recordset so move to next record
rsContacts.MoveNext
ElseIf rsContacts.EOF Then
'past end of recordset so move back to previous record
rsContacts.MovePrevious
End If
End Sub

I also have MS Active X Data object library 2.8 reference running.

 

[JerryKlmns]

miscluce

The only available CursorType for a client side Cursor Location is , adOpenStatic. If you don't provide it, it is the default so you could dump it.

Sorting of the recordset should be done using on the sql statement at the Source property, for faster results.

If the just opened recordset is empty you don't just slum the door on his face. Close it and destroy it 'cause it has feelings and it 's gonna hurt you! Your exiting here should cancel the loading of the form? Maybe you should keep loading and handle differently the empty recordset.

ADODB Recordsets when opened are always positioned at the first record.

When sub PopulateControlsOnForm finishes you slum the door on recordset 's face.

And are you 100% sure that all fields are not empty? If you do this
Me.txtAddress = rsContacts!Address & ""
you won't have to worry about.