search field on form

[Jens]

Hi,
I have a search field on my form so our employees can quickly go to the job
record that they are currently working on. The job number is the primary key
for this form.
How can I prevent them from trying to call a job that does not exist? ie job
has not yet been created or a particular record has been deleted?

Code I am using to open the correct Job Number is:

DoCmd.OpenForm "tblJB2", , , "JobNumber = " & Me!RecordNUM

 

[louisjohnphillips]

Hi,
I have a search field on my form so our employees can quickly go to the job
record that they are currently working on. The job number is the primary key
for this form.
How can I prevent them from trying to call a job that does not exist? ie job
has not yet been created or a particular record has been deleted?

Code I am using to open the correct Job Number is:

DoCmd.OpenForm "tblJB2", , , "JobNumber = " & Me!RecordNUM

Click on the text edit where you currently capture RecordNUM. Right
mouse click and choose Change To from the pop-up menu. Choose change
to combobox.

Right mouse click on the newly morphosed combobox. Choose Properties
from the pop-up menu.
On the Data tab, specify:
--a Row Source Type of "Table/Query";
--a Row Source of "SELECT RecordNum, JobDescription from TblJB2";
--a Bound Column of 1;
--a Limit To List of Yes.
On the Format tab, specify:
--a Column Count of 2;
--Column Widths of "0;1.5".

Save the form.

When the user selects a JobDescription, an On Change even fires. Trap
this even with the code:

RecordNUM = Me.combo1.value

Use this RecordNum as the opening argument for the new form.

 

[Larry Linson]

You maybe have a search "Control"? Fields live in Tables and Queries, and
may be displayed in Controls on Forms or Reports. Sounds as if you have
someone entering numbers into a Text Box on the Form.

Extract the values from the Field you wish to search in a Query, use that
Query as Row Source for a Combo Box with its Limit to List property set to
Yes. If you wish, you can use the Not in List event to allow the user to
enter a new value... there are some good examples in Help, at
http://support.microsoft.com, and in various websites in the Resources List
at http://sp.ntpcug.org/accesssig.

Assuming you leave the default value for the AutoExpand property of the
ComboBox, it will scroll its drop-down list as the user types, and will
simply not accept a value that is not one of the items in the drop down
list... if you have event code for the Not in List event, it will execute
that code if the user insists on leaving the Combo Box with a value that
isn't in the list.

Larry Linson
Microsoft Office Access MVP