DLookUp w/ Multiple Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to control which table is used in a DLookUp based on the value
in another field on a form?
 
azkayaker said:
I would like to control which table is used in a DLookUp based on the value
in another field on a form?
Click to expand...

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, . . .)
 
I would like to control which table is used in a DLookUp based on the value
in another field on a form?
Click to expand...

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]
 
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
John W. Vinson said:
I would like to control which table is used in a DLookUp based on the value
in another field on a form?
Click to expand...

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]
Click to expand...
 
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?
Click to expand...

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
Click to expand...

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]
 
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 only problem I see here is that "somefield" is not used consistenly in
each table - each table has a unique field name.
 
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"
 
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.
Click to expand...

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]
 
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?
 
azkayaker said:
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"
Click to expand...


Yes, that can work:

x = DLookup(strSomeField, strTableName, . . .)
 
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?
Click to expand...

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]
 
Back
Top