auto lookup

[davidg47]

I am a little bit of a novice when it comes to using VBA with Microsoft Access. I am building a form called "orders" with a db called "orders1". There is the ability to have 10 orders displayed per form with 5 fields per order that display important information to consider when filling out an order. Is there a way so that as the last field in an order line is filled out that a query or something could run against the "orders1" db to see if an order with the same information already exists? For example: If "Company#1" orders "Item#2" with "ProductRights#A", "TerritoryRightsA", to be used between the dates of "Jan-1-03" and "Dec-31-03", after the last field in the line is filled out, a check would be done of the "orders1" db to see if an order for the same Item# with the same ProductRights, same TerritoryRights, and at any time that falls within the same time period as the newly entered order, then a MsgBox would pop up informing the user that another company or even the same company has placed the same order sometime during the same time period as the order they just entered? The difficult part of this I guess is that even if the new order overlaps an existing one by one month in the date range, then a message needs to warn the user of the existence of the order in the db.
I have attached a .jpg of the orders form to maybe help you understand more clearly. The fileds I have highlighted are the ones on each line order that I need to check for.
I'm not sure this is the right place to post this, so I will also post it in the General Forum.
Thanks for all your help.

 

[dbalargin]

Create a command button. In the code, type: docmd.runsql "SELECT * FROM table WHERE Item#2 = "<- a field in that table. Include all the other criteria you want. Bind this query. If the query returns a value, have the pop-up box display.

 

[davidg47]

Thanks for your advice! But, sadly these people are so darned lazy so that they don't want to have to click a button. Is there a way so that as the last field in the End Date/Rights field looses focus, that the query is automatically run? If the query finds an equal order, sometime within the same date range, how would I code the command to open the MsgBox when the query finds the information?

 

[dbalargin]

use after update