Trying to clear a text box in a form

[mcomp72]

Hi everyone. I'm attempting to clear a text box in a form, but obviously there's something I'm doing wrong. I've searched online, but can't find anything that helps.

Here's what I'm trying to do: On my Timecard Form, I want to make sure that the user isn't creating a duplicate time card. So once they enter the job name (in a combo box), select the employee (also a combo box), and then the Week Ending date, I have written code for it to check if these three values already exist in any of the records of my Timecard Table. I'm doing this check on the BeforeUpdate on the WeekEnding text box on the form.

Here's the code I've written for the Sub.

Private Sub WeekEnding_BeforeUpdate(Cancel As Integer)

Dim rstT_Timecards As Recordset
Dim JobID_Table As Integer
Dim EmployeeOnJobsID_Table As Integer
Dim WeekEndingDate_Table As Date
Dim JobID_CurrentRecord As Integer
Dim EmployeeOnJobsID_CurrentRecord As Integer

Set rstT_Timecards = CurrentDb.OpenRecordset(Name:="T_Timecards", Type:=RecordsetTypeEnum.dbOpenSnapshot)

JobID_CurrentRecord = Me.Controls("JobNameComboBox")
EmployeeOnJobsID_CurrentRecord = Me.Controls("EmployeesOnJobsID")


rstT_Timecards.MoveLast
rstT_Timecards.MoveFirst

With rstT_Timecards

    Do While Not .EOF
    
        If rstT_Timecards("TimecardID") <> Me.Controls("TimecardID") Then
    
            JobID_Table = .Fields("JobID_notFK")
            EmployeeOnJobsID_Table = .Fields("EmployeesOnJobsID")
            WeekEndingDate_Table = .Fields("WeekEnding")
            
            If JobID_Table = JobID_CurrentRecord And EmployeeOnJobsID_Table = EmployeeOnJobsID_CurrentRecord And Me.Controls("WeekEnding") = WeekEndingDate_Table Then
                MsgBox "A time card for this Job/Person/Position already exists with this Week Ending date.  You cannot have more than one.  Please select another Week Ending Date."
                Me.Controls("WeekEnding").Value = ""
                Me.Refresh
                rstT_Timecards.Close
                Set rstT_Timecards = Nothing
                Exit Sub
            End If
            
        End If
    
        rstT_Timecards.MoveNext
    
    Loop

End With

rstT_Timecards.Close
Set rstT_Timecards = Nothing

End Sub

I am getting an error message on this line:

Me.Controls("WeekEnding").Value = ""

I've tried replacing the "" with Null, but I still get the error.

The WeekEnding field is of Data/Time format. Not sure if that matters.

The error I'm getting is:


I can tell I'm in over my head here. I haven't attempted to do any BeforeUpdate commands before, and obviously I'm doing something wrong.

[CJ_London]

if the field is a datetime field, it will only accept null or a valid date value

but your code is a lot more complex than it needs to be. you can just use

if dcount("*","T_Timecards","JobID_notFK=" & me.JobNameComboBox & " AND EmployeesOnJobsID=" & me.EmployeesOnJobsID & " AND WeekEnding=#" & format(me.WeekEnding,"mm/dd/yyyy") & "#")>0 Then
        MsgBox "A time card for this Job/Person/Position already exists with this Week Ending date.  You cannot have more than one.  Please select another Week Ending Date."
        me.WeekEnding = null
        me.weekending.setfocus
        cancel=true
end if

I can't work out what your criteria is supposed to be so it my best guess

[mcomp72]

So I can remove ALL of the code I am using and replace it with what you suggest, or your suggestion only replaces PART of my code?

UPDATE: I decided to go ahead and replace the entire Sub with your code. So now I have:

Private Sub WeekEnding_BeforeUpdate(Cancel As Integer)

If DCount("*", "T_Timecards", "JobID_notFK=" & Me.JobNameComboBox & " AND EmployeesOnJobsID=" & Me.EmployeesOnJobsID & " AND WeekEnding=#" & Format(Me.WeekEnding, "mm/dd/yyyy") & "#") > 0 Then
        MsgBox "A time card for this Job/Person/Position already exists with this Week Ending date.  You cannot have more than one.  Please select another Week Ending Date."
        Me.WeekEnding = Null
        Me.WeekEnding.SetFocus
        Cancel = True
End If

End Sub

When I run it, I get the same error, but the runtime number is different.



The line it is flagging is:

Me.WeekEnding = Null

Do you think this could have something to do with it being a BeforeUpdate thing?

[mcomp72]

I just moved it from BeforeUpdate to AfterUpdate and I no longer receive the error. Any idea why this would be happening on BeforeUpdate? I am still too much of a novice with Access to have any idea.

UPDATE: But now it doesn't work right. After I get the MsgBox, I then change the date to a different date, and sometimes it will give me the MsgBox after that, even though I am certain I have picked a new date that is not a duplicate.

2ND UPDATE: I put my code back in (but still in AfterUpdate), and now it's working again.

[ssanfu]

The line it is flagging is:

Me.WeekEnding = Null

You cannot check for NULLs that way.
A NULL is not equal to anything, not even another NULL.

If you enter
? (1 = 1)
in the immediate window, the result is TRUE.

If you enter
? (Null = Null)
in the immediate window, the result is Null (not TRUE as you might expect).


You would have to use the IsNull() function:

If IsNull(Me.WeekEnding) Then

Or you could use

If Len(Me.WeekEnding & "") > 0 Then

[Missinglinq]

I just moved it from BeforeUpdate to AfterUpdate and I no longer receive the error. Any idea why this would be happening on BeforeUpdate?

You cannot change the value of a Control, in the BeforeUpdate event of that Control, because this interrupts the saving of the currently entered data of that Control...which is exactly what the error message tells you!

Linq ;0)>

[CJ_London]

ah - didn't notice that - thought it was the from beforeupdate event. My eyes aren't what they were

if using the beforeupdate event of a control, you have to use the text property

...WeekEnding=#" & Format(Me.WeekEnding.text, "mm/dd/yyyy") & "#")...
...
Me.WeekEnding.text = ""