Making a macro that looks for duplicate date in a query

[Guest]

I have a form with a textbox in which to enter dates. I want to make a macro
for the textbox that :
(1)checks in a query to make sure that date isn't a duplicate
(2)gives the user a message box warning if it is a duplicate.

The form name is " formJS" and the textbox (control) name is "Date." The
query name is "qryJS" and the field name is "Date." I understand how to make
the macro and how to use it in the textbox on the form, but I need help with
the macro condition. Not sure what expression to use to get the results I
want.

 

[John Vinson]

I have a form with a textbox in which to enter dates. I want to make a macro
for the textbox that :
(1)checks in a query to make sure that date isn't a duplicate
(2)gives the user a message box warning if it is a duplicate.

The form name is " formJS" and the textbox (control) name is "Date." The
query name is "qryJS" and the field name is "Date."

Change the controlname and the fieldname. Date is a reserved word for
the builtin Date() function (which reads the computer clock and
returns today's date); Access WILL get confused! I'd also give the
textbox a different name from the query field (txtItemDate and
ItemDate respectively, for example).

I understand how to make
the macro and how to use it in the textbox on the form, but I need help with
the macro condition. Not sure what expression to use to get the results I
want.

A Macro is probably not the best tool for this, since Macros are VERY
limited when it comes to controling program flow. I'd use VBA code
instead.

Select the BeforeUpdate event of the textbox and click the ... icon by
it; choose Code Builder. Access will give you the Sub and End Sub
lines. Just edit in the rest.

Private Sub txtItemDate_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not IsNull(DLookUp("[ItemDate]", "[qryJS]", _
"[ItemDate] = #" & Me.txtItemDate & "#") Then
iAns = MsgBox("This date is already entered! Add it again anyway?", _
vbYesNo)
If iAns = vbNo Then
Cancel = True ' stop it from saving the date
Me.txtItemDate.Undo ' remove the user's entry
Else
<do something appropriate - perhaps nothing at all if you want
the date added after warning the user>
End If
End If
End Sub


John W. Vinson[MVP]