Solved Recordset query (1 Viewer)

[nector]

In order to improve the performance of a combobox and not allowing a combobox query to calculate anything I created a view in SQL Server where all the complex calculations are done. Now I want to open a recordset based on the same view since this view will be opened in memory , that means that I will gain in terms of the combobox performance.

The issue is how to link the opened recordset to the combobox, any idea is welcome:

Here is my final procedure and a subform combo box screen shoot for your easy reference. The combobox is called productid:

Dim SQL As String
Dim db As DAO.Database
Dim rs AS DAO.Recordset

SQL = "SELECT DISTINCTROW ViewtblCustomerBiginvSelect.ProductID, ViewtblCustomerBiginvSelect.ProductName, ViewtblCustomerBiginvSelect.BarCode, ViewtblCustomerBiginvSelect.TaxClass, ViewtblCustomerBiginvSelect.Prices, ViewtblCustomerBiginvSelect.RRP, ViewtblCustomerBiginvSelect.VatRate, ViewtblCustomerBiginvSelect.Tourism, ViewtblCustomerBiginvSelect.Insurance, ViewtblCustomerBiginvSelect.TourismLevy, ViewtblCustomerBiginvSelect.TaxInclusive, ViewtblCustomerBiginvSelect.InsuranceRate, ViewtblCustomerBiginvSelect.InsuranceRate AS Premium, ViewtblCustomerBiginvSelect.ExportPrice, ViewtblCustomerBiginvSelect.NoTaxes, ViewtblCustomerBiginvSelect.Sales, ViewtblCustomerBiginvSelect.TurnoverTax, ViewtblCustomerBiginvSelect.Bettings, ViewtblCustomerBiginvSelect.Excise, ViewtblCustomerBiginvSelect.ExciseRate
FROM ViewtblCustomerBiginvSelect
WHERE (((ViewtblCustomerBiginvSelect.Sales)=Yes))
ORDER BY ViewtblCustomerBiginvSelect.ProductID DESC;"

Set rs = db.Openrecordset(SQL,dbOpenDynaset,dbSeeChanges)

Set Me.[sfrmLineDetails Subform].Form.Recordset = rs

rs.Close
Set rs = Nothing
Set db = Nothing

 

[Josef P.]

A combobox also has a recordset property.

What benefit do you expect from using Form.Recordset = DAO.Recordset(Ansi89SqlStringToLinkedView) instead of Form.RecordSource = Ansi89SqlStringToLinkedView?

 

[nector]

A combobox also has a recordset property.

What benefit do you expect from using Form.Recordset = DAO.Recordset(Ansi89SqlStringToLinkedView) instead of Form.RecordSource = Ansi89SqlStringToLinkedView?

I know that but its very slow hence the need to try other methods

 

[Josef P.]

Do I understand you correctly: you want to assign the same recordset to the combo box as to the form?

Maybe like this:

Set me.YourCombobox.Recordset = Me.Recordset.Clone

Note: compare Form.RecordsetClone and Form.Recordset.Clone ... I am not sure if both bring the same behavior.

or like your code with subform:

set Me.[sfrmLineDetails Subform].Form.Recordset = rs
set Me.YourCombobox.Recordset = rs.Clone

 

[arnelgp]

maybe create another Recordset for the combobox:

Dim SQL As String
Dim db As DAO.Database
Dim rs AS DAO.Recordset
Dim rs2 As DAO.Recordset

SQL = "SELECT DISTINCTROW ViewtblCustomerBiginvSelect.ProductID, ViewtblCustomerBiginvSelect.ProductName, ViewtblCustomerBiginvSelect.BarCode, ViewtblCustomerBiginvSelect.TaxClass, ViewtblCustomerBiginvSelect.Prices, ViewtblCustomerBiginvSelect.RRP, ViewtblCustomerBiginvSelect.VatRate, ViewtblCustomerBiginvSelect.Tourism, ViewtblCustomerBiginvSelect.Insurance, ViewtblCustomerBiginvSelect.TourismLevy, ViewtblCustomerBiginvSelect.TaxInclusive, ViewtblCustomerBiginvSelect.InsuranceRate, ViewtblCustomerBiginvSelect.InsuranceRate AS Premium, ViewtblCustomerBiginvSelect.ExportPrice, ViewtblCustomerBiginvSelect.NoTaxes, ViewtblCustomerBiginvSelect.Sales, ViewtblCustomerBiginvSelect.TurnoverTax, ViewtblCustomerBiginvSelect.Bettings, ViewtblCustomerBiginvSelect.Excise, ViewtblCustomerBiginvSelect.ExciseRate
FROM ViewtblCustomerBiginvSelect
WHERE (((ViewtblCustomerBiginvSelect.Sales)=Yes))
ORDER BY ViewtblCustomerBiginvSelect.ProductID DESC;"

Set rs = db.Openrecordset(SQL,dbOpenDynaset,dbSeeChanges)
Set rs2 = db.Openrecordset(SQL,dbOpenSnapshot,dbReadOnly)

Set Me.[sfrmLineDetails Subform].Form.Recordset = rs

Set Me.[sfrmLineDetails Subform].Form!ProductID.Recordset = rs2

rs.Close
Set rs = Nothing
Set db = Nothing

 

[Minty]

As Arnel has suggested you don't need an editable recordset for a lookup combo.

Using a Snapshot will improve performance. The other option if the data doesn't change frequently would be to create a local copy of the view in a temp table and use that as the record source.

We maintain a number of local versions of lookup tables for data that doesn't change very frequently. (Countries, Staff, etc.)
They are refreshed on database load to ensure they are up to date.

 

[nector]

I almost got to work and its very fast except this new error on the "ViewtblCustomerBiginvSelect",It seems it require set an object

Private Sub ProductID_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("ViewtblCustomerBiginvSelect", dbOpenDynaset, dbSeeChanges)

Set Me.[sfrmLineDetails Subform].Form.Recordset = rs

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[nector]

Well as Minty suggetion I think the snapshoot is also very good , I have settled on

 

[ebs17]

Set rs = db.OpenRecordset( ...
Think about it.

 

[arnelgp]

i see your Accounting Solution form.
but too much unrelated info can lead to Confusion?

imo, it's more of a confusion rather than a solution.

 

[nector]

I have tried to change the combobox row source query from Dynaset to Snapshot,but after saving the changes if go back to check again it goes back to Dynaset where do I go wrong?

SELECT tblStaff.StaffID, tblStaff.EmpName, tblStaff.UserRights, ([EmpName]) AS Hstandard, ([EmpName]) AS ProStand
FROM tblStaff
WHERE (((tblStaff.EmpName)<>"Trainer"))
ORDER BY ([EmpName]);

 

[Josef P.]

[maybe OT, but important ]

Set Me.[sfrmLineDetails Subform].Form.Recordset = rs
rs.Close '<-- close the recordset of the form?

 

[Minty]

I have tried to change the combobox row source query from Dynaset to Snapshot,but after saving the changes if go back to check again it goes back to Dynaset where do I go wrong?

Simply save the query set to Snapshot as a named saved query then use that query as the rowsource.

 

[nector]

Now I do agree with Minty 100% , with an earlier post where it was mentioned that for major forms used especially for sales with comboboxes to achieve good performance, then all supporting combos must be from local tables. This is correct period normal changes; I only need to create a passthrough update query to update the a few tables at start up so that they always remain current without the users doing it manually.

Booking out from SQL server for now, but just to concentrate to help others the same way you have been helping me from scratch.

Many thanks to you all it has been a wonderful experience on this site.

Regards

Christopher