Check two fields: if they are exist, then cannot enter the same values

[cindyLiu]

Hello Access experts, please help me on this one:

I am creating a Access database for tracking teachers' monthly works. Each teacher allows enter multiple schools for each month, but only one time for each school. For example: teacher selected month for June (drop down list field), and selected school A (drop down list), then filled in data entry fields, and saved it. Later on, this teacher selects month for June, then selects School A again. This teacher should get a message , and let her know that the data for this school this month has already exist, and cannot do it.
For the code, first, create this message, and then don't create a duplicated record on the table.

Thank you,
Cindy

[June7]

Could set a compound index on the two fields which will prevent duplicate pairs and will trigger a message from Access. However, the message might not be clearly understood by user. So alternative is code that looks for the combination. This is data validation and a common question. The real trick is figuring out what event to put the code in. Since you need both values entered to validate, perhaps the form BeforeUpdate event.

A DLookup() could accomplish, something like:

If Not IsNull(DLookup("ID", "tablename", "WorkMonth='" & Me.cbxMonth & "' AND TeacherID=" & Me.cbxTeacher)) Then
MsgBox "message here"
Cancel = True
End If

[cindyLiu]

Thanks, but I don't understand the "ID" in your code. Is it for the row records? Do I need to add an auto id for each row?
Thanks,
Cindy

[June7]

You need some field for which there will always be a value for every record. An autonumber field would serve for that or use another. Just can't be WorkMonth or TeacherID (unless you concatenate them).

Alternative condition:

If DCount("*", "tablename", "WorkMonth='" & Me.cbxMonth & "' AND TeacherID=" & Me.cbxTeacher) > 0 Then

[cindyLiu]

Thank you so much, but the code gives me problem:

I put your code in the drop-down list on the School field, it gave me error on “cbxMonth”.
The name for the "month" field is month, and control source is "month"; and me for the school field is "school", and control source is "school".

Private Sub School_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("RowID", "TBL_DataEntry", "Month='" & Me.cbxMonth & "' AND School=" & Me.cbxSchool)) Then
MsgBox "Sorry, you already have a report for this school in this month"
Cancel = True
End If
End Sub


Cindy

[June7]

Exactly what is the error?

If field is a number type, don't use the apostrophe delimiters.


Month is a reserved word. Should not use reserved words as names.

[cindyLiu]

Thanks. both workmonth and schoolname fields are 'short text'.
The code still gives me error. I just play these both fields and using the BeforeUpdate [Event Procedure]: if I put 'SchoolName' first, then the error is on 'SchoolName'; otherwise, the error is on 'WrokMonth'.

Private Sub schoolName_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("RowID", "TBL_DataEntry", "WorkMonth='" & Me.cbxWorkMonth & "' AND SchoolName=" & Me.cbxSchoolName)) Then
MsgBox "Sorry, you already have a report for this school in this month"
Cancel = True
End If
End Sub

[June7]

If the fields are text then must use apostrophe delimiters on the parameters. Adjust the expression appropriately according to example.

What is the error reported?

[cindyLiu]

error message :"Method or data member not found"

highlight on ".cbxWorkMonth"


Thanks,
Cindy

[CJ_London]

cbxWorkMonth should be the name of your form control

[cindyLiu]

Thanks, no error now. But the code should not allow use to save the record if these two fields: cbxWorkMonth and cbxSchoolName are the same.
Example: cbxWorkMonth=July and cbxSchoolName=A are already in the table, then user try to do it again: cbxWorkMonth=July and cbxSchoolName=A,
then the code should pop up the message which the this month for this school already exists.

Thanks,
Cindy

[CJ_London]

try

If DCount("*", "TBL_DataEntry", "WorkMonth='" & Me.cbxWorkMonth & "' AND SchoolName='" & Me.cbxSchoolName & "'")>0 Then




assumes WorkMonth and SchoolName are the names of the fields in TBL_DataEntry

[cindyLiu]

yes, it works!!!
Thank you so..............much!

right now, the user selected month, then selected the school. since the this month and this school has already existed, then user got the error message. this is what I want:-)
for here, if the user realized the school she select is correct, but month is not, she needs to select the correct month. then how can continue this code?

Thanks,
Cindy

[June7]

Review post 2 again. And the DCount alternative was shown in post 4. You show code in the schoolName BeforeUpdate event.

[cindyLiu]

How to continue for selecting the correct workmonth? I use this code, it works on the first part: I selected workmonth, then I got error message if I selected the school already exist for this month on the table. this part works fine. But I thought as a user, maybe she selected the school was correct, but she selected the wrong WorkMonth. then I tried to selected the different WorkMonth and school stay there. but it didn't allow me to select workmonth and gives me the same error message "Sorry, you already....."

here is the code I use.

Private Sub cbxSchoolName_BeforeUpdate(Cancel As Integer)

If DCount("*", "TBL_DataEntry", "WorkMonth='" & Me.cbxWorkMonth & "' AND SchoolName='" & Me.cbxschoolName & "'") > 0 Then
Cancel = True
MsgBox "Sorry, you already have a report for this school in this month"
End If
End Sub


How do I continue to select WorkMonth without get this error message?

Thanks,
Cindy