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:
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?
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:
Code:
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?
Code:
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