refer to table to validate possible?

[Scottie]

G'day Guys,

I'm trying to include a variable Blacklist of sorts in my database.

I have my main form entering company details into one table. In another table, I have a list of names which I would like to refer to to prevent them going any further in the database.

I'm trying to get the form to look through the names listed in the second table to detect whether one of the company names on the blacklist is used in the form.

At the moment, I have the block working fine via a LIKE statement, but this cant change as various company names get added/deleted by the administrator.

Is there a looping function of some description that I've missed somewhere?

Thanks for any help in advance!

Scottie.

 

[boblarson]

Instead of looping through, why not use a DCount to find the name. If you use DCount and the value returned is 0 then it means that name is not in the table.

 

[Scottie]

woa didn't think of that!

Yeah she's working a charm now! Thanks again for your really fast and accurate response Bob!

 

[Scottie]

I've hit a snag...

It seems the error is in the criteria... a test I have run is:
txtCount = dCount("[name]", "[blacklist]", Me.Company_Name.value = "[name]") returns 0 even if there is a match
txtCount = dCount("[name]", "[blacklist]", Me.Company_Name) disregards the criteria and counts all entries in blacklist!names

is there something I'm missing? I can't find where I'm going wrong. Is this supposed to be backed up by a Private Function or a Module of some description?

Thanks for any help!

Scottie

 

[Scottie]

No ideas?

I've had a look through MSDN and through these forums, but I haven't managed to get the criteria working.
Here's the database I'm working on:

again, any help would be greatly appreciated.

Scottie.

 

[Scottie]

I've managed to get the criteria working (thanks to this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=128253&highlight=dcount )
but is there a way to use a wildcard in it to allow for any 'like' entries? From what I've tried, the LIKE operator cant replace the '=' in the criteria part.

 

[Banana]

Perhaps use a SQL query so you can use a LIKE operator. If the criteria is on a control on the form, you can refer that control. In Query builder, right-click on the criteria cell and select "Build..." then navigate to Loaded Forms -> YourFormName and select the appropriate control.

HTH.

 

[boblarson]

Your syntax for the DCount was way off. It SHOULD be:

txtCount = DCount("[compname]", "blacklist", "[compname]='" & Forms![Company details]![Company Name] & "'")

but, we're running into a problem if the company name has an apostrophe in it. So, I'm not sure what the fix for that one is. Normally you would want to look it up by company ID and not by name so that way you would not suffer from that type of problem.

 

[Scottie]

Ok, yeah I tried that string from another thread Bob, but I guess it was the apostrophe that was getting in the way.

Thanks again for your comments guys, I'll see how an SQL query goes and if not, I'll get to implementing ID's.

Thanks again for your time guys! greatly appreciated!

Scottie

 

[Banana]

If you must use names (and understand that it'll take a bit longer than if we use IDs), you can handle apostrophes or other characters if you insert the string in triple quotes.

Instead of:

'"MyString"'

Use:

"""MyString"""

This will allow Access to accept non-alphanumeric characters as a part of the string.

HTH.

 

[Scottie]

Thanks for the tip! I'll give it a go!

 

[Scottie]

Sorry to bring up an old(ish) thread guys.

I had given this a further look recently after getting my DB to a good solid working state. I am looking back into this because I'm not going to be around long where I'm working and would like to make this feature a bit more user friendly to manage.

For now, I have hard-coded the validation (a LIKE operator) into a button which works fine.

I can't use ID's for validation because the details will be filled out by companies who may come and go... with the dynamic management (annual refreshing of data) used in here, the references aren't consistent enough to use fixed references (If that makes any sense!)

I have re-tried using the dcount and dlookup functions, using the """ method to allow for commas/appostrophe's/etc... and I get a type mismatch error on this:

txtCount = DCount("[compname]", "blacklist", """ & Me.Company_Name & " = " & " * " & [compname] & " * " & """)

This is one of those situation where I wish it was possible to have something simple like:

If Me.Company_Name LIKE [blacklist].[compname].all then
msgbox "Go to jail, Go straight to Jail, do NOT pass GO, do NOT collect $200"
End If

*sigh* I know I'm sort of grasping for straws, the examples above (thanks again for those guys!) didn't return errors, but they didn't seem to return any results that were useful (either not detecting a match, or detecting everything)

after trying to modify them to get some results, they returned errors varying from "access cant find "|" referred to in your expression" to "Invalid syntax".

anyway, I have attached a cut-down version of my database if there's anyone keen on helping me sort out this dynamic database!

Cheers in advance, and (if i ever bump into you) a free beer at the local!

 

[Scottie]

I've kept trying, and to simplify it, I've been attempting to create a query that displays fields which might be similar...

Company table includes (in field [Company Name])
Daryll's Plumbing
Henrys Housing
Pauls Pipes
Professional Plumbing Services
Tim's clothing

blacklist table includes (in [compname])
Henry
Indy
Paul
Tim

Query syntax is:

SELECT blacklist.compname, Company.[Company Name]
FROM blacklist INNER JOIN Company ON blacklist.compname = Company.[Company Name]
WHERE (((Company.[Company Name]) Like "*" & [compname] & "*"));

The syntax was generated by the wizard after I put the criteria in.
I have tried using a LEFT JOIN to receive no results again.

Then I put 'Pauls Pipes' in the blacklist, it comes up no worries...

I don't know why the wildcards aren't being effective...
Has anyone had this before?
Do I need to implement some special activeX control or something?

Thanks for any advice!

Scottie

 

[Scottie]

I guess I'm a little delusional in getting this working

I'll keep plugging away and if I stumble onto something, I'll let y'all know!