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

[CJ_London]

you need the same code in the cbxWorkmonth before update control

[cindyLiu]

Hello All,

I still have a question for the code, could you take a look #15?

Thanks,
Cindy

[CJ_London]

every time you try to leave the cbxschoolName control, you will trigger the cbxschoolName beforeupdate event. Suspect you also need an cbxschoolName.undo to go with your cancel=true so the beforeupdate event is not triggered

[cindyLiu]

Thanks. But it didn't work. it didn't allow me to do anything when I click the WorkMonth combo box and always pop-up the same error message which I put in the code.

Thanks,
Cindy

[cindyLiu]

Thank you so much, Ajax! I follow your #16 and #18, and the code is working!!!!!!!!!!!!
Cindy

[CJ_London]

regret I don't have time to play around with different scenarios. Suggest comment out the 'Cancel=true' line

Ahh- see my original suggestions have worked

[cindyLiu]

Thanks, you are great!
Cindy

[cindyLiu]

Got a syntax error when I selected a school, name likes this "xxxxx's elementray school", could you tell how to fix it?

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

Thanks,
Cindy

[CJ_London]

you need to use the replace function to replace the single quote with two single quotes


....AND SchoolName='" & replace(Me.cbxschoolName,"'","''") & "'")....

[cindyLiu]

Thanks, I changed the code for Private Sub cbxSchoolName_BeforeUpdate(Cancel As Integer), and when I selected a schools, then it works.
then, I thought I should also changed the code for Private Sub cbxWorkMonth_BeforeUpdate(Cancel As Integer), but I got an error when I tried to select a month first : "Run-time error '94', Invalid use of null".

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

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

[CJ_London]

sounds like yo do not have a value for schoolname - you need to use the nz function

Replace(nz(Me.cbxschoolName,""), "'", "''")

[cindyLiu]

Thank you so much, Ajax. SUPER!
Cindy