How to make a sub-form based on disconnected recordset and update/modify data?

[Khalid_Afridi]

Dear All,
Good Day

Can anybody tell me “How to make a sub-form based on disconnected recordset and update/modify data?”

I have written the following code to get data on main form with the disconnected recordset:

‘*************************************’
Option Compare Database
Option Explicit
Dim rsContracts As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err

'call the function to open the database
Call OpenDB

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

'set various properties of the recordset
With rsContracts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblContacts table using the existing connection
.Open "SELECT * FROM tblContracts", con
'disconnect the recordset
.ActiveConnection = Nothing
End With

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

If Not rsContracts.BOF And Not rsContracts.EOF Then
ElseIf rsContracts.BOF Then
'past begining of recordset so move to next record
rsContracts.MoveNext
ElseIf rsContracts.BOF Then
'past end of recordset so move to next record
rsContracts.MovePrevious
End If

End If

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

Err_Exit:
Exit Sub
Err:
MsgBox Err.Description
Resume Err_Exit
End Sub

‘*************************************’
This works fine and I have all the functionality for delete/update/movenext/moveprevious etc. which I have coded for each button on the form and call the procedures

My Problem is:

I have another subform on the main form which has related disconnected records. I have Set Me.Recordset = rsItems (disconnected recordset) and link child fields and link master fields properties set to my primary and foreign keys on it, but it don’t give me the desire results.

How can I filter my disconnected records on my sub-form and update back them to the database with adLockBatchOptimistic cursor type property opening the connection.


Best regards

Khalid

 

[Khalid_Afridi]

Mr. Pat Hartman

Thank you for your reply, I have my database on network and more than 100 users are using it at a time, I have distributed on the network with the back-end database (My tables) and user Forms/Queries/Linked Tables (MDE file)

Each user has its own copy of MDE file in his pc with the linked database connection. It is continuous linked database file, although I have coded it to close the MDE file automatically if it is not in use for 20 minutes, but still it is causing a network traffic load when all the users are using it simultaneously with the opened linked tables connections.

Can you please give me a code how to bind a disconnected record on my main form with my continues sub-form (with disconnected recordset) and how to update/amend/delete and add the records with adLockBatchOptimistic cursor type property?

Khalid

 

[afromanam]

http://www.ozgrid.com/forum/showthread.php?t=48566