Lookup Value From Multiple Tavle (1 Viewer)

[Razon09]

Hi. So, I have a table let's call it Table A. Table A have several field, Parts ID, and P Number.
I also have Table B which has Parts ID and Parts Name Field.
And Lastly, I have Table C which has P Number and Parts ID as primary key, and Remaining Qty which is a calculated field.

What I want to do is to create a form that need to input Parts ID, and P Number. But the form will automatically fill Parts Name, and Remaining Qty which value is from Table B and C.

I Tried to do it with Lookup Wizard and it's working for 2 Table only. Either Table A with Table B, or Table A with Table C.

Is there any way to provide this?

Thank you.

 

[Pat Hartman]

When I need "lookup" values from other tables, I bind the form to a query that joins all the necessary tables. You will need to use a left join if the right side values are optional. The other important point is that you do NOT want to allow "lookup" values to be changed on a form that is intended to update a different table. Therefore, you should always set the lock property to the "lookup" controls to yes to prevent an accident from happening. The only unlocked controls on the form should be for table A.

PS, [P Number] should exist only in table C. I also do not know why [P Number] and [Parts Name Field] are defined in separate tables. The table with the PK of [Parts ID] should contain all the attributes that are defined by that ID which at a minimum would be the number and name fields.

And finally, creating column names that include embedded spaces or special characters violates the norms of every programming language I know and VBA is no exception. Just because Access allows you to create column names that violate standards for creating variable names in VBA doesn't mean that it is a good idea. Remember, you are working in a database application, not Excel where the presentation and data layers are merged. With Access, what you call objects and variables internally does not control what the user sees on forms and reports, or even on exports to Excel. So, go with "best practice" name creation and save yourself trouble with VBA.