Lookup Question

[lofgrass]

I have just started with MS Access and am pretty much self-taught so this may be a silly question…

I have created a work order database with 4 tables.
1. Work Orders – each time something goes wrong, I create a work order.
2. Vendors – the contractors that are fixing the work orders.
3. Sites – has the 20 properties I am watching over
4. Service Types – has a list of different types of works, like plumbing, electrical, duct work, etc.

QUESTION:
For the WORK ORDERS table, when I select a property and Service Type, is there a way for Access to show only the vendors that work for that Site, that Service Type, and are Active?
EX: Please see image:

Image is attached as zip.

Is this possible? If so, how? Please help!

 

[boblarson]

1. You don't define lookups at table level. That will only cause you grief.

Read here for more about why you should not use lookups directly in fields at table level:
http://www.mvps.org/access/lookupfields.htm

2. You can use lookups (combo boxes/list boxes) which use table data as their ROW SOURCE (the values that they are looking up and presenting as options to select from) but you do that on FORMS, not in tables.

 

[lofgrass]

hi, sorry, like i said im really new at this and have learned it all on my own with google and youtube...

when you say that i should do it on the forms, can you direct me to a website that might help teach me?

i just recreated my tables and created forms, but i cant find where on the form i can select the lookup option or attachments...

 

[boblarson]

When you create your form with no lookups in the table, you will need to convert the text boxes on the new form from text boxes to combo boxes. To do that right click on the text box and select CHANGE > To Combo.

Then you will need to set the row source (row source type should be Table/Query) by clicking on the ellipsis (...) next to the row source property. That will open up the query designer for you to select the lookup table you want (table where the values come from for the combo but not including the table where the data will eventually be stored).

Then be sure to set the combo's Column Count property to the number of columns in your row source query and the Column Width property to something like 0":2" which would hide the first column but display the second (handy for hiding the ID which you will be storing but show the text description so it is easy for the user to know what they have chosen).

Also, the combo's BOUND COLUMN property is by default 1 (the first column in the row source query) which means that it is the value of column 1 (which is normally the ID field which you will be storing). You can change that column number if the ID field actually falls into a different column in your row source query than in the first spot.

So, hopefully that helps.

 

[davey3389]

Is there away that the combo box can show 2 values when selected instead of 1?

 

[boblarson]

Is there away that the combo box can show 2 values when selected instead of 1?

Not really, except that in the combo's row source you can create a single field for the display which can be a concatenated version of the values but it will be hard to get them to line up too well (at least from my experience).

If you need to display multiple columns then a list box or subform is more the way to go.

 

[Simon_MT]

Bob,

Can we have a Sticky covering Combi-boxes. It gets asked time and time again and rather than the same question being re-iterated, could have a definite thread? I do mine solely as Functions others use subs and another method is DAO/ADO recordsets.

Simon

 

[boblarson]

Bob,

Can we have a Sticky covering Combi-boxes. It gets asked time and time again and rather than the same question being re-iterated, could have a definite thread? I do mine solely as Functions others use subs and another method is DAO/ADO recordsets.

Simon

If we could get our other sticky's read, it would be something to consider. But it would appear the exception, rather than the rule, that the Sticky is being read.