Validation by looking into an existing table

[Tej21]

Hi all,

i have the following table:

classID (autonumber and PK)
dateOfClass
className
time
studio (choice of two studios)
instructor (FK)

if i had a form of added a new class, i would like an error message appears if a class is doubled book in the same studio and time with instructor on a specific date.

any help

many thanks all

Tej

[Khriskin]

(Note: I'm keeping this vague as it sounds like you are posting homework questions (per your other post). If this is not the case, I apologize!)

Are you checking for duplicates against just [dateOfClass] and [time], or against [instructor] as well?

If it's just the first two, an easy check would be to use a DLOOKUP for one of the variables using the other one as the filter. Then you could check the DLOOKUP output to see if any matches were found.

If you need all three fields, you would probably have to setup a query that looks to the form for data to match. Then just DLOOKUP the query (as above) and see if any matches were found.

Hope that helps! ^_^

[Tej21]

(Note: I'm keeping this vague as it sounds like you are posting homework questions (per your other post). If this is not the case, I apologize!)

Are you checking for duplicates against just [dateOfClass] and [time], or against [instructor] as well?

If it's just the first two, an easy check would be to use a DLOOKUP for one of the variables using the other one as the filter. Then you could check the DLOOKUP output to see if any matches were found.

If you need all three fields, you would probably have to setup a query that looks to the form for data to match. Then just DLOOKUP the query (as above) and see if any matches were found.

Hope that helps! ^_^

Hey

This is not homework questions - its for my dissertation. Just having a few problems. Creating a database for a sports centre.

Never used the DLOOKUP before but i'll have a go. When I do add new classes in, and look at all current classes available, i do see duplicates, such as:

- two classes happening in the same studio at the same time on the same date with the same instructor!

I will have a go, just cleaning up the db, applying my UI, just needed help on validation.

Cheers

Tej

[Khriskin]

My mistake about the homework, sorry about that! ^_^;; <-(sheepish look) ((baa!!)

I use isnull(DLOOKUP) combo as a fast way to check and see if a query returns empty, but there are probably better ways out there.

isnull(DLOOKUP( "[Field1]", "tblData")) will spit out true if there is no data in tblData (assuming you change [Field1] to a field the query returns) and false if there is. Since the query will only return records that are in conflict, then all you need to know if that the query is empty. ^_^

[missinglinq]

Nothing to apologize for, Martha! A dissertation is homework! Fancy homework, but homework neverless!

Linq ;0)>

[Scott Price]

I agree with Linq, Martha. Your answer is spot on (as they might say in England or Australia, can't remember which LOL)! We don't provide copy-n-paste answers for homework.

As a dissertation, this is still considered under the guidelines of 'homework', however nothing in this thread has violated our posting guidelines. Good work to both of you. To the OP (Original Poster) for wanting to learn and asking a question that will help you learn. To Martha for answering in a way that will require thought.

Regards,
Scott

[Tej21]

Obviously I dont want a copy n paste answer - wheres the learning aspect in that?

I will try and figure out with what i have learnt here, and will get back to you all.

Thanks

Tej