macro to check data on a form control

[pedro]

Hi,

I have created a control on a form to enter data and wanted to create a
macro which checked if the data entered appeared on a query. If the data
entered did appear on the query then a message box would appear. I assume
this would be a before update event (or after update?) on the control.

Thanks for your assistance.

 

[Ken Snell \(MVP\)]

BeforeUpdate or AfterUpdate -- depends upon the purpose of displaying the
message box: information? tell user the value is invalid? something else?
Tell us why you want to show the message box.

Are you asking how to write such a macro as well?

 

[pedro]

Hi Ken,

The macro checks to see if the value entered in the control appears in a
query. The message would be something like 'That value exists in a table' -
information. Yes how would I construct the macro.

thanks

 

[Ken Snell \(MVP\)]

You could run a macro like this in the control's AfterUpdate event (if you
don't care if the value entered is in the table or not prior to saving the
value), or in the control's BeforeUpdate event (if you want to prevent the
saving of the duplicate data value):

AfterUpdate event:
Condition: DCount("*", "TableName", "FieldName=" & [ControlName]) >0
Action: MsgBox
Message: "Value already exists in table."
Condition: . . .
Action: StopMacro


BeforeUpdate event:
Condition: DCount("*", "TableName", "FieldName=" & [ControlName]) >0
Action: MsgBox
Message: "Value already exists in table."
Condition: . . .
Action: CancelEvent
Condition: . . .
Action: StopMacro