Validation Rule Question

[Dermot Hayes]

I posted a question a few weeks ago regarding validation
rules. I have looked into the answer I got but am still no
further on can anyone help please. I am only a beginner!
My question:- If I create a table with a field called Job
number etc. How do I do I write a vaildation rule so that
when an incorrect job number or rubbish is entered into
the parameter query instead of a valid job number, a
message box is displayed saying "Please Enter a Valid Job
Number"

The answer I recieved is as follows:-
You cannot vaildate the value entered in a queries
parameter prompt.
It is better to create a form that will provide criteria
for the query. The query can use the forms control's
reference as the parameter e.g.
PARAMETERS Forms!frmcriteria!textJobNumber Long:
SELECT....
FROM
WHERE Job Number= Forms! frmcriteria!txtJobNumber

The form has to be open for the query to work.

This methodology has been described many times in this and
other Access news groups. It is also described in the
Access help article Parameter Queries: Create a parameter
Query.

I have read the suggested articles and can't figure out
where I am supposed to enter the above information when I
am creating a form.e.g
PARAMETERS
SELECT....
FROM....
WHERE ETC...
What do I type in the select and From Options above?

Am I trying to do this the correct way? I want to call up
only a Valid Job Number Record,(when someone clicks on
a "NEXT JOB" button on the form I have created), from the
table of records I have created. If an invalid Job
Number / rubbish which is not in the table is is called
up, I would like a prompt
to Say "Please Enter a valid Job Number"
Please Help
Thanks
Dermot

 

[Kevin Sprinkel]

I think I would handle this by using a combo box, assuming
that Job Number is the key field of some application
table.

If you're new to Access, you can use the combo box wizard
to make this easy. In Form Design view, show the Toolbox
by selecting View, Toolbox. Depress the Wizard toggle
button, which looks like a wand and starts. Now place a
new combo box on your form. Tell it to look up values
from your Job Number table, and select the Job Number
field, and "Save this value for later use."

Set the Limit To List property to Yes to only allow valid
job numbers to be entered, and will show a message box if
a non-valid number is added. You can show your own custom
message by adding an event procedure for the combo box' On
Not In List Property. If you've never done this before,
it's not hard.

From form design view, right click on your combo box and
choose Properties. Choose the Event tab, and find the On
Limit To List property. Click in the field to display the
two buttons to the right. Choose the Builder button
(...), and choose Code Builder.

Access will create a procedure shell. Add the following
code:

MsgBox("Please enter a valid job number.")

Save, and close the Visual Basic window. You should now
see [Event Procedure] in the On Limit To List property.
Save your form.

Good luck.

KS

 

[Guest]

It sounds like you don't need a validation rule, but a relationship between the a "Table" of job numbers (assuming anything that gets put in there is valid). When you build the query make sure you define a relationship between the table being created/modified by the form/query you are writing and the "Table of Job Numbers" previous created and loaded up. You do this by using the "Tools" menu at the top of the Access main page select "Relationships" and created a relationship between the Job Numbers Table and the other Table you are creating. Create a relationship on Job number and the job number field in the query you have written. Select "inforce referential integrity" . This means only records you add in you new query/form that contain valid (pre-existing) job number found in the "Job Number Table" will be accepted. Anything else will generate an obnoxious error message. You can also create a lookup on/in your form/query so that users can only select from what's already in the "Table of Job Numbers".

 

[Dermot Hayes]

The Job numbers are all in a table. The combo box would be
too long for an engineer to select the record he is
looking for. He knows the job number for the record he is
looking for. I thought I could use a "Next Job" command
button to generate a parameter query "Enter Job Number"
which would in some way compared the entered number with
the numbers in the table and would thee come up with the
relevant record. If a number is entered that is not in the
table the a message would say " This is an Invalid Job
Number" or "Please Enter a Valid Job number" etc....

Any help please
Thanks in advance
Dermot Hayes

-----Original Message-----
I think I would handle this by using a combo box, assuming
that Job Number is the key field of some application
table.

If you're new to Access, you can use the combo box wizard
to make this easy. In Form Design view, show the Toolbox
by selecting View, Toolbox. Depress the Wizard toggle
button, which looks like a wand and starts. Now place a
new combo box on your form. Tell it to look up values
from your Job Number table, and select the Job Number
field, and "Save this value for later use."

Set the Limit To List property to Yes to only allow valid
job numbers to be entered, and will show a message box if
a non-valid number is added. You can show your own custom
message by adding an event procedure for the combo box' On
Not In List Property. If you've never done this before,
it's not hard.

From form design view, right click on your combo box and
choose Properties. Choose the Event tab, and find the On
Limit To List property. Click in the field to display the
two buttons to the right. Choose the Builder button
(...), and choose Code Builder.

Access will create a procedure shell. Add the following
code:

MsgBox("Please enter a valid job number.")

Save, and close the Visual Basic window. You should now
see [Event Procedure] in the On Limit To List property.
Save your form.

Good luck.

KS

-----Original Message-----
I posted a question a few weeks ago regarding validation
rules. I have looked into the answer I got but am still no
further on can anyone help please. I am only a beginner!
My question:- If I create a table with a field called Job
number etc. How do I do I write a vaildation rule so that
when an incorrect job number or rubbish is entered into
the parameter query instead of a valid job number, a
message box is displayed saying "Please Enter a Valid Job
Number"

The answer I recieved is as follows:-
You cannot vaildate the value entered in a queries
parameter prompt.
It is better to create a form that will provide criteria
for the query. The query can use the forms control's
reference as the parameter e.g.
PARAMETERS Forms!frmcriteria!textJobNumber Long:
SELECT....
FROM
WHERE Job Number= Forms! frmcriteria!txtJobNumber

The form has to be open for the query to work.

This methodology has been described many times in this and
other Access news groups. It is also described in the
Access help article Parameter Queries: Create a parameter
Query.

I have read the suggested articles and can't figure out
where I am supposed to enter the above information when I
am creating a form.e.g
PARAMETERS
SELECT....
FROM....
WHERE ETC...
What do I type in the select and From Options above?

Am I trying to do this the correct way? I want to call up
only a Valid Job Number Record,(when someone clicks on
a "NEXT JOB" button on the form I have created), from the
table of records I have created. If an invalid Job
Number / rubbish which is not in the table is is called
up, I would like a prompt
to Say "Please Enter a valid Job Number"
Please Help
Thanks
Dermot
.

.

 

[John Spencer (MVP)]

As long as there are less than 65,000 jobs in the table, you can use a combobox
to get the value. You just let the user type in the value to be found. If
you've set up the combobox as described then you should not have any problem.

ANother choice is to check the value from a textbox using the DCount function
(or DLookup or ...)

In the code of the button you click to execute the search.

If DCOUNT("*","JobsTable","JobNumber=" & me.JobNumberControl) = 0 Then
MsgBox "Invalid Job Number. Try again!"
Else
'Run your query, open your report, or open your form
End if

The Job numbers are all in a table. The combo box would be
too long for an engineer to select the record he is
looking for. He knows the job number for the record he is
looking for. I thought I could use a "Next Job" command
button to generate a parameter query "Enter Job Number"
which would in some way compared the entered number with
the numbers in the table and would thee come up with the
relevant record. If a number is entered that is not in the
table the a message would say " This is an Invalid Job
Number" or "Please Enter a Valid Job number" etc....

Any help please
Thanks in advance
Dermot Hayes

-----Original Message-----
I think I would handle this by using a combo box, assuming
that Job Number is the key field of some application
table.

If you're new to Access, you can use the combo box wizard
to make this easy. In Form Design view, show the Toolbox
by selecting View, Toolbox. Depress the Wizard toggle
button, which looks like a wand and starts. Now place a
new combo box on your form. Tell it to look up values
from your Job Number table, and select the Job Number
field, and "Save this value for later use."

Set the Limit To List property to Yes to only allow valid
job numbers to be entered, and will show a message box if
a non-valid number is added. You can show your own custom
message by adding an event procedure for the combo box' On
Not In List Property. If you've never done this before,
it's not hard.

From form design view, right click on your combo box and
choose Properties. Choose the Event tab, and find the On
Limit To List property. Click in the field to display the
two buttons to the right. Choose the Builder button
(...), and choose Code Builder.

Access will create a procedure shell. Add the following
code:

MsgBox("Please enter a valid job number.")

Save, and close the Visual Basic window. You should now
see [Event Procedure] in the On Limit To List property.
Save your form.

Good luck.

KS

-----Original Message-----
I posted a question a few weeks ago regarding validation
rules. I have looked into the answer I got but am still no
further on can anyone help please. I am only a beginner!
My question:- If I create a table with a field called Job
number etc. How do I do I write a vaildation rule so that
when an incorrect job number or rubbish is entered into
the parameter query instead of a valid job number, a
message box is displayed saying "Please Enter a Valid Job
Number"

The answer I recieved is as follows:-
You cannot vaildate the value entered in a queries
parameter prompt.
It is better to create a form that will provide criteria
for the query. The query can use the forms control's
reference as the parameter e.g.
PARAMETERS Forms!frmcriteria!textJobNumber Long:
SELECT....
FROM
WHERE Job Number= Forms! frmcriteria!txtJobNumber

The form has to be open for the query to work.

This methodology has been described many times in this and
other Access news groups. It is also described in the
Access help article Parameter Queries: Create a parameter
Query.

I have read the suggested articles and can't figure out
where I am supposed to enter the above information when I
am creating a form.e.g
PARAMETERS
SELECT....
FROM....
WHERE ETC...
What do I type in the select and From Options above?

Am I trying to do this the correct way? I want to call up
only a Valid Job Number Record,(when someone clicks on
a "NEXT JOB" button on the form I have created), from the
table of records I have created. If an invalid Job
Number / rubbish which is not in the table is is called
up, I would like a prompt
to Say "Please Enter a valid Job Number"
Please Help
Thanks
Dermot
.

.