Table lookups with queries

[David Baldacchino]

I know...lookups in Tables are BAD, EVIL I get it. I understand it, even
with my limited knowledge.

However I'm curious whether the following can be done.

Let's say I have a table (MyTable) and one of the columns is "CompanyID". In
the "Products" column, I set the the table to display a list box control and
created a lookup field from a Products table, selected Table/Query and built
a 3 column query containing the ProductID, Product Name and CompanyID. I set
the Bound Column to 1 (I want to store ProductID, but want to see Product
Name in the listbox), Column Count to 3 and the column widths to 0";1";0". I
would like to filter this lookup listbox in the datasheet by the CompanyID of
the record I'm editing. I tried setting the Criteria of the lookup query to
be equal to [MyTable].[CompanyID] but the problem is that when I open the
table, I get prompted to enter a value and it will persist when I edit
different records (the filtering doesn't change when I edit a different
record with a different CompanyID).

Is there any way to achieve this in a datasheet lookup query? Again...I'm
looking for a temporary fix to enter some data quickly and I know how to
create a form to achieve what I'm after, but am very curious if this can also
be done with a field lookup and a query. Thanks!

 

[John W. Vinson]

On Tue, 30 Sep 2008 22:06:01 -0700, David Baldacchino <David

Is there any way to achieve this in a datasheet lookup query? Again...I'm
looking for a temporary fix to enter some data quickly and I know how to
create a form to achieve what I'm after, but am very curious if this can also
be done with a field lookup and a query. Thanks

I may be mistaken, but as far as I know you can only create a *conditional*
combo box on a Form, not in a table or query datasheet.

 

[Klatuu]

Lookup fields in tables are evil, table lookups are not. It is a very valid
way to retrieve data; however, it should be done at the form level. I don't
think it can even be done at the table level.

 

[David Baldacchino]

Thanks guys. So what you're saying is that it's not possible to apply a
condition to a query within a table lookup. I kinda figured but thought I was
missing something. And yes, lookups on tables by using combo boxes on forms
are the way to go. I was just trying to find a 'quick-fix" way to enter some
data without designing a form in this case and it seemed like it should be
able to take the concept all the way through rather than being "half-baked"
so to speak. Thanks all!

 

[David Baldacchino]

I used the wrong term and cannot edit my post...with "table lookup' I meant
"lookup field". Sorry for the confusion.

 

[John W. Vinson]

Thanks guys. So what you're saying is that it's not possible to apply a
condition to a query within a table lookup. I kinda figured but thought I was
missing something. And yes, lookups on tables by using combo boxes on forms
are the way to go. I was just trying to find a 'quick-fix" way to enter some
data without designing a form in this case and it seemed like it should be
able to take the concept all the way through rather than being "half-baked"
so to speak. Thanks all!

In that case, put in the lookup field (wash your hands afterwards <g>) and use
an Autoform. Tweak the rowsource of the combo box after it's been created.

 

[David Baldacchino]

Thanks John. Interesting...a search for "autoform" in the help yielded no
results. I know how to create one...select the table and on the Create ribbon
click on "Form" (or one of the other options).

That's a good trick to get a form started up quicker, but then it requires
some coding to requery when the combo box values are changed. Guess I'll have
to bite the bullet and do it =)

 

[John W. Vinson]

That's a good trick to get a form started up quicker, but then it requires
some coding to requery when the combo box values are changed. Guess I'll have
to bite the bullet and do it =)

Eh. One line of code or macro... requery the dependent combo in the
afterupdate event of the controlling combo. Easy!

 

[David W. Fenton]

On Tue, 30 Sep 2008 22:06:01 -0700, David Baldacchino <David


I may be mistaken, but as far as I know you can only create a
*conditional* combo box on a Form, not in a table or query
datasheet.

Well, theoretically, you could do it using Screen.ActiveDatasheet,
but I wouldn't want to troubleshoot the code!