Help with Access VBA Loop to prevent duplicates

[Jenaccess]

Hi,

I’m building a form for one of my program databases (Access 2010). The business rules I’d like to set up are:

A student can be enrolled in a program, exit the program and return at a later date, i.e.:

Name --------- EntryDate----- ExitDate
John Smith ---9/1/2010 -----8/31/2011
John Smith ---12/1/2011
John Smith ---7/1/2010 -----8/1/2010


However, a student under no circumstances should have a record that looks like this:

Name---------- EntryDate -----ExitDate
John Smith ---12/19/2011
John Smith----9/1/2011

This sort of thing was able to happen in my last version of the database. Now that I’m slightly more savvy, I’m trying to implement some code that will prevent stuff like this, as I think it’s a data integrity issue.

My setup now is that the user clicks on a button to add a record, and the button opens a form. The user enters the Program name and entry date. Then they click the “Add record button”. What I’d like this button to do is go through the records that are in qryRstDuplicateProgram, and if there are records with the same student and program name and a blank exit date, or an exit date that is either null or is not greater than the new entry date the user is trying to enter, the record will be denied and a message box will let the user know that the record already exists. I tried to open a recordset and loop through, but anytime the ProgramID and StudentID match, the code generates a “Program exists for the student” error, even if the user is entering a date after the exit date, or before the entry date. If someone could please let me know where I’m going wrong, it would be much appreciated!

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * From qryRstDuplicateProgram WHERE StudentID=" & Me.StudentID)


Do While Not rst.EOF

If Me.StudentID = rst!StudentID And Me.ProgramID = rst!ProgramID And Me.txtEntryNumber < rst!ExitNumber Then
MsgBox "Program exists for this student."
Me.Undo
End If

rst.MoveNext
Loop

 

[MarkK]

I commonly use DCount() to check for the existence of records satisfying certain criteria. Looping through all the records returned by a query to count a condition seems laborious.
Consider...

   Dim criteria As String
   criteria = _
      "StudentID = " & Me.StudentID & " " & _
      "AND (ExitDate Is Null OR ExitDate > #" & YourTestDate & "#)"

   If DCount("*", "YourTable", criteria) > 0 Then
      MsgBox "Program exists for this student."
      Me.Undo
   End If

... which constructs criteria and then counts the records satisfying that condition. If there are any don't add more.
Cheers,
Mark

 

[abzalali]

Dear Sir,
I'm using Access 2013. I want to check duplicate data. I have a form where from insert data into table but before insertion I want check multiple criteria with form. I just trying with this way:

Field are:
BatchID
BillNum
CIH
IH

back end form code is:

Private Sub cmdCheck_Click()
Dim criteria As String
criteria = "[BatchID]=" & Me.cboBatchID & " AND [BillNum]=" & Me.txtBillNum & " AND [CIH]=" & Me.txtCIH & " AND [IH]=" & Me.txtIH & ""

If Nz(Me.cboRound, 0) <= 0 Then
MsgBox "Please select Round", vbExclamation, "Round"
Me.cboRound.SetFocus

ElseIf IsNull(Me.cboBatchID) Or Me.cboBatchID = "" Then
MsgBox "Please select BatchID", vbExclamation, "BatchID"
Me.cboBatchID.SetFocus

ElseIf Nz(Me.txtBillNum, 0) <= 0 Then
MsgBox "Please select Bill Number", vbExclamation, "Bill Number"
Me.txtBillNum.SetFocus

ElseIf Nz(Me.txtCIH, 0) <= 0 Then
MsgBox "Please enter Cumulative Invoice Hour", vbExclamation, "CIH"
Me.txtCIH.SetFocus

ElseIf Nz(Me.txtIH, 0) <= 0 Then
MsgBox "Please enter Invoice Hour", vbExclamation, "IH"
Me.txtIH.SetFocus

ElseIf DCount("*", "tblVerificationNum", criteria) > 0 Then
MsgBox "This number of Bill already verified", vbExclamation, "Bill Number"
Else
Me.txtAmountTSP.SetFocus
End If


End Sub

Finally I'm getting this error: Run Time Error 3075 Syntax Error Missing Operator in qry Expr. Please help me.

 

[MarkK]

It's considered bad form to post the same question in two or more places. Maybe I answer it here, someone else answers it there and you've just wasted the resources of this forum. Do you think that getting your answer is more important than someone else's?