代码之家 › 专栏 › 技术社区 › Ben.Name

gotorecord工作正常,但返回该记录值的方法最简单

goto median ms-access-2013 access-vba

Ben.Name · 技术社区 · 7 年前

部分问题是打开记录集的表,并且必须将焦点设置到子窗体。我通过vba对表格排序失败。目标是找到表的中值,从而进行排序。

Private Sub cboUser_AfterUpdate()
Dim sourceReset As String
Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset

sourceReset = sbf.SourceObject  '<--Is Table.TEMPtable btw.
Me.sbf.SourceObject = ""
Forms!frm.Requery
Forms!frm.Refresh


'Create new TEMPtable
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryTEMPtable"   '<--Is sorted here as desired
    DoCmd.SetWarnings True

Set dbMedian = CurrentDb()
Set rsMedian = dbMedian.OpenRecordset("TEMPtable") '<--Gets unsorted here

sbf.SourceObject = sourceReset
Me.OrderBy = "NetWrkDays ASC" '<--Re-sorting, but on subform, which.. is 
fine if I can return the column value later.
Forms!frm.Refresh

Me.[sbf].SetFocus

Records= DCount("[ColA]", "TEMPtable")

'Even number of records
If Records - 2 * Int(Records / 2) = 0 Then
    MEDrcd = Records / 2
    DoCmd.GoToRecord , , acGoTo, MEDrcd '<-Can see value in debug, how to 
assign it to a useful variable???
    ''''Me.CurrentRecord ("NetWrkDays")
    ''''Me.RecordSource ("NetWrkDays")
    Me.txtMED = rsMedian("NetWrkDays")
End If

'Odd number of records
If Records - 2 * Int(Records / 2) = 1 Then
    MEDrcd1 = (Records - 1) / 2
    MEDrcd2 = (Records + 1) / 2
    DoCmd.GoToRecord acDataForm, "TempTable", acGoTo, MEDrcd1
    MED1 = rsMedian("NetWrkDays")
    DoCmd.GoToRecord acDataForm, "TempTable", acGoTo, MEDrcd2
    MED2 = rsMedian("NetWrkDays")
    Me.txtMED = (MED1 + MED2) / 2
End If

我想如果你不能在那一点上返回值的话,尝试使用docmd.gotorecord是没有意义的。

移动到记录后返回值的最佳/正确方法是什么?
由于数据表和表是相同的,我只是运行与设置的重点,因为我所说的是有问题的排序表在VBA。虽然我使用rsmedian是没有意义的,因为表rs永远不会移动…但是我无法使用gotorecord检索移动子窗体的值。
我要在这里兜圈子,我希望不要弄混了才明白。谢谢您。

0 回复 | 直到 7 年前

Ben.Name 7 年前

联机找到此方法。在其他人发现自己处于类似的情况下工作得很好。

Private Sub cboUser_AfterUpdate()
Dim sourceReset As String, sqlMED As String, sTable As String, sField As String
Dim j As Integer, varVal As Single
Dim rs As DAO.Recordset

sourceReset = sbf.SourceObject
Me.sbf.SourceObject = ""
Forms!frmSTATS.Requery
Forms!frmSTATS.Refresh

   'Create new TEMPtable table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryTEMPtable"
    DoCmd.SetWarnings True

'Get MEDIAN Data/Value
sTable = "TEMPtable"
sField = "NetWrkDays"
sqlMED = "SELECT " & sField & " from " & sTable & " WHERE " & sField & ">0 Order by " & sField & ";"
Set rs = CurrentDb.OpenRecordset(sqlMED)
    rs.MoveLast
        j = rs.RecordCount
    rs.Move -Int(j / 2)

If j Mod 2 = 1 Then 'odd number of elements
   getMedian = rs(sField)
Else                'even number of elements
   varVal = rs(sField)
   rs.MoveNext
   varVal = varVal + rs(sField)
        getMedian = varVal / 2
End If
Me.txtAnswer = getMedian
rs.Close

sbf.SourceObject = sourceReset
Me.OrderBy = "NetWrkDays ASC"
Forms!frmSTATS.Refresh

结束子