Ms-Access

我可以將單個記錄從記錄集中傳遞到模組嗎？

  • July 19, 2018

我正在 MS Access 2016 中創建一個模組 RedemptionEstimate，它需要將 13 個參數傳遞給它，併計算拖欠的財產稅欠款金額。一些參數可以為空，並且將在模組中適當地處理。我嘗試了從查詢中的函式呼叫傳遞所有參數的笨拙路線，但是空值在返回的查詢欄位中的#Error 中使用。

然後我決定閱讀 Recordsets，這似乎是一個更好的解決方案，因為該模組通常會根據查詢從報告和表單中呼叫。我的理解是，記錄集可以由查詢定義，並在報告或表單的事件呼叫中創建。我的計劃是在 OnLoad 事件中創建記錄集，然後使用 Do..Until 解析記錄集，並在循環中呼叫模組。

是否可以只將記錄集中的一條記錄傳遞給模組？如果沒有，我願意接受其他建議。

我考慮過的其他可能的解決方案：

  1. 在原始表中添加一個額外的欄位並重新計算兌換值。- 我不喜歡這個解決方案，因為值每隔幾個月就會改變一次，而且舊系統就是這樣做的，而且讓系統定期重新計算 10,000 條記錄很煩人。似乎也沒有必要。
  2. 是否可以向記錄集添加一個空欄位並將整個記錄集傳遞給模組，然後將計算值添加到該欄位？- 直覺上，這感覺就像我必須在 OnLoad 事件中執行記錄集兩次，一次計算值，第二次將數據“列印”到報告或表單中。

關於記錄集的直接回答：

對於 Access 使用的 Recordset 對象（DOA - Access 使用的預設值，或 ADO），簡短的回答是“否”。遺憾的是，在這些較舊的數據模型中沒有“行”對象的概念，因此您無法獨立於 Recordset 傳遞特定行的所有值。

將整個 Recordset 對象定位到特定記錄後，您就可以傳遞它。函式可以檢索必要的值（通過 Fields 集合），然後保持 Recordset 不變，以便呼叫函式可以繼續循環遍歷所有行。

評論後更新建議

老實說，如果系統被證明足以動態地進行所有計算，我會嘗試消除儲存的計算值。確定這一點的唯一方法是嘗試一下。如果它被證明太慢了（老實說，這可能比目前系統更煩人），那麼我會使用重新計算按鈕等恢復到儲存的計算值。

由於計算的值不依賴於其他數據庫或外部查詢，因此使用您已經編寫的參數化函式應該足夠高效。這種函式的好處是它的靈活性：它可以從另一個函式和方法、從查詢中或從表單控制元素中呼叫。

（僅供參考：考慮額外的數據庫和/或外部查詢很重要，因為如果它依賴於其他查詢服務，這些通常需要適當的連接管理，和/或需要很長時間等。這通常會使解決方案複雜化並且可能證明替代方案的合理性。）

您還可以創建另一個函式，它只獲取給定記錄的主鍵（例如 ID）。然後，它只針對給定的 ID 值直接查詢數據庫表，然後使用從記錄集中檢索到的值呼叫原始函式。當完整記錄不能立即獲得或不方便時，這樣的功能可能很有用。但是要小心，因為在千或記錄的查詢中使用它可能會凍結 Access，因為它會重複打開和關閉每個函式呼叫的記錄集，通常比傳遞參數慢得多。

'* Assume the original function is RedemptionEstimate(p1, p2, ...)
Public Function RedemptionEstimateFromID(vID as Variant)
 If Not IsNumeric(vID) Then
   '* Avoid problems with null (and SQL injection concerns)
   '* Consider other error handling code
   RedemptionEstimateFromID = Null
   Exit Sub
 End If

 Dim db As Database
 Dim rs As Recordset2
 On Error Goto catch

 Set db = CurrentDb '* Not critical for a single recordset, but good habit if performing multiple queries
 Set rs = db.OpenRecordset("SELECT * FROM DataTable WHERE ID=" & vID)
 If rs.EOF Then
   '* Assuming ID is unique primary key, rs has only one record
   '* Call original function, passing values from query as parameters
   RedemptionEstimateFromID = RedemptionEstimateFrom(rs!Col1, rs!Col2, ...)
 Else
   '* ID not found... Consider other error handling code
   RedemptionEstimateFromID = Null
 End If            
 rs.Close '* Would be closed automatically, but good habit to close explicitly

 Exit Function
catch:
 '* ID not found... Consider other error handling code
 Debug.Print "Error " & err.number & ": " & err.Description      
 RedemptionEstimateFromID = Null
End Function

在包含數千條記錄的表單上顯示時，為查詢中的所有記錄計算動態值可能會造成嚴重浪費。相反，可以僅為顯示的記錄計算該值。這樣做的一個缺點可能是這樣的欄位不能用於過濾查詢。要計算目前顯示記錄的值，請將新控制項添加到表單及其Control Source屬性，添加對函式的呼叫，該函式從值作為參數傳遞： = RedemptionEstimate([Col1], [Col2], ...)。

關於替代品：

  1. 實際上，不鼓勵這種方法用於不穩定的值。但是，如果已知值很少改變，它也可能是一種有效的技術。在這種情況下，靜態表欄位比動態重複重新計算值更有效且更易於使用。此技術需要完善且可靠的更新流程和時間表。現在可能看起來很煩人，但如果你可以自動化這樣一個在正常時間之外執行的過程，它仍然是一種合理的方法。考慮回答這些問題，而不是基於“煩惱”因素:)
  • 這些值是否會針對同一計劃中的所有記錄發生變化？
  • 重新計算值是否會動態降低查詢和相關使用者界面的速度？
  • 更新過程能否在影響其他工作流程的時間之外自動執行？
  • 您是否經常在目前流程中遇到特定記錄的過時值？
  • 這種過時的價值觀是否會產生嚴重的負面影響？
  1. 要完全正確地回答這個問題，您首先需要指定表單的要求和行為。您需要更新源表值還是只查看它們？表單會一次只載入幾條記錄還是您提到的全部 10,000 條記錄？計算“RedemptionEstimates”需要多長時間——對於單行，對於整個記錄集？

創建臨時表是一種可行的替代方法，但如果您計劃將值更新回原始表，則必須確保定義了正確的索引和關係等。最壞的情況是您需要編寫額外的程式碼來更新表。但我會等到您回答其他問題後再推薦詳細資訊。

其他的建議：

將值從查詢傳遞到函式是一種合法的技術。也許 13 個參數很多，你正在探索可能性是件好事。但是，您應該能夠通過正確的 null 處理和調試來解決 #Error 值。不要放棄這種技術，因為它可能被證明是最好的解決方案。

有時需要顯式循環遍歷記錄集的每一行，但不要陷入循環。正確的技術是將記錄源綁定到表單或報表。無需循環並明確“列印”表單上的每條記錄。Access 旨在為您完成此類任務。如果您為表單（或報表）指定行源，表單將自動打開查詢並在後台創建自己的記錄集。

（我不鼓勵顯式創建一個記錄集，然後自己將其綁定到表單。Access 在技術上允許這樣做，但它有問題。嘗試在現有記錄集中創建和更新一個空欄位也會使事情複雜化，我認為需要 ADO 記錄集而不是預設值DAO。換句話說，我建議不要走這條路。）

在 CPerkins 回復之前，我提出了以下解決方案，但我仍然遇到以下問題：

  1. 該查詢旨在詢問贖回日期，但出現錯誤。我相信 OnLoad 事件會在詢問贖回日期之前呼叫該模組。我只是通過獲取模組中的目前日期而忽略了這個問題，但這需要解決。建議？
  2. 所有記錄的未綁定文本框在循環遍歷每條記錄時填充相同的數據。我不知道如何將文本框綁定到函式結果，或者是否有可能。
  3. 我還將從一個將翻閱所有記錄的表單中呼叫它，我不確定如何僅將目前記錄發送到模組。

下面是我的程式碼，我擺弄了格式，但我剛剛完成了一周。對不起。

$$ code $$

Private Sub Report_Load()

Dim dbsTrustee As DAO.Database
Dim rstInventory As DAO.Recordset
Dim varRedeemRecord As Variant
Dim curRedemptionEstimate As Currency

Set dbsTrustee = CurrentDb
Set rstInventory = dbsTrustee.OpenRecordset("InventoryDetailQuery", dbOpenSnapshot)

Do Until rstInventory.EOF
   varRedeemRecord = rstInventory.GetRows(1)
   curRedemptionEstimate = RedemptionEstimate(varRedeemRecord)
   Me.txtRedemptionAmt = curRedemptionEstimate
Loop

結束子

$$ /code $$

Function NumMonths(StartDate As Date, EndDate As Date, ExpiredDate As Date) As Long

'-------------------------------------
' Test for redemption after expiration
'-------------------------------------

If DateDiff("d", EndDate, ExpiredDate) >= 0 Then
   ' DateDiff determines number of whole months that have passed
   ' Second half of expression determines if partial month then adds to NumMonths
   NumMonths = DateDiff("m", StartDate, EndDate) + (Day(StartDate) >= Day(EndDate))
Else
   ' Set date to expiration if redemption after expire date
   NumMonths = DateDiff("m", StartDate, ExpiredDate) + (Day(StartDate) >= Day(ExpiredDate))
End If

結束功能

函式 RedemptionEstimate(RedeemRecord As Variant) 作為貨幣

' For values from passed recordset
Dim FaceAmount As Currency
Dim DateSold As Date
Dim CertRate As Single
Dim Sub1Amount As Currency
Dim Sub1TempDate As Variant 'Variant to handle null date values
Dim Sub1Rate As Single
Dim Sub2Amount As Currency
Dim Sub2TempDate As Variant 'Variant to handle null date values
Dim Sub2Rate As Single
Dim Sub3Amount As Currency
Dim Sub3TempDate As Variant 'Variant to handle null date values
Dim Sub3Rate As Single
Dim ExpTempDate As Variant 'Variant to handle null date values

' Storage for dates after testing for null
Dim Sub1Date As Date
Dim Sub2Date As Date
Dim Sub3Date As Date
Dim ExpDate As Date

' Calculated values
Dim RedeemDate As Date
Dim CertNumMonths As Long
Dim CertIntervals As Integer
Dim CertPenalty As Currency
Dim Sub1NumMonths As Long
Dim Sub1Intervals As Integer
Dim Sub1Penalty As Currency
Dim Sub2NumMonths As Long
Dim Sub2Intervals As Integer
Dim Sub2Penalty As Currency
Dim Sub3NumMonths As Long
Dim Sub3Intervals As Integer
Dim Sub3Penalty As Currency

' Assignment of passed values
FaceAmount = RedeemRecord(3, 0)
DateSold = RedeemRecord(4, 0)
CertRate = RedeemRecord(5, 0)
Sub1Amount = RedeemRecord(6, 0)
Sub1TempDate = RedeemRecord(7, 0)
' Test for null date and assign default value
If (Not IsNull(Sub1TempDate)) Then
   Sub1Date = CDate(Sub1TempDate)
Else
   Sub1Date = CDate("1/1/1901")
End If
Sub1Rate = RedeemRecord(8, 0)
Sub2Amount = RedeemRecord(9, 0)
Sub2TempDate = RedeemRecord(10, 0)
' Test for null date and assign default value
If (Not IsNull(Sub2TempDate)) Then
   Sub2Date = CDate(Sub2TempDate)
Else
   Sub2Date = CDate("1/1/1901")
End If
Sub2Rate = RedeemRecord(11, 0)
Sub3Amount = RedeemRecord(12, 0)
Sub3TempDate = RedeemRecord(13, 0)
' Test for null date and assign default value
If (Not IsNull(Sub3TempDate)) Then
   Sub3Date = CDate(Sub3TempDate)
Else
   Sub3Date = CDate("1/1/1901")
End If
Sub3Rate = RedeemRecord(14, 0)
ExpTempDate = RedeemRecord(15, 0)
' Test for null date and assign default value
If (Not IsNull(ExpTempDate)) Then
   ExpDate = CDate(ExpTempDate)
Else
   ExpDate = CDate("12/12/2099")
End If

RedeemDate = Date

' Calculate number of rollovers since purchase and round up
CertNumMonths = NumMonths(DateSold, RedeemDate, ExpDate)
CertIntervals = -Int(-((CertNumMonths + 1) / 6))

' Test for rollovers within normal range
If (CertIntervals <= 6) And (CertIntervals >= 1) Then
   CertPenalty = FaceAmount * CertRate * CertIntervals
' Handles cases that exceed upper bound
ElseIf (CertIntervals > 6) Then
   CertPenalty = FaceAmount * CertRate * 6
Else
   ' Test for redemption on date of sale
   If (DateSold = RedeemDate) Then
       CertPenalty = FaceAmount * CertRate * 1
   Else
       ' Handles errors cases that exceed lower bound
       CertPenalty = 0
   End If
End If

' Check for purchased sub
If (Sub1Amount > 0) Then
   ' Calculate number of rollovers since purchase and round up
   Sub1NumMonths = NumMonths(Sub1Date, RedeemDate, ExpDate)
   Sub1Intervals = -Int(-((Sub1NumMonths + 1) / 12))
   ' Test for rollovers within normal range
   If (Sub1Intervals <= 3) And (Sub1Intervals >= 1) Then
       Sub1Penalty = Sub1Amount * Sub1Rate * Sub1Intervals
   ' Handles cases that exceed upper bound
   ElseIf (Sub1Intervals > 3) Then
       Sub1Penalty = Sub1Amount * Sub1Rate * 3
   Else
       ' Test for redemption on date of sale
       If (Sub1Date = RedeemDate) Then
           Sub1Penalty = Sub1Amount * Sub1Rate * 1
       Else
           ' Handles errors cases that exceed lower bound
           Sub1Penalty = 0
       End If
   End If
Else
   Sub1Penalty = 0 ' No sub purchased
End If


' Check for purchased sub
If (Sub2Amount > 0) Then
   ' Calculate number of rollovers since purchase and round up
   Sub2NumMonths = NumMonths(Sub2Date, RedeemDate, ExpDate)
   Sub2Intervals = -Int(-((Sub2NumMonths + 1) / 12))
   ' Test for rollovers within normal range
   If (Sub2Intervals <= 2) And (Sub2Intervals >= 1) Then
       Sub2Penalty = Sub2Amount * Sub2Rate * Sub2Intervals
   ' Handles cases that exceed upper bound
   ElseIf (Sub2Intervals > 2) Then
       Sub2Penalty = Sub2Amount * Sub2Rate * 2
   Else
       ' Test for redemption on date of sale
       If (Sub2Date = RedeemDate) Then
           Sub2Penalty = Sub2Amount * Sub2Rate * 1
       Else
           ' Handles errors cases that exceed lower bound
           Sub2Penalty = 0
       End If
   End If
Else
   Sub2Penalty = 0 ' No sub purchased
End If
' Check for purchased sub
If (Sub3Amount > 0) Then
   ' Calculate number of rollovers since purchase and round up
   Sub3NumMonths = NumMonths(Sub3Date, RedeemDate, ExpDate)
   Sub3Intervals = -Int(-((Sub3NumMonths + 1) / 12))
   ' Test for rollovers within normal range
   If (Sub3Intervals = 1) Then
       Sub3Penalty = Sub3Amount * Sub3Rate * Sub3Intervals
   ' Handles cases that exceed upper bound
   ElseIf (Sub3Intervals > 1) Then
       Sub3Penalty = Sub3Amount * Sub3Rate * 1
   Else
       ' Test for redemption on date of sale
       If (Sub3Date = RedeemDate) Then
           Sub3Penalty = Sub3Amount * Sub3Rate * 1
       Else
           ' Handles errors cases that exceed lower bound
           Sub3Penalty = 0
       End If
   End If
Else
   Sub3Penalty = 0 ' No sub purchased
End If

RedemptionEstimate = CertPenalty + Sub1Penalty + Sub2Penalty + Sub3Penalty

結束功能

$$ /code $$

引用自：https://dba.stackexchange.com/questions/183130

相關問答

Ms-Access

選擇未完成所有培訓的員工

  • August 26, 2022
檢視問答
Ms-Access

尋找基於文件的數據庫引擎替代 MS Access

  • August 24, 2022
檢視問答
Sql-Server

SQL 數據庫與多個 Microsoft Access 數據庫

  • July 16, 2022
檢視問答
Sql-Server

SQL 數據庫與多訪問數據庫

  • July 16, 2022
檢視問答
Database-Design

使用聚合訪問 Web 應用程序查詢 3 個表 - 需要一種解決方法

  • March 1, 2022
檢視問答
Database-Design

MS Access 和多個使用者

  • February 23, 2022
檢視問答