displaying other data from other tables

[Ken]

Ok, I'm an Oracle Forms developer and have never used Access and feel like a
dope doing this simple new project in Access. This should be real simple,
but I'm not having any luck.

I have a form with a master detail relationship and on the master
relationship I have a table associated with this that needs data from another
table not on that form, how can I get this data?

More specifically, I have the Customer ID on the Order master side, but I
want to go out to the 'customer master' and pull some of that data into this
form as display only. Do I use an expression after creating a text box?
This is what I've done and I've got as the expression (example is with the
Customer's last name only), something like:

=(select [Last Name] from [tCustomers] where [tCustomers][ID] = [Customer ID])

and I keep getting #Name? displayed on this field.

[Customer ID] is, I assume, my form value.

Am I going about this all wrong? Any help with this at all just to lead me
in the right direction would be super!

Thanks,

Ken

 

[fredg]

Ok, I'm an Oracle Forms developer and have never used Access and feel like a
dope doing this simple new project in Access. This should be real simple,
but I'm not having any luck.

I have a form with a master detail relationship and on the master
relationship I have a table associated with this that needs data from another
table not on that form, how can I get this data?

More specifically, I have the Customer ID on the Order master side, but I
want to go out to the 'customer master' and pull some of that data into this
form as display only. Do I use an expression after creating a text box?
This is what I've done and I've got as the expression (example is with the
Customer's last name only), something like:

=(select [Last Name] from [tCustomers] where [tCustomers][ID] = [Customer ID])

and I keep getting #Name? displayed on this field.

[Customer ID] is, I assume, my form value.

Am I going about this all wrong? Any help with this at all just to lead me
in the right direction would be super!

Thanks,

Ken

You cannot use an = Select etc... statement in an Access control's
control source.
You can use a DLookUp() function.

Also, the criteria must be concatenated into the criteria.
Each DLookUp argument must be a string.

Is that other table's name "tCustomers" or "Customer Master"?

If the Datatype of the [CustomerID] field is Number:

=DLookUp("[Last Name]","[OtherTableName]","[ID] = " & [CustomerID])

Assuming the [CustomerID] shown on the form is 123 the above will
search for "[ID] = 123"

If the Datatype of the [CustomerID] field is Text:

=DLookUp("[Last Name]","[OtherTableName]","[ID] = '" & [CustomerID] &
"'")

If the [CustomerID] on the form is "DAVE" the above will search for
"[ID] = 'DAVE'"

Also make sure that the name of this control is not the same as the
name of any field used in it's control source expression

Look up the DLookUp function in VBA help, as well as "Restrict data to
a sub-set of records".

 

[Ken]

Fred, thanks so much. That's what I needed to know. Now I'm armed and
dangerous.

One last question if you are still out there.....is there anyway to grab
other columns from another table ALL AT ONCE so that I can have them show up
in the object list to be chosen without having to do a dlookup for every
extra displayable column I want to show?

Again, thank you!!!

 

[Klatuu]

One way would be to use a combo box based on the customer table. It would
need to be a multi column combo box with one column for each field you need
to populate your form controls. You would use the combo's row source
property to identify the data:

select [Customer ID], [Last Name] from [tCustomers]

Now you will have two columns. The first column is usually the bound column.
Don't confuse Bound Column with Bound control. They are two different
things. In a combo, the Bound column is the column that will show in the
combo's text box portion and will be the value returned when the combo is
referenced. It will also be the vaule passed to the recordset field the
combo is bound to in the Control Source property. You can use the After
Update event of the combo to load the other form controls. That is done
using the combo's column collection. The column index is zero based.

Me.txtLastName = Me.MyCombo.Column(1)

And any other controls you need to populate.