Look up specific record for edit purpose

  • Thread starter Thread starter Beth McLaren
  • Start date Start date
B

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
 
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
 
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
 
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
 
Jeff said:
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


Beth McLaren said:
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
Click to expand...


Have a look at this:

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

Phil, London
Click to expand...
 
Back
Top