Part2: Using DLookUp to prevent duplicates in Form

[alexrubio]

{{ Split from: Using DLookUp to prevent duplicates in Form }}

@alexrubio
Hi all,

I was just tasked at tweaking the above macro and again I cannot find the right combination... I need to put an exception to a particular location. In other words the macro should alert to duplicate locations unless the location is equal to "17LR". The above works perfectly in another part of the database, here is what I tried:

DLookUp("[OfficeLocation]","[tblEmployeeInfo]","[OfficeLocation] = Form.[OfficeLocation] And [OfficeLocation]<>17LR") Is Not Null

And it just does not prompt for ANY duplicates anymore, I thought maybe it was because of the "LR" in the location, so I tested with "1752" and the same results, I also tried "Or" instead of "And", that will prompt for the dupes, but when I select "1752" it does not allow me to save the record and/or move to next record, I have to cancel the changes...

Thanks again for your help!!!

[zmbd]

I really dislike that all of the examples show to use the function with the where clause built in place as it make the programmer's life more difficult.

Why you might ask, because you cannot check how the string is actually resolving!

Instead, I suggest a slight modification to make troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

So to use your code:

DIM strSQL as string
strSQL = "[AssetTag1] = Form.[AssetTag1] And [Active]<>0"
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
(DLookUp("[AssetTag1]", _
   "[tblEmployeeInfo]", _
   strSQL )) _
is not null
 

Your where string also looks a bit off...

"[AssetTag1] = Form.[AssetTag1] And [Active]<>0"

Did you intend (I've stepped this here just to put emphases on the data being pulled from the form and added grouping to help with the logic flow):

"([AssetTag1] = " & _
   Form.[AssetTag1] & _
   ") And (" & _
   [Active] & "<>0)"

so that the string would return values from the form?

If you will make these little changes and post back the resolved string we can help you tweak the code.

The other thing of note, it looks as if you are using the DLookup() directly in the if..then conditional which is fine in and of itself; however, once again it can make trouble shooting difficult. I would personally assign you function to another variable and test against that in your conditional, I usually use the NZ() around DLookup() functions and return something logical rather than a potential zero-length string (aka null) or zero when I need to be sure that there is no useable result being returned.

as for why your code is hanging... I suspect it has to do with your function not returning anything useable...

Place a STOP command at the start of your code and step thru it using [F8]... you're most likely in a loop; however, without that code, we won't be able to explicitly solve that problemwhich

[alexrubio]

Hi zmbd,

Thanks for your response, this is a macro I'm working with... The original post had both the working code and the above which was a tweaked version of it. I would also rather leave the post unsplit so that you can get the background of the story thus making more sense...?

Thanks Again...

[zmbd]

alexrubio:
+ The link to the prior thread should provide continuity.
++ the last post in the original thread was asking for the code you were using at that point for that question as your prior post said that code was not working
++ You're asking a second question, albeit closely related to the original question, yet it doesn't follow the discussion in the original thread at that point.

+ The threads can be merged again if needed.

Just a point if interest:
In Access "Macro" is not the same as "VBA."
I understand that in Excel and Word, these are one and the same; however, MS, in their infinite wisdom, when it comes to Access there is a great difference between Macro programing and VBA programing. One major difference, is that when programing for a SharePoint site, VBA isn't supported for the Forms posted to the site (and I have no kind words for that) nor is the normal method of establishing table relationships. However, that is a horse of a different colour.

[alexrubio]

Ok, problem fixed, I was missing single quotes around "17LR" since the field is a text field, grrr, they always get me...

So the working macro code is:

1.DLookUp("[OfficeLocation]","[tblEmployeeInfo]","[OfficeLocation] = Form.[OfficeLocation] And [OfficeLocation]<>'17LR'") Is Not Null

Thanks for your help zmbd, appreciate the suggestions.

[zmbd]

ahh.. the dirty little text fields...
This is precisely why I build those strings in the variable first so that you can see what is being returned.

You might find this tool to be helpful...
{Allen Browne's ElookUp}
There's a link there that leads to subqueries... also a very useful tool to have in the toolbox

BOL...

-z

[alexrubio]

@zmbd
Thanks again zmbd, will look into that in future projects...!

[NeoPa]

I've changed the selection of Best Answer as the original doesn't fit the criteria. If anything was most helpful it was the first reply which pointed the user where to look to find their problem.