Solved Lookup lists on tables

[CanuckBuck]

The lookup tables in my database have columns to define the effective and obsolete dates for the lookup values. If a row in the lookup table has an obsolete date it should not appear in the lookup list of tables that reference it UNLESS the referencing row is currently referring to the obsolete row from the lookup table, in which case it should be included. This scenario can occur if the value was used before the lookup value became obsolete.

How do I reference the value in the column from the referencing table to use as input for the lookup query? I've tried [TableName].[ColunmName] [TableName]![ColunmName], and Me.[ColunmName]. In all cases the table prompts for a parameter when I view the table.

 

[theDBguy]

Hi. If you're talking about the Lookup tab in table design view, I would recommend you don't do it there. Instead use a Combobox on Forms.

 

[CanuckBuck]

Hi. If you're talking about the Lookup tab in table design view, I would recommend you don't do it there. Instead use a Combobox on Forms.

Haha! That's so funny. I was just reading this post where I think it was you who made this same reply about four years ago.

I am referring to the Lookup tab in the table design view. I was hoping that by doing it there, any forms I create based on that table would have that lookup rather than having to do it for each form I create. It seemed like a cleaner way to do it.

From research I've done It seems that there's not an obvious way to do this. I have done it before on forms, I was just hoping for a more fundamental solution.

Thanks for your response.

 

[theDBguy]

Haha! That's so funny. I was just reading this post where I think it was you who made this same reply about four years ago.

I am referring to the Lookup tab in the table design view. I was hoping that by doing it there, any forms I create based on that table would have that lookup rather than having to do it for each form I create. It seemed like a cleaner way to do it.

From research I've done It seems that there's not an obvious way to do this. I have done it before on forms, I was just hoping for a more fundamental solution.

Thanks for your response.

Hi. If you want to do it to make it easier to create your forms, you go ahead and do it. Just make sure you delete them afterwards.

 

[Deleted member 147267]

It seemed like a cleaner way to do it.

You mean easier? Yeah, to start with. Just wait until you have to deal with Recordset2 and Field2 types in code. Lot's of fun.
If you want 1 reason to not use lookup fields in tables, pick one of these 8 reasons.
I can't comment on how to solve your initial post because I can't follow the description as you've outlined it. Even if I could, I wouldn't know how to work with lookup fields in solving your issue.

 

[The_Doc_Man]

The correct way to do this involves a JOIN query where you have separate tables to manage that lookup. You can use a WHERE clause to eliminate dates that shouldn't be there and when using this query, you can name both a field and a source table with no sweat.

However, having a lookup field in a table, while legal, may qualify as one of the dumbest ideas Microsoft ever imagined. Mostly because of the issues that show their ugly heads. Micron gave you a list before I got here so I won't bother.

 

[CanuckBuck]

Hi. If you want to do it to make it easier to create your forms, you go ahead and do it. Just make sure you delete them afterwards.

TheDBGuy;
Thanks again for your attention on this. I think what I've discovered is that there is either no way, or no at the least, no way that I can find, to do what I want, for a couple of reasons.

1. I can't find a way, on lookup tab of the table design form, to specify a rowsource that references the value of the field for which I'm creating the rowsource.
2. Even if there was a way to reference the field it would not give me the result I desire (the valid list of drop-down values for each row of the table) as per the cavet noted by Ken Sheridan in the post I referenced above.

Thank you also to Micron and The_Doc_Man for your informed responses.

Micron; I read the link you posted. I confess that although I don't understand the details of each of the items, the message is clear enough.​

The_Doc_Man; I don't think I understand what you've described but based on your feedback

one of the dumbest ideas Microsoft ever imagined. Mostly because of the issues that show their ugly heads.

It seems like a moot point.​

By way of clarification, my database is SQL Server. I'm attempting to use MS Access to create a minimal-code, light weight UI. I'm using linked tables and forms bound to the tables. I've read about unbound forms and controlling interaction to the database through code. It seems like a lot of work (a lot of code to write and maintain), especially when compared with bound forms. I'm much more comfortable in the SQL Server domain than MS Access. If I had any other tool for creating a UI I'd probably use it but MS Access is what I've got to work with.

Finally;

In hopes that this post will help others, I've renamed it to something that should be easier to find.​

Thanks again!

 

[theDBguy]

TheDBGuy;
Thanks again for your attention on this. I think what I've discovered is that there is either no way, or no at the least, no way that I can find, to do what I want, for a couple of reasons.

1. I can't find a way, on lookup tab of the table design form, to specify a rowsource that references the value of the field for which I'm creating the rowsource.
2. Even if there was a way to reference the field it would not give me the result I desire (the valid list of drop-down values for each row of the table) as per the cavet noted by Ken Sheridan in the post I referenced above.

Thank you also to Micron and The_Doc_Man for your informed responses.

Micron; I read the link you posted. I confess that although I don't understand the details of each of the items, the message is clear enough.​

The_Doc_Man; I don't think I understand what you've described but based on your feedback It seems like a moot point.​

By way of clarification, my database is SQL Server. I'm attempting to use MS Access to create a minimal-code, light weight UI. I'm using linked tables and forms bound to the tables. I've read about unbound forms and controlling interaction to the database through code. It seems like a lot of work (a lot of code to write and maintain), especially when compared with bound forms. I'm much more comfortable in the SQL Server domain than MS Access. If I had any other tool for creating a UI I'd probably use it but MS Access is what I've got to work with.

Finally;

In hopes that this post will help others, I've renamed it to something that should be easier to find.​

Thanks again!

Hi. As I was saying earlier, you can do what you want on forms, but not in tables because you can use events in forms. Tables don't have them. Besides, if you're using linked tables, I don't understand how you're able to change their designs from Access. I didn't know that was possible.

 

[The_Doc_Man]

@theDBguy - you can possibly change SQL server tables from Access using DDL and the "ALTER TABLE" syntax. If you have proper permissions, that is.

 

[The_Doc_Man]

The_Doc_Man; I don't think I understand what you've described

Don't want to leave you totally confused about what I meant, so think of this.

You were trying to have a field in a table look up a value from another table. We said, and you agreed based on practicality, that you could not do that. HOWEVER, there is nothing wrong with writing a SELECT query to do that lookup for you when you open the query. Most things in Access don't care if they are coming directly from a table or from a query. Forms and reports work that way. Heck, you can even reference a query from another query. So my idea was rather than hard-linking a lookup in the table, do a JOIN query that does the implied lookup.

If you have field fldA in table tblA and want to look up a value from table tblLkUp, for which the "key" is fldA and the "lookup value" is fldLV,

SELECT tblA.fldA, tblLkUp.fldV, tblA.other fields...
FROM tblA INNER JOIN tblLkUp ON tblA.fldA = tblLkUp.fldA ;

Obviously, you would want to retrieve other fields from tblA, but the point here is that this QUERY does the lookup for you by doing that JOIN operation. But the contents of tblA are not hard-linked to tblLkUp. The linkage only exists while the query is open and you can see the true value of the field in tblA - which is not always the case when doing lookup fields.

 

[theDBguy]

@theDBguy - you can possibly change SQL server tables from Access using DDL and the "ALTER TABLE" syntax. If you have proper permissions, that is.

@The_Doc_Man Thanks. I am well aware of that, but it doesn't provide a "Lookup Tab" that the OP was trying to manipulate. So, my confusion was how are they changing the table structure of a linked ODBC table using Access in design view?

 

[Pat Hartman]

We seem to be unanimous on the no table level lookups advice. They are not available in SQL Server in any event so the point is moot.

You marked the thread as solved but never told us your solution so I am adding mine:

In my apps, I sort all the inactive items to the end of the list so they are out of the way when the list drops down. I also have code in the BeforeUpdate event of the combo to check the inactive flag (I don't use dates but the technique is the same) and if the selected item is inactive, I do not allow the user to choose it. This does not interfere with existing uses of the inactive code.

If Me.cboSomeField.Columns(2) = True Then    ''' Item is Active
Else
    Cancel = True
    Me.cboSomeField.Undo
    Msgbox "you may not select an inactive item"
    Exit Sub
End if