判断重复值

huanghyd

在排单窗体输入排单批次值PlanBath时，对记录进行检查，判断同一天同一台机不能重复PlanBath值的输入。下面的代码放在PlanBath 更新后事件里。发现会出错，有时候不能检查出同一天同一台机已经输入同样的PlanBath值，没有反应。请高手帮忙看看那里的问题？谢谢！

排单顺序： PB01 => PB02 => PB03 => PB04   同一天DyelotDate同一台机MachineName不能重复PlanBath值的输入。

Private Sub PlanBath_AfterUpdate()
Dim rs As New ADODB.Recordset
rs.Open "SELECT Tbl_DyeHistory.DyelotDate, Tbl_DyeHistory.MachineName, Tbl_DyeHistory.PlanBath FROM Tbl_DyeHistory WHERE Tbl_DyeHistory.DyelotDate=# " & Me.DyelotDate.Value & " # ", CurrentProject.Connection, adOpenStatic, adLockReadOnly
Do While Not rs.EOF
  If rs.RecordCount > 0 Then
    If Me.MachineName.Value = rs("MachineName") And Me.PlanBath.Value = rs("PlanBath") Then
       MsgBox "系统检测到当天" & Me.MachineName.Value & " 机台的这个" & Me.PlanBath.Value & " 已经存在，" & "请核对后重新输入新的PlanBath ", vbInformation, "提示重复"
       Me.PlanBath = Null
       Me.PlanBath.SetFocus
    Exit Sub
  Else
    End If
  End If
rs.MoveNext
Loop
End Sub

smilingkiss

在do while语句之前试试加上：rs.movefirst
还有，最好在函数结束前关闭rs

todaynew

dim str as string
str="DyelotDate=#" & Me.DyelotDate.Value & "#"
str=str & " and MachineName='" & Me.MachineName.Value & "'"
str=str & " and PlanBath=" & Me.PlanBath.Value
if Dcount("*","Tbl_DyeHistory",str)>0 then
    MsgBox "系统检测到当天" & Me.MachineName.Value & " 机台的这个" & Me.PlanBath.Value & " 已经存在，" & "请核对后重新输入新的PlanBath ", vbInformation, "提示重复"
end if

或
dim ssql as string
Dim rs As New ADODB.Recordset
ssql="SELECT * FROM Tbl_DyeHistory WHERE "
ssql=ssql & "DyelotDate=#" & Me.DyelotDate.Value & "#"
ssql=ssql & " and MachineName='" & Me.MachineName.Value & "'"
ssql=ssql & " and PlanBath=" & Me.PlanBath.Value
rs.Open ssql,CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
    MsgBox "系统检测到当天" & Me.MachineName.Value & " 机台的这个" & Me.PlanBath.Value & " 已经存在，" & "请核对后重新输入新的PlanBath ", vbInformation, "提示重复"
end if
rs.close
set rs=nothing

huanghyd

先谢谢 smilingkiss 和 todaynew 。我改用你们的代码测试发现也不行。同一天同一机台已经输入这个PlanBath值了，第一次输入PlanBath值可以判断出来重复，再次重复输入后不会判断，没反应。请大家再帮忙看看。图片如下：

Henry D. Sy

建议在更新前判断

huanghyd

也先谢谢 Henry D. Sy。刚刚我把上面三段代码分别放在更新前事件里测试，结果也不行。问题同样。请大家再帮忙看看。谢谢！{:soso_e100:}

smilingkiss

huanghyd 发表于 2013-11-28 13:33
先谢谢 smilingkiss 和 todaynew 。我改用你们的代码测试发现也不行。同一天同一机台已经输入这个PlanBath ...

刚才重新看过代码，是更新后的，应该是没问题的呀本人水平有限，还是等等高手解答

smilingkiss

或者做个相似的例子上传上来看看

todaynew

1、写一个自定义函数
Function btnEnabled(ByVal DyelotDate_Ctrl As Control, ByVal MachineName_Ctrl As Control, ByVal PlanBath_Ctrl As Control) As Boolean
    Dim ssql As String
    Dim rs As New ADODB.Recordset
    Dim b As Boolean
    b = IsDate(DyelotDate_Ctrl.Value)  '数据为日期类型
    b = b And IsNull(MachineName_Ctrl.Value) = False '数据不为null
    b = b And IsNull(PlanBath_Ctrl.Value) = False  '数据不为null
    If b = True Then
        ssql = "SELECT * FROM Tbl_DyeHistory WHERE "
        ssql = ssql & "DyelotDate=#" & DyelotDate_Ctrl.Value & "#"
        ssql = ssql & " and MachineName='" & MachineName_Ctrl.Value & "'"
        ssql = ssql & " and PlanBath=" & PlanBath_Ctrl.Value
        rs.Open ssql, CurrentProject.Connection, adOpenStatic, adLockReadOnly

        b =b and rs.RecordCount = 0 '不存在记录

        rs.Close
    End If
    Set rs = Nothing

    btnEnabled = b
End Function

2、假设有一个用于新增的按钮名曰：btnInsert，在控件DyelotDate、MachineName和PlanBath的更新后事件中均如下代码：
        '调用自定义函数
        me.btnInsert.Enabled=btnEnabled(me.DyelotDate,me.MachineName,me.PlanBath)

smilingkiss

todaynew 发表于 2013-11-29 11:01
1、写一个自定义函数
Function btnEnabled(ByVal DyelotDate_Ctrl As Control, ByVal MachineName_Ctrl As ...

明白了！！！