Macro for Error Message

[Risikio]

I am trying to write a macro that will bring up an error message if the data
entered on a form does not match the data already entered into the database.
I know I have to use the MsgBox arguement, but I am having trouble with the
condition.

As an example, if the user enters "James" into the First Name field on the
the form, but the database record has "Jim", I need the error message to
alert the user that the data does not match. The form has three separate
fields that will require this error message [First Name], [Last Name], and
[Category].

I also have a fourth error message if all three of those fields are left
blank. The condition for the fourth error message follows, but it does not
work.

[Forms]![criteria]![First Name]="" AND [Forms]![criteria]![Last Name]="" AND
[Forms]![criteria]![Category]=""

Nothing happens for this fourth error message when I test the form leaving
the three fields blank.

Any help would be appreciated. I am not very good with macros yet.

 

[Steve Schapel]

Risikio,

"" is not blank. It is a zero-length string, or empty string. This
is not the same as Null. Also, assuming your macro is happening on an
event on the Criteria form, you do not need the Forms! reference. So...
[First Name] Is Null And [Last Name] Is Null And [Category] Is Null

As regards your main question, I am not 100% clear on your meaning. You
mean if the user enters "James", and there is no record at all anywhere
in the table for "James"? Or does this apply to a specific record? If
so, how do you know which record? Can you give some further examples
please?

 

[Risikio]

Thank you for the help on the null vs. blank.

As to my main question - If the user enters "James" as a [First Name] and
there are no entries in the data database for "James", I need an error
message. If there is a record for "James", the macro needs to move to [Last
Name]. If the user enters "James" for [First Name] and "Smith" for [Last
Name] and there are no records to match both fields, the user should get an
error message. (The user will enter both first and last names).

The third field is [category]. The user has the option of leaving the [First
Name] and [Last Name] fields blank and entering a [category]. If the user
leaves [First Name] and [Last Name] blank and enters a [category], the user
needs to get an error message if the [category] does not match any records in
the database.

While most of this may seem trivial and unneccessary, the users of this
database are visually impaired (blind) and therefore will have a harder time
knowing whether the information they have typed in is incorrect. I am trying
to develop the error message to alert them prior to printing that the
information they have entered will result in the printing of a blank sheet of
paper.

Thank you very much for your help.

Risikio,

"" is not blank. It is a zero-length string, or empty string. This
is not the same as Null. Also, assuming your macro is happening on an
event on the Criteria form, you do not need the Forms! reference. So...
[First Name] Is Null And [Last Name] Is Null And [Category] Is Null

As regards your main question, I am not 100% clear on your meaning. You
mean if the user enters "James", and there is no record at all anywhere
in the table for "James"? Or does this apply to a specific record? If
so, how do you know which record? Can you give some further examples
please?

--
Steve Schapel, Microsoft Access MVP

I am trying to write a macro that will bring up an error message if the data
entered on a form does not match the data already entered into the database.
I know I have to use the MsgBox arguement, but I am having trouble with the
condition.

As an example, if the user enters "James" into the First Name field on the
the form, but the database record has "Jim", I need the error message to
alert the user that the data does not match. The form has three separate
fields that will require this error message [First Name], [Last Name], and
[Category].

I also have a fourth error message if all three of those fields are left
blank. The condition for the fourth error message follows, but it does not
work.

[Forms]![criteria]![First Name]="" AND [Forms]![criteria]![Last Name]="" AND
[Forms]![criteria]![Category]=""

Nothing happens for this fourth error message when I test the form leaving
the three fields blank.

Any help would be appreciated. I am not very good with macros yet.

 

[Steve Schapel]

Risikio,

Ok, thanks for the further explanation.

I think this can all be managed from the Conditions of your macro.

On the After Update event of the First Name control on the form, you can
have a MsgBox macro that has the Condition...
DCount("*","YourTable","[First Name]='" & [First Name] & "'")=0

On the After Update event of the Last Name control on the form, you can
have a MsgBox macro that has the Condition...
DCount("*","YourTable","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0

 

[Risikio]

Steve,

Thank you again for all your help. The [First Name] MsgBox Macro is working
perfectly. I used the same condition for the [category] (changing the field
names of course) and it seems to be working as well.

The MsgBox macro for the [Last Name] however does not seem to do anything.
I have double checked to make sure I copied it correctly and I have gone
through the expression piece by piece. It looks correct to me, but does
nothing on the After Update event. If I understand the condition correctly,
the asterisk at the beginning is to check all records in the table specified
(in this case my table is "Main"). The condition then checks the first name
against the first name entered and while at the same time checking the last
name against the last name of the same record. It looks correct and sound
correct when going through piece by piece, so I am not really sure why it
isn't working.

Thanks again for all the help.

Risikio,

Ok, thanks for the further explanation.

I think this can all be managed from the Conditions of your macro.

On the After Update event of the First Name control on the form, you can
have a MsgBox macro that has the Condition...
DCount("*","YourTable","[First Name]='" & [First Name] & "'")=0

On the After Update event of the Last Name control on the form, you can
have a MsgBox macro that has the Condition...
DCount("*","YourTable","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0

--
Steve Schapel, Microsoft Access MVP

Thank you for the help on the null vs. blank.

As to my main question - If the user enters "James" as a [First Name] and
there are no entries in the data database for "James", I need an error
message. If there is a record for "James", the macro needs to move to [Last
Name]. If the user enters "James" for [First Name] and "Smith" for [Last
Name] and there are no records to match both fields, the user should get an
error message. (The user will enter both first and last names).

The third field is [category]. The user has the option of leaving the [First
Name] and [Last Name] fields blank and entering a [category]. If the user
leaves [First Name] and [Last Name] blank and enters a [category], the user
needs to get an error message if the [category] does not match any records in
the database.

While most of this may seem trivial and unneccessary, the users of this
database are visually impaired (blind) and therefore will have a harder time
knowing whether the information they have typed in is incorrect. I am trying
to develop the error message to alert them prior to printing that the
information they have entered will result in the printing of a blank sheet of
paper.

 

[Steve Schapel]

Risikio,

Yes, the second condition is supposed to check for that particular
combination of First Name and Last Name. So, in the database there may
be a James Smith and a Bob Brown, but if your users type in James Brown,
the message box should be displayed if there is no James Brown in the
table. Can you post back with an exact copy/paste of your condition
expression?

 

[Risikio]

Steve,

Here is the exact copy/paste of the condition.

DCount("*","Main","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0

Thanks.

Risikio,

Yes, the second condition is supposed to check for that particular
combination of First Name and Last Name. So, in the database there may
be a James Smith and a Bob Brown, but if your users type in James Brown,
the message box should be displayed if there is no James Brown in the
table. Can you post back with an exact copy/paste of your condition
expression?

--
Steve Schapel, Microsoft Access MVP

Steve,

Thank you again for all your help. The [First Name] MsgBox Macro is working
perfectly. I used the same condition for the [category] (changing the field
names of course) and it seems to be working as well.

The MsgBox macro for the [Last Name] however does not seem to do anything.
I have double checked to make sure I copied it correctly and I have gone
through the expression piece by piece. It looks correct to me, but does
nothing on the After Update event. If I understand the condition correctly,
the asterisk at the beginning is to check all records in the table specified
(in this case my table is "Main"). The condition then checks the first name
against the first name entered and while at the same time checking the last
name against the last name of the same record. It looks correct and sound
correct when going through piece by piece, so I am not really sure why it
isn't working.

 

[Steve Schapel]

Risikio,

Is the name of the textbox that these names are being typed into
actually First Name and Last Name?

I assume these are unbound textboxes on a separate form, not related to
the actual data in the table? Maybe if you can explain a little about
the actual process here, it will help me understand the problem.

 

[Risikio]

Hi Steve,

On the form, the textboxes are unbound. The actual names of the textboxes
are [First Name] and [Last Name].

The database itself is really nothing more than an address book. The users
are required to print mailing labels from this database. The form is
intended to allow the user to either print a single label - [First Name] AND
[Last Name] OR a group of labels - [catergory]. These are the only three
fields/textboxes on the form and all are unbound. Below the textboxes are
three buttons - Print Labels which runs the original macro you helped me with
(null vs. blank) and then prints a report (the actual labels). The second
button resets the form, because I found that the textboxes would work fine
the first time through, but if the user simply deleted what was entered and
entered new data, the labels would not print. This mostly dealt with the
[category] field/textbox. The third button simply closes the form.

I am not sure if the following information will help, but I have included it
just in case it provide some extra light on the subject.

There are four tables in the database - categories, Main, Switchboard Items,
Titles.
There is only one query - Label
There are three forms - Label (the enter information into the database),
Criteria (to enter information to print labels - This is the form we are
discussing), Switchboard.
There is only one report - Labels

The "Main" table has the following fields - Title, First Name, Last Name,
Address, Address2, City, State, Zip, and category.

The query for the report has the following fields - Name: [Title] & " " &
[First Name] & " " & [Last Name]), Address, Address2, CityStateZip: [City] &
" " & [State] & " " & [Zip]), Zip (not shown but sorted ascending for bulk
mailing), category (in the or: row of the criteria
[Forms]![Criteria]![category]), First Name (not shown but in the criteria:
row [Forms]![Criteria]![First Name]), and Last Name (not shown but in the
criteria: row [Forms]![Criteria]![Last Name])

Thanks again for all of your help.

Risikio,

Is the name of the textbox that these names are being typed into
actually First Name and Last Name?

I assume these are unbound textboxes on a separate form, not related to
the actual data in the table? Maybe if you can explain a little about
the actual process here, it will help me understand the problem.

--
Steve Schapel, Microsoft Access MVP

Steve,

Here is the exact copy/paste of the condition.

DCount("*","Main","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0

 

[Steve Schapel]

Risikio,

Thank you for the very full explanation. I am sorry, there must be a
simple explanation, but for now I can't see where the problem lies. It
seems to me that everything is clear and correct, and it "should" work
as expected.

If you like, you can email me the (preferably zipped) .mdb file and I
will have a look at it. Send it to steves at mvps dot org

 

[Risikio]

Steve,

I will attmept to send the database as a zipped file. The email will come
from jrisk at hsc dot wvu dot edu. Please let me know if you do not receive
it. Our email system (groupwise) has a tendency to strip off certain
attachments because it thinks it contains a virus. Hopefully, zipping the
file will prevent this.

Thanks.

Risikio,

Thank you for the very full explanation. I am sorry, there must be a
simple explanation, but for now I can't see where the problem lies. It
seems to me that everything is clear and correct, and it "should" work
as expected.

If you like, you can email me the (preferably zipped) .mdb file and I
will have a look at it. Send it to steves at mvps dot org

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

On the form, the textboxes are unbound. The actual names of the textboxes
are [First Name] and [Last Name].

The database itself is really nothing more than an address book. The users
are required to print mailing labels from this database. The form is
intended to allow the user to either print a single label - [First Name] AND
[Last Name] OR a group of labels - [catergory]. These are the only three
fields/textboxes on the form and all are unbound. Below the textboxes are
three buttons - Print Labels which runs the original macro you helped me with
(null vs. blank) and then prints a report (the actual labels). The second
button resets the form, because I found that the textboxes would work fine
the first time through, but if the user simply deleted what was entered and
entered new data, the labels would not print. This mostly dealt with the
[category] field/textbox. The third button simply closes the form.

I am not sure if the following information will help, but I have included it
just in case it provide some extra light on the subject.

There are four tables in the database - categories, Main, Switchboard Items,
Titles.
There is only one query - Label
There are three forms - Label (the enter information into the database),
Criteria (to enter information to print labels - This is the form we are
discussing), Switchboard.
There is only one report - Labels

The "Main" table has the following fields - Title, First Name, Last Name,
Address, Address2, City, State, Zip, and category.

The query for the report has the following fields - Name: [Title] & " " &
[First Name] & " " & [Last Name]), Address, Address2, CityStateZip: [City] &
" " & [State] & " " & [Zip]), Zip (not shown but sorted ascending for bulk
mailing), category (in the or: row of the criteria
[Forms]![Criteria]![category]), First Name (not shown but in the criteria:
row [Forms]![Criteria]![First Name]), and Last Name (not shown but in the
criteria: row [Forms]![Criteria]![Last Name])

 

[Steve Schapel]

James,

I received the database you sent. Thanks.

On the After Update event of the Last Name control, you have the macro
MsgBox2. The Condition is like this...
DCount("*","Label","[Last Name]='" & [Last Name] & "'")=0

There is no table or query named "Label". I changed the Condition to
use your Main table, as we discussed...
DCount("*","Main","[Last Name]='" & [Last Name] & "'")=0
.... and it then works fine. I also tested, as per our earlier
discussion, to validate both the Last Name and First Name on the After
Update event of the Last Name control, so tried the macro Condition like
this...
DCount("*","Main","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0
.... and this also works as expected.

 

[Risikio]

Steve,

Sorry about the macro being incorrect. I had played around with it and
changed it to resemble the marco on the [First Name] to see if it would work
that way and forgot to change it back. My apologies for that error.

However, I have copied the correct condition directly from your post and
pasted it into the condition box of the MsgBox2 Macro and it still isn't
working for me. I am guessing then that it has to be something on my
computer? I am running Access 2003 if that makes any difference.

James,

I received the database you sent. Thanks.

On the After Update event of the Last Name control, you have the macro
MsgBox2. The Condition is like this...
DCount("*","Label","[Last Name]='" & [Last Name] & "'")=0

There is no table or query named "Label". I changed the Condition to
use your Main table, as we discussed...
DCount("*","Main","[Last Name]='" & [Last Name] & "'")=0
.... and it then works fine. I also tested, as per our earlier
discussion, to validate both the Last Name and First Name on the After
Update event of the Last Name control, so tried the macro Condition like
this...
DCount("*","Main","[First Name]='" & [First Name] & "' And [Last
Name]='" & [Last Name] & "'")=0
.... and this also works as expected.

--
Steve Schapel, Microsoft Access MVP

Steve,

I will attmept to send the database as a zipped file. The email will come
from jrisk at hsc dot wvu dot edu. Please let me know if you do not receive
it. Our email system (groupwise) has a tendency to strip off certain
attachments because it thinks it contains a virus. Hopefully, zipping the
file will prevent this.

 

[Steve Schapel]

Risikio,

All I can think of now, is that maybe the copy/paste from the newsgroup
post is messing something up. For example, depending on how you are
viewing the newsgroup, the newsreader may be inserting a line wrap, or
changing the format of the " marks, or something. So, try re-typing it
into the macro condition rather than copy/paste. It has to be something
very simple.

 

[Risikio]

Steve,

Thank you for staying with me and working through this. The macro works
fine once I typed it in. Whatever the copy/paste did, must have been the
problem from the begininng since you first gave me the macro, because I think
I did a copy/paste originally.

Everything works fine now. Thank you very much for all your help.

Risikio,

All I can think of now, is that maybe the copy/paste from the newsgroup
post is messing something up. For example, depending on how you are
viewing the newsgroup, the newsreader may be inserting a line wrap, or
changing the format of the " marks, or something. So, try re-typing it
into the macro condition rather than copy/paste. It has to be something
very simple.

--
Steve Schapel, Microsoft Access MVP

Steve,

Sorry about the macro being incorrect. I had played around with it and
changed it to resemble the marco on the [First Name] to see if it would work
that way and forgot to change it back. My apologies for that error.

However, I have copied the correct condition directly from your post and
pasted it into the condition box of the MsgBox2 Macro and it still isn't
working for me. I am guessing then that it has to be something on my
computer? I am running Access 2003 if that makes any difference.

 

[Steve Schapel]

Thanks for letting me know, James. It is a relief to know we were
approaching it correctly, even though we had that difficulty. The
property settings assume a plain text entry, and I guess the newsreader
can make all sorts of changes to the structure of text as typed.