you need the same code in the cbxWorkmonth before update control
you need the same code in the cbxWorkmonth before update control
Hello All,
I still have a question for the code, could you take a look #15?
Thanks,
Cindy
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
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
Thank you so much, Ajax! I follow your #16 and #18, and the code is working!!!!!!!!!!!!
Cindy
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
Thanks, you are great!
Cindy
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
you need to use the replace function to replace the single quote with two single quotes
....AND SchoolName='" & replace(Me.cbxschoolName,"'","''") & "'")....
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
sounds like yo do not have a value for schoolname - you need to use the nz function
Replace(nz(Me.cbxschoolName,""), "'", "''")
Thank you so much, Ajax. SUPER!
Cindy