判斷重復(fù)值

huanghyd

在排單窗體輸入排單批次值PlanBath時，對記錄進行檢查，判斷同一天同一臺機不能重復(fù)PlanBath值的輸入。下面的代碼放在PlanBath 更新后事件里。發(fā)現(xiàn)會出錯，有時候不能檢查出同一天同一臺機已經(jīng)輸入同樣的PlanBath值，沒有反應(yīng)。請高手幫忙看看那里的問題？謝謝！

排單順序： PB01 => PB02 => PB03 => PB04   同一天DyelotDate同一臺機MachineName不能重復(fù)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 "系統(tǒng)檢測到當天" & Me.MachineName.Value & " 機臺的這個" & Me.PlanBath.Value & " 已經(jīng)存在，" & "請核對后重新輸入新的PlanBath ", vbInformation, "提示重復(fù)"
       Me.PlanBath = Null
       Me.PlanBath.SetFocus
    Exit Sub
  Else
    End If
  End If
rs.MoveNext
Loop
End Sub

smilingkiss

在do while語句之前試試加上：rs.movefirst
還有，最好在函數(shù)結(jié)束前關(guān)閉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 "系統(tǒng)檢測到當天" & Me.MachineName.Value & " 機臺的這個" & Me.PlanBath.Value & " 已經(jīng)存在，" & "請核對后重新輸入新的PlanBath ", vbInformation, "提示重復(fù)"
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 "系統(tǒng)檢測到當天" & Me.MachineName.Value & " 機臺的這個" & Me.PlanBath.Value & " 已經(jīng)存在，" & "請核對后重新輸入新的PlanBath ", vbInformation, "提示重復(fù)"
end if
rs.close
set rs=nothing

huanghyd

先謝謝 smilingkiss 和 todaynew 。我改用你們的代碼測試發(fā)現(xiàn)也不行。同一天同一機臺已經(jīng)輸入這個PlanBath值了，第一次輸入PlanBath值可以判斷出來重復(fù)，再次重復(fù)輸入后不會判斷，沒反應(yīng)。請大家再幫忙看看。圖片如下：

Henry D. Sy

建議在更新前判斷

huanghyd

也先謝謝 Henry D. Sy。剛剛我把上面三段代碼分別放在更新前事件里測試，結(jié)果也不行。問題同樣。請大家再幫忙看看。謝謝！{:soso_e100:}

smilingkiss

huanghyd 發(fā)表于 2013-11-28 13:33
先謝謝 smilingkiss 和 todaynew 。我改用你們的代碼測試發(fā)現(xiàn)也不行。同一天同一機臺已經(jīng)輸入這個PlanBath ...

剛才重新看過代碼，是更新后的，應(yīng)該是沒問題的呀本人水平有限，還是等等高手解答

smilingkiss

或者做個相似的例子上傳上來看看

todaynew

1、寫一個自定義函數(shù)
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)  '數(shù)據(jù)為日期類型
    b = b And IsNull(MachineName_Ctrl.Value) = False '數(shù)據(jù)不為null
    b = b And IsNull(PlanBath_Ctrl.Value) = False  '數(shù)據(jù)不為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、假設(shè)有一個用于新增的按鈕名曰：btnInsert，在控件DyelotDate、MachineName和PlanBath的更新后事件中均如下代碼：
        '調(diào)用自定義函數(shù)
        me.btnInsert.Enabled=btnEnabled(me.DyelotDate,me.MachineName,me.PlanBath)

smilingkiss

todaynew 發(fā)表于 2013-11-29 11:01
1、寫一個自定義函數(shù)
Function btnEnabled(ByVal DyelotDate_Ctrl As Control, ByVal MachineName_Ctrl As ...

明白了！��！