OPening Record Source in Ms Access VBA (1 Viewer)

[nector]

I thought this was going to be easy for me, but it has proved challenging.

Background

I am working with the cloud database AZURE now to improve the performance with the data entry forms I was advised to get rid of all domain functions such Dlookup. The current Dlookups which I'm using to get some data from the Customers table are reproduced below and I need this to be replaced by the record set to improve performance:

Me.BuyerTPIN = DLookup("TPIN", "tblCustomers", "CustomerID =" & Me.CustomerID)
Me.BuyerName = DLookup("Company", "tblCustomers", "CustomerID =" & Me.CustomerID)
Me.BuyerTaxAccountName = DLookup("Company", "tblCustomers", "CustomerID =" & Me.CustomerID)
Me.BuyerAddress = DLookup("Address", "tblCustomers", "CustomerID =" & Me.CustomerID)
Me.BuyerTel = DLookup("Phone", "tblCustomers", "CustomerID =" & Me.CustomerID)

Now the new record set I'm struggling to implement looks like below, I have compiled it no error, but it does not work! Can someone help to fix it?

Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Rst As DAO.Recordset
Dim Details As Variant
Dim sql As String
Dim Z As Integer
Set db = CurrentDb
sql = "SELECT * FROM tblCustomers WHERE CustomerID= Me.CustomerID"
Set Rst = CurrentDb.OpenRecordset(sql)
Set Rs = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)

'Process data.
    Z = 1
    For Each Details In Rst
        Rs.AddNew
        Rs![BuyerTPIN] = Details("TPIN")
        Rs![BuyerName] = Details("Company")
        Rs![BuyerTaxAccountName] = Details("Company")
        Rs![BuyerAddress] = Details("Address")
        Rs![BuyerTel] = Details("Phone")
        Rs.Update
        Z = Z + 1
    Next

    Rs.Close
    Set Rs = Nothing
    Set db = Nothing
    Set Rst = Nothing
    Set Details = Nothing

 

[Minty]

Does not work means what?

Also, you appear to be saving a load of details that could simply be linked from the Customer table?
If they need saving with the Invoice at that point, why not bring those details into the form as hidden columns from the customer combo drop down and simply refer to them from there?

 

[nector]

The code does not pull data and save in the customer invoice header. Remember the idea here is to reduce human data entry by the users if some of these obvious controls can be captured automatically. The benefit of automatic data entries are many:

(1) Eliminates omission and human errors
(2) Fast data entry
(3) Reliability of the software

 

[Minty]

I get all that, but you haven't answered the question.
What doesn't work?

We use Azure SQL all the time as a backend for clients, and the odd Dlookup to populate something isn't going to be that bad, however, if it's not saving it I assume it is just for display? If so see my comment about the Combo. Pull the fields into the combo and simply set the control source of the other textboxes to the relevant hidden columns.

 

[nector]

Ok Done!

 

[arnelgp]

sql = "SELECT * FROM tblCustomers WHERE CustomerID= Me.CustomerID"

you forget to concatenate on the above:

should be:

sql = "SELECT * FROM tblCustomers WHERE CustomerID= " & Me.CustomerID

 

[MajP]

The current Dlookups which I'm using to get some data from the Customers table are reproduced below and I need this to be replaced by the record set to improve performance:

replacing a handful of dlookups with a recordset is not going to improve performance. If you have performance issues this is not the issue.
The big problems with domain aggregate functions is when people use them inside queries instead of a subquery or other sql solution. That creates a massive performance hit. They cannot use indices and run for each record.

For Each Details In Rst

What is that supposed to do? That does nothing. What is the Z counter doing? What do you think this code is doing? The code makes zero sense to me.

 

[Pat Hartman]

Using 5 domain functions rather than a join to the main table means that Access needs to run 6 queries instead of 1. That is not more efficient.

Using an update loop in VBA is always slower than using an action query to insert new rows.

Duplicating data is almost alway a poor solution. Your code loop seems to looping through a recordset to append rows to a different table but it is not getting values from the "source" recordset so maybe that is why the code doesn't work. If you want to copy an order, then use an append query that has two argument, one to select using the from parentID and the second to provide the to parentID as the FK.

 

[nector]

May be here is there is a bit of misunderstanding, Kindly follow through here:

(1) All the forms load fast with Azure SQL database, it's like working within access
(2) This was noticed after replacing all domain function that were attached to the parent/child forms in FE and switching off the Auto rename function
(3) The use DSN less file goes with the necessary link to whatever PC the FE is installed, and not need to relink, that makes the login fast
(4) The reports reasonably are fast including the heavily unionized income statement & balance sheet

Problem area

(1) data entry takes too much time (Example 8 seconds to appear on a form per entry) to be committed to the server or saved there that is my concern, its ok to who are using big invoices where it's not extremely busy. However, for supermarket business then this can never be entertained as the ques will go to infinite.

It's not even the net issue here, I do not know the real issue.

 

[Minty]

Do the individual records need to be saved/loaded in real-time or would the end of the batch be sufficient?
You could write them locally to a local temp table, then save/upload them to the main linked table in one go at the end of the session/batch.

 

[cheekybuddha]

I think you are iterating your recordset incorrectly:

' ...
'Process data.
    Z = 1
    For Each Details In Rst
' ...

Instead, try it like:

' ...
Set Rst = CurrentDb.OpenRecordset(sql)
Set Rs = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)

'Process data.
    Z = 1
    Do While Not Rst.EOF
        Rs.AddNew
        Rs![BuyerTPIN] = Rst("TPIN")
        Rs![BuyerName] = Rst("Company")
        Rs![BuyerTaxAccountName] = Rst("Company")
        Rs![BuyerAddress] = Rst("Address")
        Rs![BuyerTel] = Rst("Phone")
        Rs.Update
        Z = Z + 1
    Loop
' ...

 

[nector]

Thank you so much cheekybuddha this has sorted out problem

 

[cheekybuddha]

I'm glad this solves the immediate problem, but please do consider Pat's post #8.

Duplicating data from one table to another is often an alarm of inefficient schema design.

You probably ought to just store the CustomerID as a foreign key in tblCustomerInvoice and then you have linked access to all those details.

 

[nector]

All is fine without the record set but after removing unused control the speed for the forms works no problem. Whether binded to tables or not the key here is indexes