Re: Validation Rule More Advise Please.

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 03/19/04 

Date: Thu, 18 Mar 2004 19:39:24 -0500

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

Dermot Hayes wrote:
>
> 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
> >>.
> >>
> >.
> >