Look up specific record for edit purpose

[Beth McLaren]

I would like to make a form that will allow the user to look up a specific
record in my DB from a form - I would like to allow the user to type in what
they would like rather than having a predetermined list of items, as it could
be fairly long. The categories I would include would give them separate
options to look up by one of the following:
1) Annoucement Number
2) Position Name
3) HR Specialist Name
I envision having them type in for example:

Annoucement Number: (they type in 114)

Then it would automatically go to the form that references it. They would
be able to see it or edit it if they needed.....
I assume this could be a query that I would need to make and put it within a
form but I need some direction on whether or not this is what I need....any
suggestions are helpful!
Thank you!
Beth

 

[Jeff Boyce]

Beth

Can I assume that you are looking for a way to have your user find one kind
of record, depending on the value in one of those three fields?

Or do you have multiple tables that need to be searched, and the possibility
of one, two, or three search criteria?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beth McLaren]

I would like the form to have the ability for the user to pick 1 of three
ways to look for the information, but the information would be within 1 table
- the following fields would be in the table:

1) Annoucement Number
2) Position Name
3) HR Specialist Name

They could use any of those criteria to pull up the information needed that
would reside in one form from 1 table. Sometimes they may have only the
annoucement number and not the position name or the HR Specialist name....or
vice versa....does this make sense??

Beth

 

[Jeff Boyce]

Beth

Thanks for the clarification...

"How" depends on "what" -- what are the fieldnames in your table, and the
table's name?

Generically, one approach would be to create a form that uses an option
group with those three radio buttons (forced choice, one only), a field to
fill in the search value, plus a command button. When the command button is
clicked, the event procedure "behind" it:
* determines which radio button is selected
* creates a SQL "WHERE" statement "on the fly" (e.g., "WHERE
[AnnouncmentNumber] Like '*" & Me!txtYourSearchValueField & "*'")
* generates a complete SQL statement, using the WHERE clause
* runs that SQL statement
* displays the results, perhaps in a listbox on your form

Now, that said, I find that users RARELY remember things like announcement
numbers or the exact spelling of Position or HR names, so requiring them to
get it right is potentially troublesome.

Another approach might be to use comboboxes for each one of those fields.
This way, the user gets to SELECT the one they're after. (and you can turn
on the auto-complete property of the combobox, so if they get the first part
of it correct, Access helps them...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Philip Herlihy]

Beth

Thanks for the clarification...

"How" depends on "what" -- what are the fieldnames in your table, and the
table's name?

Generically, one approach would be to create a form that uses an option
group with those three radio buttons (forced choice, one only), a field to
fill in the search value, plus a command button. When the command button is
clicked, the event procedure "behind" it:
* determines which radio button is selected
* creates a SQL "WHERE" statement "on the fly" (e.g., "WHERE
[AnnouncmentNumber] Like '*" & Me!txtYourSearchValueField & "*'")
* generates a complete SQL statement, using the WHERE clause
* runs that SQL statement
* displays the results, perhaps in a listbox on your form

Now, that said, I find that users RARELY remember things like announcement
numbers or the exact spelling of Position or HR names, so requiring them to
get it right is potentially troublesome.

Another approach might be to use comboboxes for each one of those fields.
This way, the user gets to SELECT the one they're after. (and you can turn
on the auto-complete property of the combobox, so if they get the first part
of it correct, Access helps them...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like the form to have the ability for the user to pick 1 of three
ways to look for the information, but the information would be within 1
table
- the following fields would be in the table:

1) Annoucement Number
2) Position Name
3) HR Specialist Name

They could use any of those criteria to pull up the information needed
that
would reside in one form from 1 table. Sometimes they may have only the
annoucement number and not the position name or the HR Specialist
name....or
vice versa....does this make sense??

Beth

Have a look at this:

http://allenbrowne.com/ser-62.html

Phil, London

 

[KenSheridan via AccessMonster.com]

Beth:

Firstly I would advise that you do use combo boxes rather than text boxes for
the user to enter the parameter. They can still type the value in, in which
case the control will go to the first match progressively as each character
is entered, but at the same time they can only enter a value which is present
in the database, so for the Announcement Number combo box for instance the
RowSource property would be along these lines:

SELECT [Announcement Number] FROM [Announcements] ORDER BY [Announcement
Number];

This assumes that there is a table Announcements in which the Announcement
Number values are distinct. If not, and you only have a table which can have
multiple instances of the same number, then you can still do it with:

SELECT DISTINCT [Announcement Number] FROM [Announcements] ORDER BY
[Announcement Number];

That would imply a design flaw in the table, however as with that scenario it
should reference a table with distinct values of each number to ensure valid
data by enforcing referential integrity.

The form would have three unbound controls for entering the parameters, along
with controls bound to the columns (fields) in the table. The form would be
based on a query which references the unbound control as parameters, but
optionalizes each by testing for OR <parameter> IS NULL, so the query would
be along these lines:

SELECT *
FROM YourTable
WHERE ([Announcement Number] = Forms![YourForm]![cboAnnouncementNumber]
OR Forms![YourForm]![cboAnnouncementNumber] IS NULL)
AND ([Position Name] = Forms![YourForm]![cboPositionName]
OR Forms![YourForm]![cboPositionName] IS NULL)
AND ([HR Specialist Name] = Forms![YourForm]![cboHRSpecialistName]
OR Forms![YourForm]![cboHR SpecialistName] IS NULL);

Each parenthesised expression will evaluate to TRUE for those rows with that
value in the relevant column if a value is selected, or for all rows if no
value is selected in the relevant control. So a user can optionally select
from any of the three controls

In the AfterUpdate event procedure of each of the three unbound controls set
the other two to Null and requery the form with, in the case of the
cboAnnouncementNumber control for instance:

[cboPositionName] = Null
[cboHRSpecialistName] = Null
Me.Requery

Ken Sheridan
Stafford, England