Using DLookUp to prevent duplicates in Form

[alexrubio]

Hello All,

I've been successfully using the following code:

DLookUp("[AssetTag1]","[tblEmployeeInfo]","[AssetTag1] = Form.[AssetTag1] ") Is Not Null

In a Macro and basically it looks up the AssetTag field in all records in the database to make sure there are no dupes prior to leaving the field, its set on the Before Update of the respective field.

Now I have been trying to implement the same scenario but now looking at a checkbox, in other words I need to make sure a checkbox's value is True before I perform the above, otherwise ignore it and allow the dupe. Why because I cannot have any dupes on records that are currently set as active (chkActive = True), only on records that are not active (chkActive = False)

I tried adding an If prior to the above stating:

If [chkActive] = False Then...

But the problem is that it only checks the current active record you are working with and not all records.

If anyone can shed some light on this, would really appreciate it!

Thanks in advance!!!

Alex

[twinnyfo]

Alex,

So, you want it to find out if there is a record with the same AssetTag AND the Checkbox checked? This might work:

DLookUp("[AssetTag1]", _
    "tblEmployeeInfo", _
    "[AssetTag1] = " & Me.[AssetTag1] & _
        " AND chkActive") Is Not Null

Hope this hepps!

[alexrubio]

Thanks twinnyfo,

Exactly, but won't the "Me[AssetTag1..." only look at the current record on display and not all records? The form statement is not needed?

Thanks again,

Alex

[alexrubio]

@twinnyfo
Tried as above and got error:

The object doesn't contain the Automation object 'Me'.
You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

[twinnyfo]

"Me.AssetTag1" is equivalent to "Form.AssetTag1"

But, perhaps the Macro doesn't like the similarity to VBA (which, by the way, it is much easier to manage things in an Access project via VBA than with Macros).

How does this work:

DLookUp("[AssetTag1]", _
    "tblEmployeeInfo", _
    "[AssetTag1] = " & Form.[AssetTag1] & _
        " AND chkActive") Is Not Null

It is the same as your original code, but adds the chkActive criteria.

[alexrubio]

@twinnyfo
Fails with macro error 2001, could it be because we are not telling it if chkActive is True or False somewhere?

If I don't do it using a macro, how else can we check as soon as the value is chosen?

[twinnyfo]

if chkActive is a checkbox (Yes/No Field), then using just the field name evaluates to its value (that is, if the field is true, then it returns true). How this works in a criteria expression is that you are trying to find a record that has a particular AssetTag. When the engine finds that tag, the expression "AssetTag1 = 37" (as an example) evaluates to "True". When you add the statement "AND chkActive", if there is arecord with AssetTag of 37 and the check box checked for chkActive, the statement "chkActive" evaluates to "True". Thus you have "True AND True" which evaluates to "True".

This abbreviated method just saves typing, but is a good habit to get into, because lets say you had a check box and every time you click a button, you want the check box to change its value. Thus, you have two ways of doing this:

If Me.chkIndicator = True Then
    Me.chkIndicator = False
Else
    Me.chkIndicator = True
End if

OR You could simply say:

Me.chkInidator = Not Me.chkIndicator

To you, which is easier to write? I know--more than you probably wanted.

Concerning the next question...........

Instead of using a macro, create an event procedure in the same BeforeUpdate Event on the form. VBA and Macros have many similarities, but VBA is much more powerful and flexible and (I think) easier to write and troubleshoot....

[alexrubio]

@twinnyfo
Thanks for your explanation, I don't think it will work in my scenario because I have about 7 fields to check, each of them check each other for dupes...

Now, I tried this on the macro:

DLookUp("[AssetTag1]","[tblEmployeeInfo]","[AssetTag1] = Form.[AssetTag1] And [chkActive]= -1") Is Not Null

And it worked fine, but it only checks the current record open on the screen, NOT all records, as I need. I also used "-1" because for the life of me I always have trouble placing the single and double quotes on the correct place, hehe... If not I would rather use "True" instead.

I seem to be almost there, but just need that last push, hehe, to get this working...

[twinnyfo]

I still don't understand:

And it worked fine, but it only checks the current record open on the screen

The very definition of using DLookUp is that it searches all records in the Data source specified, in your case, tblEmployeeInfo. But, it is looking for the value of AssetTag1, which is found on your form. If your Form is bound to the Table, and the current record is shown, and it is the only record that has that particular AssetTag, then it will find only that record. The way you have explained it, you are LOOKING for the record that has that record's AssetTag.... I don't know how else you intend to do it.

[alexrubio]

@twinnyfo
It is not doing it, because when I set the AssetTag1 field of another record, whose chkActive field is "False", and on another record I select the same asset, I get no warning. But if I check the chkActive box on that same record prior to entering the asset, it does warn me...

I've had all of this working perfectly, without the checkbox option of course, for a long time, just need to add the checkbox option....

[twinnyfo]

And [chkActive] = Form.chkActive

Although I am still confused as to how your macro (as written) works.

To the DB engine, "[AssetTag1] = Form.[AssetTag1]" should be a meaningless expression, as is "[chkActive] = Form.chkActive", as [AssetTag1] must be evaluated against a numerical value (assuming, here....) and [chkActive] must be checking for True or False, which the expression "Form.chkActive" is neither, but, would be evaluated as "True" because it has a value that does not equal -1.

More properly, your expression should be:

DLookUp("[AssetTag1]", "tblEmployeeInfo", "[AssetTag1] = " & Form.[AssetTag1] & " AND [chkActive] = " & Form.chkActive) Is Not Null

I removed all the line continuation breaks, in case that was an issue in your macro.

All I can say is that to a VBA programmer, everything about the syntax of your original post tells me that it cannot work. But if it works, then that is all that matters.

However, in lieu of all this, the more complex you get with your db, the more you will want to move toward VBA exclusively.

[jforbes]

What is the Control Source of the chkActive Control?

I doubt that it is chkActive, probably more like Active. You are providing the DLookup with a Where Clause which means you need to provide the Table's Column Names on one side of the expression and the expected value on the other.

Also the Expected Value for the Form confuses me a little, but that may just be due to my lack of experience with Macro Development. I would expect it to be in this format:

Form![The Form Name]![Control Name]

This might work:

DLookUp("[AssetTag1]","[tblEmployeeInfo]","[AssetTag1] = Form![The Form Name]!AssetTag1 And [Active]<>0") Is Not Null
 

Lastly, I typically test a true/false, yes/no expression against 0. It works easier that way across platforms as 0 is nearly always False or No, where as the value for True can vary.

[alexrubio]

@twinnyfo
I tried your above code and it failed with error 2001...

The original code was suggested a while ago by someone in another forum and they suggested I use a macro because its not only checking the table for possible dupes but also the forms other fields prior to saving the record to flag them as dupe as well... I have 7 asset tag fields (AssetTag1, AssetTag2, etc.) each has the original code referencing each other so that no dupes can be entered anywhere... I really did not question why it had to be a macro, but I guess I can try it as an expression and see what the results are without the checkbox and see if it performs the same purpose and then after that, if it works, try to add the checkbox in the loop...

[twinnyfo]

Yes, this question is a bit confusing to me, also.

[alexrubio]

@jforbes
Thanks Much jforbes,

Your code failed with error 2001, though you are absolutely right about chkActive, I ALWAYS catch myself making that mistake chkActive is the name of the checkbox, not the table source name "Active"!

But I tried your code again with that change and it did not work, BUT, I fixed the "Active" issue on my previous code and it worked like a champ!

Here is the final code:

DLookUp("[AssetTag1]","[tblEmployeeInfo]","[AssetTag1] = Form.[AssetTag1] And [Active]<>0") Is Not Null

For some reason it does not like the name of the form in there, "frmEmployeeInfo". Going to try twynnyfo's suggestion and try to get away from the macros, do you think the same code will work outside of the macro.

Again thanks a million to both of you, could not have done it without your help...!

[twinnyfo]

VBA should have very similar syntax. Both J and I are standing by to assist as needed.

[alexrubio]

@twinnyfo
Again, Thank you guys a lot for this... I will get this up and running with the macros to make sure it all works, then I will make a copy and try the VBA...

[twinnyfo]

There is also a way to convert your existing macros to VBA. I have used that in the past. MS does an OK job, but my VBA always required cleaning up after their conversion, just to make it pretty.

[alexrubio]

@twinnyfo
Ok, I tried the above in VBA and did not work...

I tried with and without form name and obviously the working macro as well...

[Brilstern]

alex,

Can you post your attempted VBA so we can see where any issues are at?