Data provider could not be initiated

[tekkyun]

I have a subform where its recordsource property is switched to a temp recordset variable when the main form is opened.
I now need to amend the main form so that the subform recordsource is reset to sql if certain options are selected by the user on the main form. My code is bombing out when I try and reset the recordsource property as follows:

Set frm = Forms("FrmName")
Set ctl = frm.Controls("FrmName")
sql = "SELECT Tbl.......etc etc"
ctl.Form.RecordSource = sql

The error number is run-time error 31 (data provider could not be initiated).

I have tested the sql statement and it works fine. It is just a simple selection of records from an existing table in the local database
I seem to need another statement to re-establish the link between the form and the original data source.

Any ideas?

 

[TheAceMan1]

How are ya tekkyun . . .

. . . and this:

[blue]   Dim prp As property, SQL As String
   
   Set prp = Forms![purple][b][i]FormName[/i][/b][/purple].Properties("RecordSource")
   
   prp = "SELECT Tbl.......etc etc"
   
   Set prp = Nothing[/blue]

See Ya! . . . . . .

 

[tekkyun]

Thanks Aceman but still get same error where I set prp=sql

I now have
Dim frm as form
Dim ctl as control
Dim prp as property
Dim sql as string

Set frm = Forms("FrmName")
Set ctl = frm.Controls("subFrmName")
sql = "SELECT Tbl.......etc etc"
Set prp = ctl.Form.Properties("Recordsource")
prp = sql

 

[TheAceMan1]

tekkyun . . .

In my code you substitute [purple]FormName[/purple] with [blue]the actual name of your form![/blue]

See Ya! . . . . . .

 

[tekkyun]

Aceman,
I tried that but get runtime error 2450 (Microsoft access can't find the form.....)
The form in question is a subform currently open within main form when this code runs. That's why I thought that I needed to reference it as per my code above.

 

[TheAceMan1]

[blue]The form in question is a [purple]subform[/purple] . . .[/blue]

Somewhere I lost focus on the [purple]subform[/purple]. Try this (assumes code is running in mainform):

[blue]   Dim prp As Property, SQL As String
   
   Set prp = [[purple][b][i]subFormName[/i][/b][/purple]].Form.Properties("RecordSource")
   
   SQL = "SELECT Tbl.......etc etc"
   prp = SQL
   
   Set prp = Nothing[/blue]

See Ya! . . . . . .

 

[tekkyun]

Thanks AceMan
... just tried that but now back to original problem, run-time error 31 at line prp=sql.

 

[TheAceMan1]

tekkyun . . .

Post the SQL . . .

See Ya! . . . . . .

 

[Remou]

tekkyun
Are you sure you are using the name of the subform control, rather than the name of the subform contained by the control? They are often the same, but not always.

 

[tekkyun]

Sorry for the delay in replying guys, but out of the office yesterday,

The name of subform control is same as the name of the subform.

Here's the sql (runs when user makes selection on main form):-

Private Sub OOPIndicatorSet_Click()
Dim frm As Form
Dim ctl As Control
Dim SQL As String
Dim prp As Property

Set frm = Forms("FrmViewWeeklyDataArrearsToMRP")
Set ctl = frm.Controls("FrmViewWeeklyDataArrearsToMRPByBuyer")

SQL = "SELECT TblArrearsToMRPSummary.Buyer, TblArrearsToMRPSummary.Name, TblArrearsToMRPSummary.CountNoArrsOrders AS CountOrders, TblArrearsToMRPSummary.TickNoArrs AS Tick, TblArrearsToMRPSummary.CountArrsOrders, TblArrearsToMRPSummary.TickArrs, TblArrearsToMRPSummary.CountCancellationOrders, TblArrearsToMRPSummary.TickCancellations, TblArrearsToMRPSummary.CountST20Orders, TblArrearsToMRPSummary.TickST20s " & _
"FROM TblArrearsToMRPSummary " & _
"WHERE (((TblArrearsToMRPSummary.CurrentWeek) = '" & [Forms]![Frm_menu]![selectWeek] & "') And ((TblArrearsToMRPSummary.OOPIndicator) = " & [Forms]![FrmviewWeeklyDataArrearsToMRP]![OOPIndicatorSet] & "))" & _
"GROUP BY TblArrearsToMRPSummary.Buyer, TblArrearsToMRPSummary.Name, TblArrearsToMRPSummary.CountNoArrsOrders, TblArrearsToMRPSummary.TickNoArrs, TblArrearsToMRPSummary.CountArrsOrders, TblArrearsToMRPSummary.TickArrs, TblArrearsToMRPSummary.CountCancellationOrders, TblArrearsToMRPSummary.TickCancellations, TblArrearsToMRPSummary.CountST20Orders, TblArrearsToMRPSummary.TickST20s;"

Set prp = [FrmViewWeeklyDataArrearsToMRPByBuyer].Form.Properties("Recordsource")

prp = SQL

Set prp = Nothing

Set frm = Nothing

End sub

Also, the recordsource for the subform is switched to temp recordset when the main form is opened (works fine).
The temp recordset is defined as global variable (Global RsSt20 As ADODB.Recordset)
Here's a section of sql for the on-open event of main form:-

Private Sub Form_Open(Cancel As Integer)
Dim frm as form
Dim ctl as Control

Set frm = Forms("FrmViewWeeklyDataArrearsToMRP")

Set ctl = frm.Controls("FrmViewWeeklyDataArrearsToMRPByBuyer")

'Assign temp RsSt20 recordset to subform recordset
Set ctl.Form.Recordset = RsSt20

ctl.Form.refresh

End sub

Hope this gives you guys some clues. Thanks

 

[Remou]

I reckon you are on very shaky ground using a global variable for your recordset. You do not seem to have tested to see if it was still functioning. Have a look at RoyVidar's post in this thread:

General VBA programming advice for users
thread707-1323753

 

[tekkyun]

Think you're on to something Remou....
I have just done following checks:-

1. Take out code line to use temp recordset, Set Ctl.Form.Recordset=RsSt20. Code worked fine.
2. Put code line back in and checked functioning of RsSt20 at error line prp=sql by debug.printing field data. All ok.

So recordset seems to be functioning properly AND is causing problem.

Can you tell me how to use temp recordset properly in this situation?
Thanks

 

[Remou]

I have gone through the whole thing in a mock-up, except for the global variable, and it works for me. Try this, for the sake of testing:

Private Sub OOPIndicatorSet_Click()
Dim frm As Form
Dim ctl As Control
Dim SQL As String
Dim prp As Property

Set frm = Forms("FrmViewWeeklyDataArrearsToMRP")
Set ctl = frm.Controls("FrmViewWeeklyDataArrearsToMRPByBuyer")

SQL = "SELECT * From TblArrearsToMRPSummary"
ctl.Form.RecordSource = SQL

Set frm = Nothing

End sub

The error you got was quite odd, so I wonder if it was misleading.

Microsoft, on binding recordsets:

http://support.microsoft.com/kb/281998/EN-US/

 

[tekkyun]

Same error. Its definitely the global variable that's the problem....just need to know how to fix it.

I'll have a read of microsoft article.

Thanks

 

[tekkyun]

.....article isn't much help I think. The problem I have is not binding the form to ADO recordset (that bit works fine), but un-binding the form from the ADO recordset and re-connecting it to sql data source.