0

I am new to ms access, I have prepared a database for a school where I want to apply duplicate entry restrictions.

I want to stop duplicate entry in same month. Type of studentID is number while the month is linked with another table where its type is text. I have tried many options but type mismatch error occurs. The data is being entered from a form and I need to apply validation at the time of form update

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim x As Integer

x = DCount("StudentID", "tblFeeVoucherGenerate", "StudentID =" & Me.StudentID & " ")
If x > 0 Then
 MsgBox "Fee Already Recorded for this month"
 Me.Undo
 Exit Sub
End If
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Record has been Saved"
DoCmd.RunCommand acCmdRecordsGoToNew

End Sub

I want to add month condition to record a student fee once in a month.

June7
  • 19,874
  • 8
  • 24
  • 34
Shakir
  • 1
  • 1
    I am afraid that your question in the actual state is not answerable because we don't have enough details. For instance you refer to a month and we don't see anything of that in your code. – Thomas G Aug 15 '20 at 09:16

2 Answers2

1

Text field parameters need apostrophe delimiters (date/time uses # character). Consider:

x = DCount("StudentID", "tblFeeVoucherGenerate", "StudentID =" & Me.StudentID & _
     " AND MonthFieldName='" & Me.tbxMonth & "'")

Month is a reserved word - advise not to use it as field name.

June7
  • 19,874
  • 8
  • 24
  • 34
  • x = DCount("ID", "tblFeeVoucherGenerate", "StudentID =" & Me.StudentID & _ " AND [FeeMonthID]='" & Me.FeeMonth & "'") – Shakir Aug 16 '20 at 10:07
  • Now I received error, "Data type mismatch in criteria expression, error code 3464 – Shakir Aug 16 '20 at 10:08
  • Example has correct syntax for data types you indicated. If FeeMonthID is really a text field, should not error. – June7 Aug 16 '20 at 15:48
1

DCount function does not cross tables and can be slow if table is big. Consider making a query to join the tables named qryFeeVoucherGenerate

SELECT tblFreeVoucherGenerated.StudentID AS StudentID, 
tblWithNameOfDateField.NameOfDateFiled AS NameOfDateField FROM tblFreeVoucherGenerated
LEFT JOIN
tblWithNameOfDateField 
ON tblFreeVoucherGenerated.StudentID = tblWithNameOfDateField.StudentID
WHERE DATEDIFF( 'm' , tblWithNameOfDateField.DonationDate, Date()) = 0;

Save the above query with name qryFeeVoucherGenerated then use:

 Private Sub StudentID_AfterUpdate()

'If you decide to create a textbox for the DateField the use:
'DCount("*", "[qryFeeVoucherGenerate]", "[StudentID] = '" & Me.StudentID.Value & "' AND CDATE([NameOfDateField]) = '" & Me.NameOfMonthFiled.Value & "'") > 0

 If DCount("[StudentID]", "[qryFeeVoucherGenerate]", "[StudentID] = '" & Me.StudentID.Value & "'") > 0 Then

 MsgBox "Fee Already Recorded for this month"
 Me.Undo
 Exit Sub
 End If

 MsgBox "Record has been Saved"
 DoCmd.RunCommand acCmdRecordsGoToNew

 End Sub
Fil
  • 469
  • 3
  • 12