MS Access:使用 VBA 验证表中的数据 [英] MS Access: Validate data in tables using VBA

问题描述

好吧，我在另一个帖子里问过这个问题，但它变得太乱了......

Ok, I asked this question in another post but it became too messy...

这是我想要做的...

首先，有一个叫做 Age 的表，用户可以在其中输入年龄范围，表中可以输入多个年龄范围......表中的字段名称为MinAge"和MaxAge".

First, there is a table called Age where the user enters age ranges, and there can be multiple age ranges entered into the table... In field names in the table are "MinAge" and "MaxAge".

我有一个切换按钮，用户可以通过它验证数据以确保四件事:

I have a toggle button whereby the user validates the data to ensure four things:

1. 表中没有任何空字段
2. 每行的 MaxAge 大于 MinAge
3. 某行的 MaxAge 大于前一行的 Min 年龄...
4. 一行的 Min Age 比下一行的 Max Age 依次增加一个

一些例子

- 最小年龄 最大年龄第 1 行:0 5第 2 行:6 8

通过，因为每行的最小年龄 > 大于最大年龄，第 2 行的最小年龄大于第 1 行的最大年龄，并且第 2 行的最小年龄依次比第 1 行的最大年龄大 1.

PASS because Min age is > than Max age for each row, Min age on row 2 is > than max age on row 1, and Min age on row 2 is sequentially greater by 1 than the max age on row 1.

          Min Age    Max Age
   ROW 1:               5
   ROW 2:   6           

失败，因为第 1 行和第 2 行中有空值

FAIL because there are null values in row 1 and 2

          Min Age    Max Age
   ROW 1:   10           5
   ROW 2:   6            8

失败，因为第 1 行中的最小年龄大于最大年龄

FAIL because the Min Age is greater than the Max age in row 1

         Min Age    Max Age
   ROW 1:   0            5
   ROW 2:   4            8

失败，因为第 2 行的最小年龄小于第 1 行的最大年龄......

FAIL because the Min Age on Row 2 is less than the Max age on row 1.....

推荐答案

我会在一个数组中处理它.过去似乎很简单

I would handle it within an array. Seems pretty straight forward past that

Dim i as long, j as long, z as long q as long
Dim arr As Variant
Dim arr2() As String, strMsg as string
Dmi rs as recordset
Dmim errBool as Boolean

set rs = CurrentDb.OpenRecordset("your query statement")
With rs
    rs.MoveLast
    q = rs.RecordCount
    rs.MoveFirst
    z = rs.Fields.Count
End With

ReDim xaRR(q, z)  
arr = rs.GetRows(q)

For j = LBound(arr, 2) To UBound(arr, 2)
    For i = LBound(arr, 1) To UBound(arr, 1)
        arr2(j, i) = arr(i, j)
    Next i
Next j

errBool = True
for i = lbound(arr2,1) to ubound(arr2,1)
    if i > 0
        If arr2(i,0)= arr2(i-1,1)+1 then 
            errBool = false
            strMsg = "Start point isnt an increment by 1 of the last position"
        end if 
        if (IsNull(arr2(i,0)) Or (IsNull(arr2(i,0))) = True Then 
            errBool = false
            strMsg = "You have null values"
        End if
        If arr2(i,0) > arr2(i,1) then 
            errBool = False
            strMsg = "You min is larger than your max for row " & i
        End
    end if
next i

if errBool = False then
    MsgBox strMsg
End if 

rs.Close
Set rs = Nothing

这篇关于MS Access:使用 VBA 验证表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！