have input field in form that will search & Populate form with record

[jcnoble]

I am a newbie to this site and access so please forgive me if I don't do this totally correctly.

I have a table -tblPartNumbers - which list a desc and four fields with part numbers for that description which different companies use for that part, i.e. Widget; 02.3659.33; UC20003699; 04.2222.002;3lk99. All Part numbers are unique, there are NO duplicate numbers anywhere.

I used autoform to create a form - frmPartNumber - which showed five fields: Description, Company ABC part number, Company 123 part number, Company XYZ part number & Company 789 part number.

What I would like to do and NEED HELP doing, is put a search field in the form -frmPartNumber -such that I could type in a part numberin search field and the five fields of information for the record with that part number in it would then populate my frmPartNumber form. This is like the "by example" LookUp in the software program Called ACT.

Thank you in advance for your help.

Charles

 

[grtammi]

Charles:

Are you experienced with SQL and VBA at all? If so, post your e-mail address and I'll send you a copy of a search form I've built, which is along similar lines.

HTH

Greg


Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[ReluctantDataGuy]

I usually add an unbound combo box in the form's header.

If you want to "zoom" to a record while the remainder of the records remain accessible, then you can use the Find function.

If you want to filter using wildcards, then you could use Filtering.

Personally - I like to set the forms recordset using the AfterUpdate event of the combo box, and then giving the user a command button [Show All] to get the whole recordset back. (Obviously, if your recordsets are huge, this would be an unecessary hit to the server).

So, create a combo box (cboLookup for explanatory purposes) and "fill it" (RowSource) with a table/query that returns all your part numbers. I like to give the user something more than a number to pick from, so I usually make the combo box have 2 columns - the 2nd column with some more identifiable information - like part name or description (if its not too long).

In the AfterUpdate event of the combo box you write something like:

Me.RecordSource = "SELECT * FROM tblPartNumbers WHERE [Company1Part#] = " & Me.cboLookup & " Or [Company2Part#] = " & Me.cboLookup & " Or [Company3Part#] = " & Me.cboLookup & " Or [Company4Part#] = " & Me.cboLookup


Then you need to give the user a way to restore the entire recordset if they want to scroll through the list. So add a command button and on its OnClick event add:

Me.RecordSource = "SELECT * FROM tblPartNumbers"

As noted above, you can adapt the combo box solution to the Find function or for Filtering the form. The key is to use the AfterUpdate event of the combo box.

BTW: If you use the same form to add new part numbers, then you'll probably want to requery cboLookup in the form's AfterInsert event.

 

[jcnoble]

Thank the two people who have responed. I don't know how to e-mail them directly so I am reponding with this.

I do not know any SQL or VBA. I need an answer the would be step by step instructions using access keys/buttons. I repeat, NO VBA or SQL because it does me no good.

Thanks for any help.

Charles

 

[dgwilliams]

hope this helps

you have two options that are very simple.

1.) use the access search facility provided. to do this click in the unique field and then press the search button in the toolbar. looks like a pair of binoculars. type the id and the record will be displayed on the form. You can add this button to the form using a command button.

2.) when the form is in design view right click on the unique field and then changeto and then combo box. this will make the field a combo box. so whenever you type in a unique id into that field or select it from a list the other fields will fill in automatically.