validation rule with msgbox using macros

[moonlight]

Hi,

I have Form1 which has command button, when click it will open the Form2
which is built based on Query. This query has 2 fields {ID and Name} the
criterion is under the Name field. The criteria asked (after clicking the
button in Form1) the user to enter the Name that he need to show in Form2.In
my case the form2 will open all the time if the Name in the query the Form2
will show it, and if it was not on the query also Form2 will open empty.

How to, make Form2 open only if the Name is in query and if not it will give
me error message?

Very important i need help

 

[Steve Schapel]

Moonlight,

It is preferable to put an unbound textbox on your form, where the user
can enter the name criteria, and then, in the query, replace your
paramter prompt in the criteria of the name field with a reference to
the textbox, using syntax such as:
[Forms]![NameOfForm]![NameOfTextbox]

In the macro, you can put this in the Condition of your OpenForm action:
DCount("*","YourQuery")>0

If you don't see a Condition column in the macro design window, select
it from the View menu.

By the way, as an aside, 'name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or databse object.

 

[moonlight via AccessMonster.com]

Thank you for your help.


How I can give an error message if the name that I enter is not listed in
the name field ??
and how to make the second form not open in this case??

Very Important.


My Regards,

 

[Steve Schapel]

Moonlight,

You could add a MsgBox action to your macro, to say "No matching name"
or some such. The Condition for this would be the opposite of the
Condition given for the OpenForm action, i.e.
DCount("*","YourQuery")=0

 

[moonlight via AccessMonster.com]

Thank you very much

I used the validation Rule for the textbox(by puting all the possible values)
, but the problem is that (the validation rule)take limit size of chracters
and I have alot of values to put?

is there another way to do it(not puting all the possible values)?


sorry for bad english and thank you again

My Regards

 

[Steve Schapel]

Moonlight,

I know that the Subject of your message here did mention validation
rule. But I don't think it is a good idea to try and manage this with
validation rule. My suggestion is something else. My suggestion is to
manage it via the Condition of the macro. Did you understand what I
told you before about Condition:
DCount("*","YourQuery")>0
In this case, you replace the "YourQuery" with the name of the query
that the Form2 is based on. And there is criteria in this query that
restrict the query data by the name entered in the criteria textbox. So
therefore if there is no record in the query, it means the name does not
match, and therefore the macro condition means the form only opens if
there is a matching name. So just forget the validation rule idea.

 

[moonlight via AccessMonster.com]

hi,
thank you again,

Yes I understand what you told me before about ConditionCount("*",
"YourQuery")>0 , and it works fine.
I thought in validation rule because I need error message and I try DCount
("*","YourQuery")=0 to put it in the same macro that I use DCount("*",
"YourQuery")>0 in,the problem is that the action ( MsgBox) do not has
condition to use DCount("*","YourQuery")=0 in.

If I understand you correct, what is the Solution ?

My Regards,

 

[Steve Schapel]

Moonlight,

When you look at design window for the macro, do you see a column with
'Condition' heading, to the left of the 'Action' column? If not, please
go to the View menu, and select Conditions there.

Then, in constructing your macro, this is what I am proposing:

Condition: DCount("*","YourQuery")=0
Action: MsgBox

Condition: DCount("*","YourQuery")>0
Action: OpenForm

Hope that will be clearer.

 

[moonlight via AccessMonster.com]

Thank you very much is working fine.

I have another problem; I work in another form, shows different departments I
set them as option group. In this form the user will click on the department
then it will search (in this department) for a person work in the chosen
department. How I can use DCount("*","YourQuery")>0 to help me in
showing that person information in second form (who works in the chosen
department)?

Thank you again,

Sorry for my bad English

 

[Steve Schapel]

Moonlight,

Well, "YourQuery" in this case will need to be a query that selects the
employees according to the department selected in the Option Group.

However, there will be an extra step for you here, probably. Due to the
fact that an Option Group has a numerical value, according to the Option
Value property of the selected Option Button within the Option Group.
So you will need to have a table that you can join into your query,
where each Department is allocated a number to correspond with the
Option Group number. Hope you understand.

 

[moonlight via AccessMonster.com]

Hi Mr. Steve,

Yes, I have query which depending on the value (Department ID In my Case)of
the Option Group , but that will show all employees in that department, what
I need now is to search for one employer in this department ? I hope this
figure help you to understand what I am trying to do.



My Query Form1
Form2
{ Depending on Option Group
to chose employer
option Group value <== Command Button ==>
from chosen Dep.
(Department ID)} {I put DCount("*","My Query")>0)
TextBox (to chose emp.
And open Form2}
& open Form3)



==> Form3 (Show all information of the emp.)


I hope you undrstand me...........

 

[moonlight via AccessMonster.com]

Hi Mr. Steve,

Yes, I have query which depending on the value (Department ID In my Case)of
the Option Group , but that will show all employees in that department, what
I need now is to search for one employer in this department ? I hope this
figure help you to understand what I am trying to do.



My Query Form1
Form2
{ Depending on Option Group
to chose employer
option Group value <== Command Button ==>
from chosen Dep.
(Department ID)} {I put DCount("*","My Query")>0)
TextBox (to chose emp.
And open Form2}
& open Form3)



==> Form3 (Show all information of the emp.)


I hope you undrstand me...........

 

[moonlight via AccessMonster.com]

Hi Mr.Steve,

Yes, I have query which depending on the value (Department ID In my Case)of
the Option Group , but that will show all employees in that department, what
I need now is to search for one employer in this department ? I hope this
figure help you to understand what I am trying to do.



My Query Form1
Form2
{ Depending on Option Group
to chose employer
option Group value <== Command Button ==>
from chosen Dep.
(Department ID)} {I put DCount("*","My Query")>0)
TextBox (to chose emp.
And open Form2}
& open Form3)



==> Form3 (Show all information of the emp.)


I hope you undrstand me...........

 

[moonlight via AccessMonster.com]

hi Mr.steve,

Sorry about three posting it was Ok but when I post them look like that, I
know You will not understand any thing know.

I will try another way.

 

[moonlight via AccessMonster.com]

Hi Mr. Steve,

Sorry again….I post it all again so you can understand better…….

Yes, I have query which depending on the value (Department ID In my Case)of
the Option Group , but that will show all employees in that department, what
I need now is to search for one employer in this department ? I hope this
will help you to understand what I am trying to do.


My Query==> Depending on option Group value (Department ID) which is from
Form1

Form1 ==> has Option Group to chose Department and Command Button {I put
DCount("*","My Query")>0) And open Form2 on event using macro}

Form2==> TextBox (to chose employer from the chosen Department) & open Form3

Form3==> (Show all information of the employer)

My Regards,

 

[Steve Schapel]

Moonlight,

Maybe on Form2 you can have a Combobox that lists all of the Employers
related to the selected Department. Then you can select the employer in
the combobox. And then Form3 is based on a query that uses the value of
the combobox in the criteria, so syntax of Criteria in the query for
Form3 looks something like this:
[Forms]![Form2]![NameOfEmployerCombobox]

Does that help?

 

[moonlight via AccessMonster.com]

Steve,

you are great It works fine now,but if you have another Idea (reather than
combobox) I like to know it.

Thank you Again.

 

[Steve Schapel]

Moonlight,

Well, you could use any other type of data control for the selection of
the employer, and reference this in the same way in Form3's query. But
I would assume there are too many employers for an Option Group to be
practical. And a textbox means the user must type the information,
which leaves more chance to enter it wrong. That's why I would prefer a
combobox. Also, with a combobox, you can set its Row Source to be more
than 1 column, so if the linking data is an ID number for example, this
can be the hidden Bound Column of the combobox, but the person's Name
(which may be more relatable for the user of your application) this is
what you will actially see in the combobox. If the combobox's Auto
Expand property is set to yes, and the user is typing a name, it becomes
very user-friendly. Hope that helps.

 

[moonlight via AccessMonster.com]

Steve,

When I WRITE a name (not in that department I chose before) in combobox,
Form3 will open with the name information.Because maybe I chose Department
ONE for example,but by mistake when I went to WRITE in combobox, I WRITE
name not in that department.

How I can stop that?

I hope I make my Quistion clear

My Regards,

 

[Steve Schapel]

Moonlight,

I think I understand the question. If you set the 'Limit To List'
property of the combobox to Yes, you won't be allowed to enter a name
that is not in the selected department.