Linked information; how do I show it?

[dogmelissa]

If this doesn't make sense, let me know and I'll try to explain it better.
We have an "Order Entry" form, which reads out of the table "Orders". We
also have a "Customers" form which reads out of the table "Customers". We
give each job it's own individual job number, which goes in the Orders table,
through the Order Entry form. Currently, on the Order Entry form, you type in
the job #, pick the customer from a list box and then enter the rest of the
data. However, each customer has a salesperson attached to them, through the
Customers table, and I want to be able to show that salesperson on the Order
Entry form. I don't want it to be something that can be changed, but just as
a reference, so that I know that "Joe" is the person responsible for the
Customer "1Company". I know I have to somehow get a box (text? list?) to be
linked to the customer that you pick in Order Entry, and pull up the
Salesperson in the Customers table, but I can't quite figure out how to do
that.
Suggestions?
Thanks in advance,
Melissa

 

[Steve Schapel]

Melissa

There are 3 commonly used approaches to this...

1. Base your Order Entry form on a Query which includes both the Orders
table and the Customers table, joined on the applicable field
(CustomerID?). Then you can include the Salesperson field from the
Customers table in the query, and hence you can have a textbox on the
form directly bound to this field. When you select a Customer from the
listbox or combobox on the form, the associated Salesperson will
automatically be shown. You should set the Enabled property of the
Salespeson control to No and its Locked property to Yes.

2. You can put an unbound textbox on the form, and set its Control
Source property to the equivalent of...
=DLookUp("[Salesperson]","Customers","[CustomerID]=" & [CustomerID])

3. You can make your Customer listbox or combobox multi-column so it
includes the Salesperson in one of the columns (you can set the Column
Widtrh of this column to 0 so it doesn't actuall show up in the listbox
or combobox, just as long as it is represented there. Then, you can put
an unbound textbox on the form, and set its Control Source property to
the equivalent of...
=[Customer].[Column](2)
(in this case, the "2" assumes the Salesperson is the 3rd column in the
combobox's Row Source, i.e. the columns are numbered starting from 0 for
the first column)

 

[dogmelissa]

I'm trying the second approach, because the first would require a re-working
of the Order Entry page, which I'm not willing to do at this point.
I can't get this to work. Not sure what I'm missing.

I put in an unbound text box and entered this code in the Control Source's
expression builder:

=DLookUp("[Employee]","Customers","[CompanyName]=" & [CompanyName])

And on the form, when I pull up a job, I get "#Name?" in this box. Actually,
even on a blank job (which is what the form opens as), there is this error in
the box.

Is there a way to fix this? Does it matter that I'm using MS Access 2000
(9.0.3821 SR-1) ? Will this code work even if you had suggested it for a
different version?

Thanks again,
Melissa

Melissa

There are 3 commonly used approaches to this...

1. Base your Order Entry form on a Query which includes both the Orders
table and the Customers table, joined on the applicable field
(CustomerID?). Then you can include the Salesperson field from the
Customers table in the query, and hence you can have a textbox on the
form directly bound to this field. When you select a Customer from the
listbox or combobox on the form, the associated Salesperson will
automatically be shown. You should set the Enabled property of the
Salespeson control to No and its Locked property to Yes.

2. You can put an unbound textbox on the form, and set its Control
Source property to the equivalent of...
=DLookUp("[Salesperson]","Customers","[CustomerID]=" & [CustomerID])

3. You can make your Customer listbox or combobox multi-column so it
includes the Salesperson in one of the columns (you can set the Column
Widtrh of this column to 0 so it doesn't actuall show up in the listbox
or combobox, just as long as it is represented there. Then, you can put
an unbound textbox on the form, and set its Control Source property to
the equivalent of...
=[Customer].[Column](2)
(in this case, the "2" assumes the Salesperson is the 3rd column in the
combobox's Row Source, i.e. the columns are numbered starting from 0 for
the first column)

--
Steve Schapel, Microsoft Access MVP

If this doesn't make sense, let me know and I'll try to explain it better.
We have an "Order Entry" form, which reads out of the table "Orders". We
also have a "Customers" form which reads out of the table "Customers". We
give each job it's own individual job number, which goes in the Orders table,
through the Order Entry form. Currently, on the Order Entry form, you type in
the job #, pick the customer from a list box and then enter the rest of the
data. However, each customer has a salesperson attached to them, through the
Customers table, and I want to be able to show that salesperson on the Order
Entry form. I don't want it to be something that can be changed, but just as
a reference, so that I know that "Joe" is the person responsible for the
Customer "1Company". I know I have to somehow get a box (text? list?) to be
linked to the customer that you pick in Order Entry, and pull up the
Salesperson in the Customers table, but I can't quite figure out how to do
that.
Suggestions?
Thanks in advance,
Melissa

 

[Steve Schapel]

Melissa,

My first idea is just as easy as the others really. It doesn't require
any "re-working", just a very simple query which you could make in about
7-10 seconds, and change the Enabled and Locked properties of the
Employee control. Anyway, whatever.

With your textbox, first of all it is important that the name of the
texbox is not the same as a field in the form's record source. Second,
if the CompanyName field is text data type (sorry, I had assumed your
listbox/combobox would be doing the normal reference to a numerical ID
field), then your expression will need to be like this...
=DLookUp("[Employee]","Customers","[CompanyName]='" & [CompanyName] & "'")

Try this, and let us know. If you still have problems, please give some
more details about the structure of the CompanyName field on the form,
i.e. Control Source, Row Source, Bound Column, etc.

--
Steve Schapel, Microsoft Access MVP

I'm trying the second approach, because the first would require a re-working
of the Order Entry page, which I'm not willing to do at this point.
I can't get this to work. Not sure what I'm missing.

I put in an unbound text box and entered this code in the Control Source's
expression builder:

=DLookUp("[Employee]","Customers","[CompanyName]=" & [CompanyName])

And on the form, when I pull up a job, I get "#Name?" in this box. Actually,
even on a blank job (which is what the form opens as), there is this error in
the box.

Is there a way to fix this? Does it matter that I'm using MS Access 2000
(9.0.3821 SR-1) ? Will this code work even if you had suggested it for a
different version?

Thanks again,
Melissa

:

Melissa

There are 3 commonly used approaches to this...

1. Base your Order Entry form on a Query which includes both the Orders
table and the Customers table, joined on the applicable field
(CustomerID?). Then you can include the Salesperson field from the
Customers table in the query, and hence you can have a textbox on the
form directly bound to this field. When you select a Customer from the
listbox or combobox on the form, the associated Salesperson will
automatically be shown. You should set the Enabled property of the
Salespeson control to No and its Locked property to Yes.

2. You can put an unbound textbox on the form, and set its Control
Source property to the equivalent of...
=DLookUp("[Salesperson]","Customers","[CustomerID]=" & [CustomerID])

3. You can make your Customer listbox or combobox multi-column so it
includes the Salesperson in one of the columns (you can set the Column
Widtrh of this column to 0 so it doesn't actuall show up in the listbox
or combobox, just as long as it is represented there. Then, you can put
an unbound textbox on the form, and set its Control Source property to
the equivalent of...
=[Customer].[Column](2)
(in this case, the "2" assumes the Salesperson is the 3rd column in the
combobox's Row Source, i.e. the columns are numbered starting from 0 for
the first column)

--
Steve Schapel, Microsoft Access MVP

If this doesn't make sense, let me know and I'll try to explain it better.
We have an "Order Entry" form, which reads out of the table "Orders". We
also have a "Customers" form which reads out of the table "Customers". We
give each job it's own individual job number, which goes in the Orders table,
through the Order Entry form. Currently, on the Order Entry form, you type in
the job #, pick the customer from a list box and then enter the rest of the
data. However, each customer has a salesperson attached to them, through the
Customers table, and I want to be able to show that salesperson on the Order
Entry form. I don't want it to be something that can be changed, but just as
a reference, so that I know that "Joe" is the person responsible for the
Customer "1Company". I know I have to somehow get a box (text? list?) to be
linked to the customer that you pick in Order Entry, and pull up the
Salesperson in the Customers table, but I can't quite figure out how to do
that.
Suggestions?
Thanks in advance,
Melissa

 

[dogmelissa]

I understand about enabling and locking text boxes, but the problem I have
with our database is that someone else built it, and many things I try to
change give me errors because of relationships and "related entries". If I
rebuild the database at some point, I would like to see this form running off
a query, but I don't think I can do it at this point. So I'm trying to work
around what I have.

I copied EXACTLY what you typed into my box, and I still got the same error.
So here are some details about the Customer field list box:

Combo Box: Customer
Name: Customer
Control Source: Customer (this is a field in my "Orders" table)
Row/Source Type: Table/Query
Row Source: SELECT [Customers].[CompanyName] FROM Customers;
Enabled: Yes
Locked: No

What the heck; here is the text box I just put in:

Text Box: Text142
Name: Text142
Control Source: =DLookUp("[Employee]","Customers","[CompanyName]='" &
[CompanyName] & "'")
Enabled: Yes
Locked: No

And the error I get when I open the form (it doesn't change regardless of
whether there is a record loaded or not) is "#Name?"

Is there any more information you need?
Thanks again.
Melissa

 

[Steve Schapel]

Melissa,

Aha! Now I see what you have done... er, I think!

I think it should be like this...
=DLookUp("[Employee]","Customers","[CompanyName]='" & [Customer] & "'")

In other words, you want Text142 textbox to show the value of the
Employee field in the Customers table for the record where the
CompanyName field in the Customers table is the same as the Customer
combobox. Right?

 

[dogmelissa]

Yes, that is EXACTLY what I want it to do. Now it's just a matter of trying
this. I have to go to work tomorrow, so I will try it then--I'm just checking
from home to see if you had any suggestions. I will post tomorrow and let you
know how it goes.
Thanks.
Melissa

Melissa,

Aha! Now I see what you have done... er, I think!

I think it should be like this...
=DLookUp("[Employee]","Customers","[CompanyName]='" & [Customer] & "'")

In other words, you want Text142 textbox to show the value of the
Employee field in the Customers table for the record where the
CompanyName field in the Customers table is the same as the Customer
combobox. Right?

--
Steve Schapel, Microsoft Access MVP

I understand about enabling and locking text boxes, but the problem I have
with our database is that someone else built it, and many things I try to
change give me errors because of relationships and "related entries". If I
rebuild the database at some point, I would like to see this form running off
a query, but I don't think I can do it at this point. So I'm trying to work
around what I have.

I copied EXACTLY what you typed into my box, and I still got the same error.
So here are some details about the Customer field list box:

Combo Box: Customer
Name: Customer
Control Source: Customer (this is a field in my "Orders" table)
Row/Source Type: Table/Query
Row Source: SELECT [Customers].[CompanyName] FROM Customers;
Enabled: Yes
Locked: No

What the heck; here is the text box I just put in:

Text Box: Text142
Name: Text142
Control Source: =DLookUp("[Employee]","Customers","[CompanyName]='" &
[CompanyName] & "'")
Enabled: Yes
Locked: No

And the error I get when I open the form (it doesn't change regardless of
whether there is a record loaded or not) is "#Name?"

Is there any more information you need?
Thanks again.
Melissa

 

[dogmelissa]

SUCCESS!!!
The latest code you gave me worked PERFECTLY!!
Thank you thank you thank you thank you!

Melissa

Yes, that is EXACTLY what I want it to do. Now it's just a matter of trying
this. I have to go to work tomorrow, so I will try it then--I'm just checking
from home to see if you had any suggestions. I will post tomorrow and let you
know how it goes.
Thanks.
Melissa

Melissa,

Aha! Now I see what you have done... er, I think!

I think it should be like this...
=DLookUp("[Employee]","Customers","[CompanyName]='" & [Customer] & "'")

In other words, you want Text142 textbox to show the value of the
Employee field in the Customers table for the record where the
CompanyName field in the Customers table is the same as the Customer
combobox. Right?

--
Steve Schapel, Microsoft Access MVP

I understand about enabling and locking text boxes, but the problem I have
with our database is that someone else built it, and many things I try to
change give me errors because of relationships and "related entries". If I
rebuild the database at some point, I would like to see this form running off
a query, but I don't think I can do it at this point. So I'm trying to work
around what I have.

I copied EXACTLY what you typed into my box, and I still got the same error.
So here are some details about the Customer field list box:

Combo Box: Customer
Name: Customer
Control Source: Customer (this is a field in my "Orders" table)
Row/Source Type: Table/Query
Row Source: SELECT [Customers].[CompanyName] FROM Customers;
Enabled: Yes
Locked: No

What the heck; here is the text box I just put in:

Text Box: Text142
Name: Text142
Control Source: =DLookUp("[Employee]","Customers","[CompanyName]='" &
[CompanyName] & "'")
Enabled: Yes
Locked: No

And the error I get when I open the form (it doesn't change regardless of
whether there is a record loaded or not) is "#Name?"

Is there any more information you need?
Thanks again.
Melissa

 

[Steve Schapel]

Melissa,

You're welcome. I am very happy to know it's working for you.

To call it "code" is a bit of an overstatement It's just an expression.

Anyway, my fond hope is that you can understand what it does and how it
does it, so you will be able to handle it next time you have to do
something like this.