Validation Rule or Query?

Please bear with me...I'm trying to do something to a database I didn't design and I don't know much about technical terms.

What I am trying to do is compare data as it is being entered into a form with what is already stored in the database, but the comparison is between two separate items on the form and in the database. The data in section A on the form must match data in section A in the database table (so it is filtered??); then the data entered in section B on the form is compared to already existing data in section B in the database for all instances where section A information matches to see if a certain item is already in section B in the database (and if so, an error message needs to be displayed on the form; if not, data entry on the form should be allowed). This second comparison is not to check for duplicates; rather it is to avoid certain combinations of section B data.

So, I am not sure if I need to build a query that needs to be ran before updating or if I need to build a validation rule to check if certain data already exists based upon section A and section B data.

Any suggestions?

TIA

msushelly

Explain a little more on why you need this. I believe that what you are after can be taken care of with a few tweaks to your relationships between tables and indexing. If possible it would help if you could post your DB with sample data (or put on an FTP site)so that we can look at it and offer correct advice.

S-

The database is built to track items that need to be repaired on aircraft. Each aircraft system that needs repairs has a unique number assigned to it. Certain items must be repaired immediately; others are deferred and are tracked in our database.

There are systems that cannot be deferred together and our database does not currently flag this. So, on aircraft number 200, we cannot have system X and system Y deferred at the same time, but they can be separately.

What I would like to have happen (and this may not be possible) is the following: as aircraft number 200 is getting a deferred maintenance item entered in a form, the database compares any items currently outstanding on that aircraft and makes sure what item is being deferred can be by checking the unique number to others to make sure there is no conflict. If there is no conflict, the entry can continue. If there is a conflict, then a message needs to pop up to tell the user that this item cannot be deferred because of a conflict.

This will be hard to explain how so give me a day of 2 and I will send you a sample DB that will do this. Please feel free to ask how it is going.


S-

Thanks so much for your help. I'll be patiently waiting!

Hey, I didn't have a time to create the sample over the weekend. I will work on it today, but I have included a sample that has ADO and DAO code for you to look at. The sample I will send you will use VBA code.

S-

Sorry this took so long

Now I didn't give you everything you wanted, becuase it would take even longer

What I have is code that checks

1. To makes sure a maintenace request can be deferred

2. To make sure that there isn't currently a maintenance item already deferred to the specify date for that aircraft

You can use these to simply example to make a more complex checking in your system.

If you need more help please feel free to ask and I will help you fine tune your system if statements

S-

I guess I forgot to tell you the most important part. My company is using Access 97. Can you convert the file so that I can open it? Access 97 doesn't recognize the database format in the files you posted.

Thanks so much!

Here it is in 97 (I think)

Your company really should consider converting to 2000 or to be current 2002.

There is no MS support of 97 and as of the middle of this year there will be no mainstream support from MS for 2000 either. You are leaving your self open to potentail security issues the MS will not fix.

S-

I hear you on the software update...I work for a bunch of cheapskates.

Anyway, I tried the program and here's what I got. When I opened it, a window popped up stating that the code was converted to my program's current version of Visual Basic. I am getting an error after I try to enter information on the form. I enter data and when I click the update button, I get a window that pops up stating: Compile error: can't find project or library. When I click OK, the code pops up and the following is highlighted in yellow:

Private Sub cmdUpdateDAO_Click()

Also two lines down, this is highlighted in brown:

DAOdb As Database

Help!! I'm looking at the code but I can't figure out why this error is occurring.

I understand about the cheapskates

In the VB editor window, Look at tools, references. You should see a reference that is missing. Unselect it and find a different version of that reference.


S-