Dlookup problem - Check to see if a date has been entered

[beacon]

Hi everybody,

I'm really hoping this will be the last problem I run into because I don't have much hair left to pull out! :)

I have a form that the user will enter a census date (indexed field) and a census into. I'm indexing the census date because I don't want duplicate entries.

When the user opens the frmCensus and tries to enter a date, I've implemented code that fires when the user selects a submit button that should check to see if the date has already been entered or not. If the answer is yes, msgbox pops up, clears all fields, and sets the focus on the date field. If the answer is no, the record is saved.

However, things aren't working out the way I had hoped and I think it's because there's an error with the Dlookup function I've used. Here's the current code:

Private Sub cmdSubmit_Click()
 
    Dim varCensusDate
 
    varCensusDate = Me.CensusDate
 
    If IsNothing(varCensusDate) Then
        MsgBox "Date field cannot be blank", vbExclamation, "Null value"
        Exit Sub
    End If
 
    If IsNothing(Me.Census) Or Me.Census = 0 Then
        MsgBox "Census cannot be blank or have a zero value", vbExclamation, "Null Value"
        Exit Sub
    End If
 
    If Not IsNothing(DLookup("CensusDate", "tblCensus", varCensusDate)) Then
        MsgBox "A census value has already been entered for this date", vbInformation, "Duplicate Entry"
        If (Me.Dirty = True) Then
            Me.Undo
        End If
        Me.CensusDate.SetFocus
        Exit Sub
    End If
 
End Sub
 

I thought it was working when I originally tested it out, but know it's popping up the "census value has already been entered" msgbox for everything I try.

Any ideas?

Thanks,
beacon

[PianoMan64]

@beacon
Hello Beacon,

The reason that you're having the issue is because you don't have the DLookup() function Criteria set correctly. You didn't include the field from the table tblCensus in the criteria.

The correct way to do what you're wanting is this:

 
DLookup("CensusDate","tblCensus","[CensusDate]=#" & varCensusDate & "#")
 
 

Hope that helps,

Joe P.

[ADezii]

Hope you don't mind, but I took the liberty of re-writing your code:

Private Sub cmdSubmit_Click()
If IsNull(Me![CensusDate]) Then
  MsgBox "Date field cannot be blank", vbExclamation, "Null value"
    Exit Sub
Else
  If IsNull(Me![Census]) Or Me![Census] = 0 Then
    MsgBox "Census cannot be blank or have a zero value", vbExclamation, "Invalid Entry for Census"
      Exit Sub
  End If
End If
 
If DCount("*", "tblCensus", "[CensusDate] = #" & Me![CensusDate] & "#") > 0 Then
  MsgBox "A census value has already been entered for this date [" & Me![CensusDate] & "]", _
          vbInformation, "Duplicate Entry"
    If Me.Dirty Then
      Me.Undo
      Me![CensusDate].SetFocus
    Else
      'fall through
    End If
End If
End Sub
 

[beacon]

@ADezii
Hi Joe & ADezii,

Y'know, I read about using the '#' sign when using dates and I think I used it in another Dlookup function I wrote, but for some reason I didn't even realize in this case. Thank you for pointing that out.

ADezii...I don't mind you rewriting the code at all, but I would like to know your reasoning for writing it the way you did just so I can gain a better understanding of the functions, in case I run across another instance when I may need to implement it in this way.

[ADezii]

@beacon
Mostly a matter of personal preference, since there are many ways in VBA to accomplish the same task. Simply another approach for you to look at.