Can you join tables with more than one field? (1 Viewer)

[JudyH]

Fields in Table A:
Part# , Customer , ID# , Description

In my form, when I enter the Part# the query will fill in Customer, ID# , Description.

I ALSO want my query to fill in Part#, Customer, Description when I enter ID# in the appropriate field.

Is this possible?

 

[Jack Cowley]

Create your form and base it on your table. Place the fields you want to see on the form. Then create a combo box using the Wizard. On the first screen select the third item, "Find a record....". Follow the rest of the Wizards dialog boxes. When you are finished you will be able to either start typing in your Part # or select it from the combo box. When you exit the combo box the appropriate record will be displayed on your form....

[This message has been edited by Jack Cowley (edited 07-24-2001).]

 

[JudyH]

hmm....there are over 600 Part#'s in the table. That would be a really BIG combo box.

I want to be able to fill-in by using ID# also...

 

[Jack Cowley]

Yes, there will be a lot of Part#'s in the combo box but you do not have to search through the entire list to find what you want. If you start typing the Part # in the combo box Access will start Autofilling the combo box for you with the closest match to what you are inputing. If you have 2 Part Numbers, 123abc and 123mbc, when you type 1 Access will show you both numbers but as you continue to type it will continue to narrow down the possibilites until you have 123a and then it will show only that Part #. This has advantages over a plain text box because the user cannot type in a wrong Part #. You can, of course, use a plain text box in which the user can fill in the complete Part # and then search for that. Also, it you want to search by another field you can create a combo box for that as well and the user can then search by whichever method they prefer. I suggest you give the combo box method a try and if you do not like it you certainly can use any method that works best for you....

[This message has been edited by Jack Cowley (edited 07-25-2001).]

 

[JudyH]

I tried your suggestion and I believe it would work if all of the fields on the Form were from the same table
My problem is (I think) that the Part# field is the 'join' between two tables in a query
(one- to- many) and Access won't let me 'join' the ID# field also. When I do this I get no information in the query or on the form.

[This message has been edited by JudyH (edited 07-25-2001).]

 

[Jack Cowley]

JudyH -

Are we having fun yet?

The only join you need between your tables is the one you have. You should still be able create a combo box, using the Wizard, that will allow you to use the ID# as well to find a record on your form. If the ID# is a bound field on the form then it should work.

If your db is not proprietary, does not contain confidential information or is not huge I would be glad to take a quick look at it to see if I can get it to work. Just zip it up and email it to me. No quarantees that I can get it to work, but I will give it a shot.

Jack

 

[JudyH]

Learning Access is still fun , even when I have to read the instructions.
I've sent the database- let me know what you think!
thank you!

Judy