sync recordset in subfrm

[DTSFreak]

Hi,

Can anyone help me with setting a recordset in a subform?

I've this code where i use the connection method to connect to a sql server
database. I can use it to set the recordset in a form and a subform, but
when i go the next record in the main form the subform's recordset stays the
same.

Here is the code:

Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

With rst
.Source = "SELECT * FROM MyTable1"
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.ActiveConnection = GetConnection(True)
.Open
End With

Set Me.Recordset = rst ' this is for the main form

Set rst = Nothing

End Sub

for the subform it goes somewhat the same. Anyone knows the problem?

 

[VBslammer]

Are you updating the recordsource of the subform in the parent form's Form_Current() event?


VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[DTSFreak]

HI,

No i did it on the open event. Should it be on the current event, because i also tried that but it didn't work. Maybe i did something wrong.

 

[VBslammer]

I would set the main form on Form_Load() and the subform on Form_Current(). The subform should have a key field that links to the main form's primary key. If the parent form is at a new record, the subform will not contain any records, so I would check for that:

Private Sub Form_Current()
  If Me.NewRecord Then
    Call UpdateSubform(0)
  Else
    Call UpdateSubform(Me![PrimaryField])
  End If
End Sub

Private Sub UpdateSubform(ByVal KeyFieldValue As Long)
  Dim strSQL
  
  strSQL = "SELECT * FROM tblDetails WHERE [IDfield]=" & KeyFieldValue
  
  [green]' update the subform's recordset...[/green]

End Sub

If the key field is a string the code would need to change somewhat, like [tt]"... WHERE [IDfield]='" & KeyFieldValue & "'"[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[DTSFreak]

Hi,

Thanks for the code, it works. But how do i use it for inserting? If i insert a record i'm getting errors or sometimes a vb crash.