|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Look up specific record for edit purposerecord 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 McLaren 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 Show quoteHide quote "Beth McLaren" <BethMcLa***@discussions.microsoft.com> wrote in message news:EDDF0DC1-1E9E-4DEB-B256-5D992204BBED@microsoft.com... >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 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 -- Show quoteHide quoteBeth McLaren "Beth McLaren" wrote: > 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 McLaren 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 Show quoteHide quote "Beth McLaren" <BethMcLa***@discussions.microsoft.com> wrote in message news:533D63F5-6AAC-4997-8F40-7FC6891D5ECA@microsoft.com... >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 McLaren > > > "Beth McLaren" wrote: > >> 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 McLaren Jeff Boyce wrote:
Show quoteHide quote > Beth Have a look at this:> > 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" <BethMcLa***@discussions.microsoft.com> wrote in message > news:533D63F5-6AAC-4997-8F40-7FC6891D5ECA@microsoft.com... >> 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 McLaren >> >> >> "Beth McLaren" wrote: >> >>> 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 McLaren > > http://allenbrowne.com/ser-62.html Phil, London 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 Beth McLaren wrote: Show quoteHide quote >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1  
Other interesting topics
DCOUNT??
new query or different filter Leading zero's in number fields Table relationship design conditional formatting vba code for a split/datasheet form? How to make table, query, etc. list stable. does not match the defined precision of the column New Database, trying to determine keys and relationships Photo not showing Need help setting up a simple DB |
|||||||||||||||||||||||
