Verify data entered on form is new 1

[puforee]

I have a form I will use to change a set of records (Duplicate) to a new idenity.
I am dealing with course numbers. I first pick a cours number to duplicate...no problem...I have done this. Now I want to type in the new coursenumber in a text box...lets say I want to create a new course numbered 406. I already have many courses and I don't want to accidently duplicate a course number I already have.

So, I enter a number like 406 in the new course number field. Using an event (which one is best?)after the number is entered, how would I check a table/query containing all my course numbers to ensure 406 has not been used? If it has been used a MSG will indicate "Already usee". If not used...it will continue to run some other code....

Any quick ideas.

Thanks

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[puforee]

PHV

Ok, I looked at the DLookUp function...It will find items in a table with a match. I don't want to find the match...a match would be incorrect. I want to validate there is no match.

So, from my form-new course number field...I want to validiate that new number is NOT is my other table?

Is there some way I could twist the DLookUp to accomplish this?

 

[dhookom]

Is there some way I could twist the DLookUp to accomplish this?

Do some testing to find out what DLookup() returns when there is no match and use that logic in your code.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I will give it a try...thanks.

 

[puforee]

OK...I used this code trggered by after update event for my new course text box.

Private Sub New_Course_AfterUpdate()
Dim varX As Variant
varX = DLookup("[New Course]", "Major ATA TBL", "Course" <> "[New Course]")
MsgBox varX
End Sub

It returned what ever I typed in the text box. Not good.

Help

 

[PHV]

varX = DLookup("Course", "Major ATA TBL", "Course='" & Me![New Course] & "'")
If IsNull(varX) Then MsgBox "OK, it's a new course"

If course is a numeric field then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[puforee]

Great...here is what I ended up with. If there is no match...that is good so instead of IsNull I wanted to use Not Null but I could not get the syntax correct.

So I used this...hope it meats with your approval.

Dim varX As Variant
varX = DLookup("Course", "Major ATA TBL", "Course='" & Me![New Course] & "'")
If (varX) > 0 Then MsgBox "This course has already been used...Try another course number

 

[PHV]

If Not IsNull(varX) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[puforee]

PHV
I'm back.

So I am using varX = DLookup("Course", "Major ATA TBL", "Course='" & Me![New Course] & "'")

But...I just realized I need more criteria. I need to match Me.Model field on my form to Major ATA TBL, Model (field)

So, for a given model I need to ensure the course number has not been used. Can this be done in one DLookup statement.

 

[puforee]

Never mind PHV. I just changed the Domain to a query that already filters by model...so the DLoopUp stayed the same except for the Domain name.

Again...thanks for all your help.