So disconnected means recordset can only be connected in same rout

Hello,

I am struggling to understand disconnected recordsets.

I have a form that opens and using VBA, I am creating a recordset.

Great.

Then while in another sub routine, I open another form for data entry. I've
tried to reconnect to the disconnected recordset and it doesn't work.

So I've set the recordset (rsGuestData) to a new recordset

set rsGuestData = NEW ADODB.RECORDSET

1) open that recordset
2) Populate fields

With rsGuestData

.Source = mySQL
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Options:=adCmdText
.MoveLast
rsGuestCount = .Bookmark

End With

With frmKeyLeads

If Len(rsGuestData!firstname) > 0 Then
.txtFirstName.Text = rsGuestData!firstname
End If

If Len(rsGuestData!lastname) > 0 Then
.txtLastName.Text = rsGuestData!lastname
End If

If Len(rsGuestData!telephone) > 0 Then
.txtTelephone.Text = CStr(rsGuestData!telephone)
End If


Question

1) Why do I have to create a new recordset?
2) Is it that you can't reference a disconnected recordset from a different
procedure/sub routine?

I appreciate any help on this.

Tony

1) You have to create a new recordset because it's basically a container
for whatever you need it at a moment. Your question is akin to asking,
"Why do I need to create a form?" Maybe I'm not understanding.

2) You didn't specify where you declared the rsGuestData. You have to be
mindful of the scope a variable has. If you use a Dim statement inside a
routine, then it goes out of scope at end of routine. If you declare it
at module level, then its scope is more longer. In case of code behind
of form, it's alive as long as the form is open then goes out of scope
once the form closes. Most of times, if not all times, recordsets that
are intended to be used as a form's recordsource ought to be declared at
module level.

3) It should be noted that you would use several events to drive the
whole process. For example, you'd use form's OnLoad to instantiate the
recordset which you already did, but you would then refer to form's
BeforeUpdate event to validate records and you would also need to use
form's Close event to re-connect the recordset and fire UpdateBatch
method to re-synchronize the changes made.


Webtechie wrote:
> Hello,
>
> I am struggling to understand disconnected recordsets.
>
> I have a form that opens and using VBA, I am creating a recordset.
>
> Great.
>
> Then while in another sub routine, I open another form for data entry. I've
> tried to reconnect to the disconnected recordset and it doesn't work.
>
> So I've set the recordset (rsGuestData) to a new recordset
>
> set rsGuestData = NEW ADODB.RECORDSET
>
> 1) open that recordset
> 2) Populate fields
>
> With rsGuestData
>
> .Source = mySQL
> .CursorLocation = adUseClient
> .CursorType = adOpenDynamic
> .LockType = adLockOptimistic
> .Open Options:=adCmdText
> .MoveLast
> rsGuestCount = .Bookmark
>
> End With
>
> With frmKeyLeads
>
> If Len(rsGuestData!firstname) > 0 Then
> .txtFirstName.Text = rsGuestData!firstname
> End If
>
> If Len(rsGuestData!lastname) > 0 Then
> .txtLastName.Text = rsGuestData!lastname
> End If
>
> If Len(rsGuestData!telephone) > 0 Then
> .txtTelephone.Text = CStr(rsGuestData!telephone)
> End If
>
>
> Question
>
> 1) Why do I have to create a new recordset?
> 2) Is it that you can't reference a disconnected recordset from a different
> procedure/sub routine?
>
> I appreciate any help on this.
>
> Tony
>
>
>