Compare entered data to data in table and present Msg Box 3

[Wease]

I've created a membership database, and I have a list of people who have been banned from membership. I want my users to be aware when they are entering a member name that they may be entering an individual who has been banned. My idea is to create a new table called "banned" with the first and last names of the offending individuals. After the user has entered a members last name on the form and exits the last name field, I want the program to compare the entered last name to the last name in the banned table. If there is no match, the user is merely taken to the next field in the form. If there is a match, I want a Msg Box to appear that says "Attention, Jon Doe has been banned for life". Where Jon Doe is the first and last name from the banned table. I only want an "ok" button on the Msg Box that allows the user to dismiss the msg box and move on. It will then be up to the individual to determine if they can continuing adding this member or delete the entire entry.

I apologize for my ignorance, but I have no idea how to program this in access. Could someone point me in the right direction?

 

[ZmrAbdulla]

Look for "DLookUp Funtion" in the help file

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[Remou]

Hi ZmrAbdulla
If there is a long list of banned members, would a recordset be more suitable, in case of more than one banned member with the same last name?

 

[BoxHead]

something like...

Private Sub txtLastName _AfterUpdate()
If DCount("*", "tblBanned", "[bannedLN] = " & """" & txtLastName & """" & " AND [bannedFN] = " & """" & txtFirstName & """") > 0 Then
MsgBox "The Name " & txtLastName & ", " & txtFirstName & " is on the listed of Banned Members.", vbOKOnly, "Verify Name!"
End If
End Sub

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[Wease]

BoxHead,

Thanks for you help, I must be close but I'm getting a run-time error.

Private Sub txt_primary_last_name_AfterUpdate()
If DCount("*", "tblBanned", "[banned_last_name] = " & """" & txtLastName & """" & " AND [banned_first_name] = " & """" & txtFirstName & """") > 0 Then
MsgBox "The Name " & txtLastName & ", " & txtFirstName & " is on the listed of Banned Members.", vbOKOnly, "Verify Name!"
End If
End Sub

I'm getting the error that it can't find the input table or query? I'm an access newb, so forgive me but did I use an incorrect expression to specify the table? My table is called Banned

 

[BoxHead]

Wease,

Tke a look at DCount in the help file. Basically what the function is saying here is:

Count all of the records {DCount("*"...} in the table named "tblbanned" where the Last Name field in tblBanned {banned_last_name} is equal to my Form control named 'txtLastName' and where the First Name field in tblBanned {banned_first_name} is equal to my Form control named 'txtFirstName'

If your table is named Banned, changed 'tblBanned' to 'banned', but also check the field names and the textbox names.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[Wease]

John (a.k.a BoxHead),

Thank You VERY MUCH, it worked perfectly !! I really, really appreciate your help !!!!!!!!!!

 

[spizotfl]

I have a quick question for Boxhead, if you are still around....
In the code

If DCount("*", "tblBanned", "[banned_last_name] = " & """" & txtLastName & """" & " AND [banned_first_name] = " & """" & txtFirstName & """"

why do you need all of the qoutes?
thanks

 

[BoxHead]

Wease,

Glad to help!

spizotfl,

The quotes let Access evaluate a variable instead of a single value. Without the quotes, the function would be looking for someone whose last name was actually Txtlastname.

The reason for using four double quotes is two-fold:
1.) Access knows that if you put something in between two double quotes that it is a string so it gets to the first one and evaluates everything as a string until it gets to the next one. But the program reads the quote as an indicator of a string not as a part of the string. In the aggregate functions, you need to have quotes that are actually in the string.

[COLOR=blue]
So, Access sees the first double quote, says, "Ah, a string is starting!"  

It reads the second double quote and says, "Whoa, that was weird, a Zero-Length-String".  

It reads the third double quote and screams, "[b]Time-out here![/b] Are we starting [i]another[/i] string?" 

And then it gets to the fourth and says, "I get it!  The quotation mark [b]is[/b] the string!"[/color]

2.) While it makes more logical sense to me to use three double quotes, I have had that not work. I've seen others use double-quote/single-quote/double-quote at times ("'"), but I've had occasions when that didn't work and I have yet to have a problem with using the four... so basically, it works for me.

It's like trying to type an opening bracket [, the word 'smile' and a closing bracket ]in this post. If you type them all together they display ...

.






John

Use what you have,
Learn what you can,
Create what you need.

 

[spizotfl]

thank you, that makes very good sense.
mike

 

[PHV]

but I've had occasions when that didn't work
A way that should always work:

If DCount("*", "tblBanned", "[banned_last_name] = '" & Replace(txtLastName, "'", "''") & "' AND [banned_first_name] = '" & Replace(txtFirstName, "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

I was away..

Remou, DlookUp will do it
see PHV's answer in thread702-1140593 for instance. You need to use DlookUp twice to check the FirstName & LastName with "AND".
And Wease was asking for LastName only.

After the user has entered a members last name on the form and exits the last name field, I want the program to compare the entered last name to the last name in the banned table.

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[Remou]

Hi Zameer
It is because Wease was asking for last name only that I wondered about a recordset. It seems to me that with a large membership and a large banned list it would be possible to get two people with the same last name on the banned list. It also seemed that there might then be a danger (with dlookup) that the user remained unaware of a second person on the banned list with a particular surname.

 

[ZmrAbdulla]

Any way DCount will be better for less coding.

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[BoxHead]

PHV,

Thanks for that!

I put your Replace function in a new function called agVal() and used it in a series of Aggregate functions with great results.

Function agVal(myVar) As String
If IsDate(myVar) Then
agVal = "#" & Replace(myVar, "'", "''") & "#"
ElseIf IsNumeric(myVar) Then
agVal = Replace(myVar, "'", "''")
Else
agVal = "'" & Replace(myVar, "'", "''") & "'"
End If
End Function

What I don't understand is how does it work?

It seems that the variable is 'read' by access with single quotes to begin with and your method changes that single quote with a single-quote/space/single-quote combination.

Is that right? How could you possibly know that?




John

Use what you have,
Learn what you can,
Create what you need.

 

[RoyVidar]

Single quotes would only be a problem with text fields - no valid date or number contains a single quote, so you would only apply it to the last part of your function (text/string).

The challenge, regardless of working with domain aggregates, WhereCondition of openform/openreport, DAO or ADO, is that we are building a string containing either the whole SQL statement, the where clause (without the keyword Where) or only the criterion. This string, is what is then later evaluated and eventually possibly executed by whichever function or method we are using.

When such string contains special characters, like single quotes "O'Neil, O'Brian"... that singulare occurance of single quote is probably evaluated as a text delimiter without a closing text delimiter, creating syntax errors and the likes - "O'Neil" just doesn't always evaluate correctly;-) Doubling it up, like demonstrated by PHV, should make it accepted by all methods. Note - it is doubling up the single quote, not making any quote/space/quote combinations.

Try out PHV's suggestion in the immediate pane (ctrl+g):

[tt]s = "O'Neil"
? "'" & Replace(s, "'", "''") & "'" -> 'O''Neil'
s = "O'Brian"
? "'" & Replace(s, "'", "''") & "'" -> 'O''Brian'[/tt]

Doublequoting, as you demonstrated in this thread, works for DAO, WhereCondition and the Domain Aggregates, while the replace function as demonstrated by PHV will also work with ADO (though, for the ADO .Find method and .Filter property, I think it is only good for one occurance of single quote - but then there are other methods working for that)

Roy-Vidar