عدم تكرار قيمة بشروط Dcount

الرد على أولاً :)

Private Sub IDDox_R_AfterUpdate()

'save the Record
DoCmd.RunCommand acCmdSaveRecord

'requery the ComboBox, to avoid getting empty cells
Me.IDDox_R.Requery
End Sub

'========================================================

Private Sub IDDox_R_GotFocus()

'requery the ComboBox, to account for new entered data in the Table by others
Me.IDDox_R.Requery
End Sub

'========================================================

Public Function Test_Data(iJob_id, iDoxID)
On Error GoTo err_Test_Data

    'we need certain data to process the Formula's and compare data,
    'so we will make the Row Source query, of the ComboBox IDDox_R, send us these data,
    '1st data to receive is Job_ID, which the query takes from the Sub Form's Parent Form, [Forms]![DoxR_O]![Job_ID]
    '  it's easier to take the data from the Main Form, to avoid getting into The Main Form calling the Sub Form Controls
    '  The following link gives ALL the required code for callings between:
    '  The Main Form A, Sub Form B, and Sub Form C inside Sub Form B
    '  http://access.mvps.org/access/forms/frm0031.htm
    '2nd data DoxID, from the query itself
    
    'the Row Source query:
    'Get_Data: Test_Data([Forms]![DoxR_O]![Job_ID],[DoxID])
    '  we made an Alias name (Dummy name) in the query named it Get_Data,
    '  asked it to call the Function Test_Data (which we are in it now),
    '  send 2 pieces of data to the Function (because the Function is asking for 2 pieces of data)
    
    'Although we can call the 2 pieces of data any names we like, to make it easy on me, I usually add an "i" to the name,
    '  so I don't have to go back and Form and table to get the right names :)
    
    'How it works:
    'The query will generate the list of ALL data of the selected field IDDox_R,
    '  but we need to loop through the Table DocumentR_O to compare the IDDox_R, Job_ID, and Reference_No
    '  so each data we get from the query, it will be formulated, and compared with Table DocumentR_O fields.
    '  the best way to loop through Table DocumentR_O, is to do it through Recordset
    
    
    Dim rst As DAO.Recordset
    
    'open the Recordset, constraining the data to Job_ID
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM DocumentR_O WHERE [Job_ID]= '" & iJob_id & "'")
    'to reset the Recordset cursor, we have to move to the end of the Recordset, then move back to the begining
    rst.MoveLast: rst.MoveFirst

    'Loop through the Recordset, fro the 1st Record to its last RecordCount
    For i = 1 To rst.RecordCount
        'initialize the variable Use_This to nothing,
        'as we will assign the unique Record to it
        Use_This = ""
        
        'check and intercept the data that don't match our need,
        'IF Receipt_No is Empty, and DoxID from the query is matching IDDox_R in the Table
        If IsNull(rst!Receipt_No) = True And DoxID = rst!IDDox_R Then
        
            'we cannot use this DoxID, so keep Use_This variable empty
            Use_This = ""
            
            'and we don't need this DoxID, so don't continue checking with the rest of Table data,
            'move to the next query data by bypassing the rest of the data checking,
            'so we use GoTo the line the bypasses this checking,
            'which if after rst.MoveNext, which moves to the next table Record
            ' Debug.Print iJob_id & ">" & rst!SR & " - we CAN'T use: " & iDoxID
            GoTo Move_to_Next_Record
        End If
            
            'since the un-wanted DoxID are intercepted and not assigned to Use_This variable
            'the data the gets here is the correct required data
            Use_This = iDoxID
            
        'move to the next Record in Table DocumentR_O
        rst.MoveNext
        
Move_to_Next_Record:
    'move to the next Record in the Query
    Next i
    
    ' just make sure that USe_This is NOT empty
    If Len(Use_This & "") <> 0 Then
        
        'now we send back the correct DoxID to the Combobox Row Source query, one at a time
        'Debug.Print iJob_id & " - we CAN use: " & Use_This & "> Len=" & Len(Use_This)
        Test_Data = Use_This
    End If
    
'NOTE:
    '  since the Combobox Row Source query will list ALL the data from Table Documents,
    '  and this Function will NOT send the unqualified DoxID,
    '  this will leave an Empty Record in the Get_Data field in the quey:
    'Get_Data: Test_Data([Forms]![DoxR_O]![Job_ID],[DoxID])
    '  so to remove these Empty cells, we have to put in the Criteria: IS NOT NULL
    '  this way Get_Data will hide the Empty Records, and ONLY show DoxID we sent from this Function
    
    
    'clean up, to release the Recordset from the memory
    rst.Close
    Set rst = Nothing
    
Exit Function

err_Test_Data:

    MsgBox Err.Number & vbCrLf & Err.Description
    
End Function

وللحديث بقية :)

جعفر

10.Dcounter.zip