Date Validation on Form

[wilderfan]

I have created 1 Form in the attached mdb file.

Before a new record is added (through the form) to the table CorpNames, I would like to check that the date entered on the form comes AFTER the date of the last (previous) name change for the corporation in question.

When it does not, a warning message should appear. Something like, "Are you sure this is the correct date?"

If anyone can suggest what VBA coding would help, please let me know.

FYI - The file was created using ACCESS 2003.

Thx.

 

[pbaldy]

In the form's before update event, open a recordset or use DMax on the CorpNames table, getting the max date for the appropriate ID. Test that against the value on the form. If it doesn't pass muster, you can add

Cancel = True

to stop the update and allow the user to change the value.

 

[wilderfan]

Thanks, Paul.

Just to make sure I'm on the right track -

So I should:

Create a Before Update sub procedure that calls the DMax function.

The syntax for the DMax function/procedure would be:


Function DateTest() As Date

Dim dteCurrentMax As Date
dteCurrentMax = DMax("[EffectiveDate]", "CorpNames", "[CorpID] = " _
& Forms!Form_Corporation Name Change Form!Combo19)

DateTest = dteCurrentMax
End Function



I want the criteria in the DMax function to be dependent on the combo box selection from the form.

I wouldn't be surprised if I've got the syntax wrong. If so, pls let me know.

Thx.


-- Robert

 

[pbaldy]

That looks about right, though you may need to bracket the form name, due to the inadvisable spaces. Once you have the max date, test it against the date entered on the form and stop the update if appropriate. Being lazy, I'd probably just have code in the before update event like:

If Me.DateFieldName < DMax(...) Then

 

[wilderfan]

Still having problems unfortunately.

If anyone can spot the error in my VBA code below, please advise.

I have also uploaded the zip file of my mdb file (created using ACCESS 2003).



Private Sub txtSelectDate_BeforeUpdate(Cancel As Integer)

' Define the current maximum date for the selected CorpID
Dim dteCurrentMax As Date
dteCurrentMax = DMax("[EffectiveDate]", "CorpNames", "CorpID = Forms![Form_Corporation Name Change Form]!cboSelectCorpID")

' Compare newly entered date to the current maximum date
If txtSelectDate.Value > dteCurrentMax Then
Exit Sub
Else
MssgBox "You must enter a later date."
Cancel = True
End If
End Sub

 

[boblarson]

Well, one thing I see is you need to change your DMax syntax to put the form reference outside of the quotes:

dteCurrentMax = DMax("[EffectiveDate]", "CorpNames", "CorpID =" & Forms![Form_Corporation Name Change Form]!cboSelectCorpID)

 

[pbaldy]

That and this

MssgBox

should be

MsgBox

but the before update is never actually firing because of your popup calendar. You can fire it from the other events related to that.

 

[wilderfan]

Thanks, Bob. Thanks, Paul.

I'll forgive myself for the quotation mark mistake. But the Msgbox typo is a little embarrassing !

Paul, if I understand you correctly, I should insert my VBA code into the 2 event sub procedures which trigger the pop up calendar. Is that correct?

Thanks again to both of you.

Much appreciated.


-- Robert

 

[pbaldy]

Well, my point is that the before update event of the control is not fired when you set the value in code, so that code isn't actually running as you have it. I'd probably put the test in the before update event of the form, which will fire either way.