HowTo extract Recordset from Sub Form into recroedset object

[Rx_]

Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectlly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record dependeing on the network traffic through a small pipe.

The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.

The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.

Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource


Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value

Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229

 

[spikepl]

There are 48 fields that need validation - is there a way to reference the entire recordset?

I've read and re-read you post and still am confused. Are you speaking of an entire record? So what prevents you reading that?

Or a recordset containing how many records? What prevents you reading that? If you want to fiddle with data you could also have a detached ADO recordset.

Besides, how can you speak of validation on read-only data?

 

[Rx_]

I should have mentioned the Subform only has 0 or 1 record! Sorry.
The subform is read-only. The code will validate a field and let the user know to open up the web app and fix the data on Oracle before importing it. The Oracle app has little to no field validation.


After some more searching, this came up:
Set Rst=Form_frmStatments_subform.Recordset.Clone()

Dim Rst As DAO.Recordset
Set Rst = Form_SubformName.RecordsetClone
 Rst.MoveFirst
   Do While Not Rst.EOF()
    With Rst
      'Do something
       .MoveNext
    End With
 Loop

Have not tried this just yet, must catch a train.
The Form_SubformName.RecordsetClone doesn't show up with Intelesense. Will be back Monday to look further into it.

RE: an entire record? So what prevents you reading that?
LOL, my lack of experience in using subforms!

I would normally just create a temp recorset object and re-run the Select statement shown above to populate the temp recordset. The 3 to 6 second delay is what I am trying to avoid.

 

[spikepl]

DO not use this format:

Form_SubformName.RecordsetClone

It opens hidden forms etc and gets people into deep doodoo. To refer to recordset on subform use the conventional reference:

Forms!MyForm!Subformcontrol.Form.RecordsetClone

 

[Rx_]

Perfect! Exactly what I was looking for.
So much documentation out there did not lead me to this. THANKS!

Set RScloneSHLBHL = Forms!frmsr_NewWellEntry!fsubsrNavSHLBHL.Form.RecordsetClone
If RScloneSHLBHL.RecordCount > 0 Then
' code to validate Oracle data before migrating it into SQL Server