Complex Table "Lookup" Lists

[Rawb]

When working with plain 'ol Tables, how can you set the "Lookup" of a Field to return values based on the value of another Field in the same Record.

For example, I have the following three Tables like so:

Customer Table:

CustomerID|CustomerName
1|Rawb's Incredibly Expensive Imports
2|pbaldy's Second-rate Knockoffs

ShipTo Table:

CustomerID|ShipToID|Address1|Address2
1|1|100 Hoity Toity Ln|
2|1|123 Backalley St|Apt -1

Orders Table:

OrderID|OrderCustomerID|ShipToID
1|1|1
2|1|1
3|2|1
4|2|1

How do I get my Orders Table (when looking at the straight Table in Datasheet View) to only show the relevant ShipTo entries instead of all of them?

I've already tried entering the following into the Field's Lookup Row Source:

SELECT * FROM ShipTo WHERE [CustomerID]=" & [OrderCustomerID]

P.S.
I have an ENORMOUS amount of respect (and envy) for pbaldy. All usage of his name is done lightheartedly! >.>

[pbaldy]

Second-rate knockoffs?!? I'll have you know my knockoffs are all first-rate!

For starters, I personally never use table level lookup fields:

http://www.mvps.org/access/lookupfields.htm

To directly answer your question, I don't think that can be done at the table level (most of us don't let users work directly in tables anyway). That would typically be done with cascading combos on a form:

Baldy-Cascading Combos

[Rawb]

I'll respond to the points presented in the link (primarily because I'm INCREDIBLY OCD about things) first, and then respond to your post in general afterwards.

#1: A Lookup field ONLY displays the looked-up value if the Bound field's width is set to 0". In all other cases, the actual value stored in the field is displayed.

#2 - First part: This is kind of a "duh" statement. Any sorting/filtering by values not present in the Table won't work. However, with proper relationships, sorting can be accomplished through the use of Queries (which would still be necessary if a Lookup wasn't present).

#2 - Second part: I'm not even sure what they're saying. . . However all users will only have the Access Runtime and thus won't be able to change the Design of Forms in any way.

#3: I didn't know about this one. I can see how that could cause issues with bloat, corruption, and overall performance.

#4: I didn't know about this one either. In fact, I think this has solved several problems I've had with a previous database!

#5: Not true. Yes, Report and Form Objects default to a Combo Box, however they can be easily and quickly changed to a Textbox and still work perfectly. Once a Textbox, the additional lookup is no longer needed/used.

#6: The lookups are purely for my personal ease of use. And as the designer of the database, one would hope I already know "what is really happening" and have already set up "good relational methodology." The users will never have direct access to the Tables themselves.

#7: Again, I didn't know this one. However, since we're already working in a proprietary Microsoft data format, that assumption holds even without lookups :P

#8: What's a RWOP Query? Is it like a QWOP Query? >.>

#8 - Seriously: I assume they're talking about UAC/Workgroup security. I will not be implementing this type of security and, instead, will be using a combination of Windows integrated User Authentication and my own security through the use of a Users-type Table within the database itself.

Now that I've gone through all that, I'd like to say that it appears the disadvantages of using Table Lookups outweighs the benefits. Therefore, I will (*sigh*) be removing all Table Lookups from my database.

WAY TO RAIN ON MY PARADE!

[pbaldy]

WAY TO RAIN ON MY PARADE!

If you hadn't called my stuff second-rate...

RunWithOwnerPermissions

The general feeling among MVP's seems to be that there's nothing inherently wrong with using table level lookup fields, as long as you understand their quirks and limitations. The problem is that most people who use them don't understand those things. You seem to, so go for it!

[JennyHunt]

Hi Paul, Thank you so much for your website. Do you know of a sample that uses three cascading combos, the third dependant on the first two? Much appreciated.

[pbaldy]

Happy to help Jenny. Offhand I don't, but conceptually it would be no different. The third would either depend on the second or both the first two, depending on your specifics. Give it a try, and if you get stuck post the effort here and we'll fix it.

[JennyHunt]

Thanks! Here's what I tried:

Private Sub cboPropCode_AfterUpdate()
Dim strSource As String
Dim strSource2 As String
strSource = "SELECT DISTINCT Unit " & _
"FROM qry_Contacts " & _
"WHERE PropertyCode = '" & Me.cboPropCode & "' ORDER BY Unit"
Me.cboUnit.RowSource = strSource
Me.cboUnit = vbNullString
strSource2 = "SELECT DISTINCT Tenant " & _
"FROM qry_Contacts " & _
"WHERE Unit = '" & Me.cboUnit & "' ORDER BY Tenant"
Me.cboTenant.RowSource = strSource2
Me.cboTenant = vbNullString
End Sub

[Rawb]

Jenny,

If you set your Combo Boxes' .RowSource Property like shown below, all you should have to do is run a .Requery on the Combo Box each time it's changed.

Units Combo Box .RowSource:

SELECT DISTINCT [Unit] FROM qry_Contacts WHERE [PropertyCode]=[cboPropCode] ORDER BY [Unit]

Tennants Combo Box .RowSource:

SELECT DISTINCT [Tenant] FROM qry_Contacts WHERE [Unit]=[cboUnit] ORDER BY [Tenant]

Then, for cboPropCode's OnChange Event, just run:

Me!cboUnit.Requery

And for cboUnit's OnChange Event, run:

Me!cboTennant.Requery

[JennyHunt]

Thanks! The units can repeat for a property (ie more than one property could have a unit 1) so the tenant is dependant on the unit and propcode. Does this resolve that problem?

[JennyHunt]

I tried something like this and ran into that problem.

[Rawb]

Thanks! The units can repeat for a property (ie more than one property could have a unit 1) so the tenant is dependant on the unit and propcode. Does this resolve that problem?

If the Tennant is dependant on both the PropCode and the Unit, then you'll need to use both criteria in the Tennant's Combo Box's .RowSource:

SELECT DISTINCT [Tenant]
FROM qry_Contacts
WHERE [Unit]=[cboUnit] AND [PropertyCode]=[cboPropCode]
ORDER BY [Tenant]

[JennyHunt]

You rock! Thanks for being so patient with such a newbee question. Now that I look at it -- duhhhhhh

[JennyHunt]

Hi -- Could you please tell me what I am doing wrong in trying to apply the answer to the code below? Thank you very much

strSource2 = "SELECT DISTINCT Tenant " & _
"FROM qry_Contacts " & _
"WHERE Unit = '" & Me.cboUnit & "' AND "PropertyCode = '" & Me.cboPropCode & "' ORDER BY Tenant"
Me.cboTenant.RowSource = strSource2
Me.cboTenant = vbNullString

[pbaldy]

The quotes in red closed off the string:

"WHERE Unit = '" & Me.cboUnit & "' AND "PropertyCode = '" & Me.cboPropCode & "' ORDER BY Tenant"

[JennyHunt]

Here is my complete code with the quote taken out. The tenant list is blank. Can you see what I'm doing wrong please?

Private Sub cboPropCode_AfterUpdate()
Dim strSource As String
Dim strSource2 As String
strSource = "SELECT DISTINCT Unit " & _
"FROM qry_Contacts " & _
"WHERE PropertyCode = '" & Me.cboPropCode & "' ORDER BY Unit"
Me.cboUnit.RowSource = strSource
Me.cboUnit = vbNullString
strSource2 = "SELECT DISTINCT Tenant " & _
"FROM qry_Contacts " & _
"WHERE Unit = '" & Me.cboUnit & "' AND PropertyCode = '" & Me.cboPropCode & "' ORDER BY Tenant"
Me.cboTenant.RowSource = strSource2
Me.cboTenant = vbNullString
End Sub