DLookUp w/ Multiple Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

azkayaker - 12 Jul 2007 00:06 GMT

I would like to control which table is used in a DLookUp based on the value
in another field on a form?

Reply to this Message

Marshall Barton - 12 Jul 2007 00:57 GMT

>I would like to control which table is used in a DLookUp based on the value
>in another field on a form?

Dim strTableName As String
Select Case someotherfieldonaform
Case thisvalue
    strTableName = "tableA"
Case thatvalue
    strTableName = "tableB"
Case othervalue
    strTableName = "tableC"
. . .
End Select
x = DLookup("somefield", strTableName, . . .)

Signature

Marsh
MVP [MS Access]

Reply to this Message

azkayaker - 13 Jul 2007 17:30 GMT

The only problem I see here is that "somefield" is not used consistenly in
each table - each table has a unique field name.

> >I would like to control which table is used in a DLookUp based on the value
> >in another field on a form?
[quoted text clipped - 10 lines]
> End Select
> x = DLookup("somefield", strTableName, . . .)

Reply to this Message

azkayaker - 13 Jul 2007 17:48 GMT

As a followup - Would I add a str value for somefield ie strSomeFeld into the
code?:

Dim strTableName As String
Dim strSomeField As String

Select Case someotherfieldonaform
Case thisvalue
    strTableName = "tableA"
strSomeField = "field1"
Case thatvalue
    strTableName = "tableB"
strSomeField = "field2"

Case othervalue
    strTableName = "tableC"
strSomeField = "field3"

Reply to this Message

Marshall Barton - 13 Jul 2007 21:17 GMT

>As a followup - Would I add a str value for somefield ie strSomeFeld into the
>code?:
[quoted text clipped - 13 lines]
> strTableName = "tableC"
> strSomeField = "field3"

Yes, that can work:

x = DLookup(strSomeField, strTableName, . . .)

Signature

Marsh
MVP [MS Access]

Reply to this Message

John W. Vinson - 12 Jul 2007 03:03 GMT

>I would like to control which table is used in a DLookUp based on the value
>in another field on a form?

The second argument to DLookUp is a string value with the name of a table or
query. Often this will be a string constant ("MyTable") but it works just fine
using a variable name or a forms reference, just so that variable contains a
valid table name:

=DLookUp("[Fieldname]", [cboTable], "<some criteria>")

where cboTable is the name of a combo box with the table name in the combo's
bound column should work fine.

John W. Vinson [MVP]

Reply to this Message

azkayaker - 13 Jul 2007 00:30 GMT

The value in the combobox/field is not equal to the name of the table(s) and
is already a bound field for the value selected. Can I have more than one
bound field for a combo?

Otherwise

It is more of an If/Then ie If field value = ABC then use Table1, = DEF
then use Table 2.....ect

Thanks

> >I would like to control which table is used in a DLookUp based on the value
> >in another field on a form?
[quoted text clipped - 10 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 13 Jul 2007 01:12 GMT

>The value in the combobox/field is not equal to the name of the table(s) and
>is already a bound field for the value selected. Can I have more than one
>bound field for a combo?

Where in your database is the correspondance between the field value and the
table name stored?

You can include more than one column in a combo box (assuming that the
tablename is in some other field in your combo's rowsource table), and you can
refer to

=comboboxname.Column(n)

where n is the zero based index of the field.

>Otherwise
>
>It is more of an If/Then ie If field value = ABC then use Table1, = DEF
>then use Table 2.....ect

I have to be concerned about the need to do this exercise AT ALL. Are Table1
and Table2 and so on multiple tables of very similar or identical structure?
If so your database needs attention to normalization: storing information
(categories, subsets, etc.) in tablenames is Very Bad Design and will become a
real problem down the road. What are these tables, and why the need to select
them?

John W. Vinson [MVP]

Reply to this Message

azkayaker - 13 Jul 2007 17:26 GMT

Here is the best analogy that I could come up with to explain the relation
ship between the form and tables.

The form is a continuous form on which Field1 contains the identifier such
as Dog,City,Car. Field2 contains the a value such as 1,2,3 or A,B,C. If Dog
is the value in Field1 then Field2 value represent the breed of dog 1,2 or 3
and that information is located in tbleDog ie 1=pug, 2=wolf ect...If Field2
is City then Field2 is A,B or C and corresponidng info is in tblCity ie
A=Atlanta, B=Boston ect.

My request is that based on Field1's value the DLookUp uses a specific table
to fill one or more fields on the form with data from the corresponding table
based on Field2's value. Currently all you see is Field2's value.

As I understand you are saying I need a table the shows the correlation
between values in Field1 and the associated tables ie Dog...tblDog,
City....tblCity.

> >The value in the combobox/field is not equal to the name of the table(s) and
> >is already a bound field for the value selected. Can I have more than one
[quoted text clipped - 24 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 13 Jul 2007 18:20 GMT

>My request is that based on Field1's value the DLookUp uses a specific table
>to fill one or more fields on the form with data from the corresponding table
[quoted text clipped - 3 lines]
>between values in Field1 and the associated tables ie Dog...tblDog,
>City....tblCity.

Well, you need that information *somewhere* - a table (the first combo's row
source) would certainly be convenient, but you could have it in an array in
VBA code, in a Switch() function, as multiple inline VBA statements...

My suggestion would be to add a new field to the first table containing the
tablename; either make that the Bound Column of the combo or reference it in
your DLookUp() using the comboboxname.Column(n) syntax.

John W. Vinson [MVP]

Reply to this Message

azkayaker - 13 Jul 2007 21:06 GMT

Ok - i'll try this and add another field for the table names. Not being that
fluent with Access what is the comboboxname.Column(n) systax where n is the
zero based index of the field? Is n = the column number for the combo source?

> >My request is that based on Field1's value the DLookUp uses a specific table
> >to fill one or more fields on the form with data from the corresponding table
[quoted text clipped - 13 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 13 Jul 2007 23:00 GMT

>Ok - i'll try this and add another field for the table names. Not being that
>fluent with Access what is the comboboxname.Column(n) systax where n is the
>zero based index of the field? Is n = the column number for the combo source?

Let's say your first combo, cboType, is based on a table with fields
TableType (e.g. Dog, City, ...) and TableName (tblBreeds, tblCities, ...), and
FieldName (the name of the field that you want to look up in that table). The
combo box would have ColumnCount 3 to include all threee fields; ColumnWidths
0.75;0;0 to display only the TableType. The other fields are still in the
combo, just not visible.

In your DLookUp code you would use

=DLookUp(Forms!YourFormName!Column(2), Forms!YourFormName!Column(1), <optional
criteria>)

to look for the fieldname (the third column, they're numbered 0, 1 and 2) in
the combo box, in the tablename (the second column, (1).)

John W. Vinson [MVP]

Reply to this Message