Text Autopopulated in Form Doesn't Display in Table

[rszebras]

I inherited a database (as a novice at Access) and need to modify it to
make it more efficient, i.e., the assignment form needs to autopopulate
with the client's name, address, phone number, etc., when you select
the cust id. Found this cited as a good method:
http://www.mvps.org/access/forms/frm0058.htm

I have two tables (clients, assignments) and one form (assignment
input) in the database. I have a
control box called CUST ID in the assignment input form. I set the row
source type to table/query. I set the row source to the following:

SELECT Clients.Custome rID, Clients.Contact FROM Clients ORDER BY
CustomerID;

I then set the control source of the textbox (it was previously a combo
box with a drop down menu)to:

=[CUST ID].Column(1)

This results in the name of the client (contact) autopopulating when I
select the customer id. However, when I try to save the form I get this
error message: The field 'Assignment Data Sheet.Rec'd From' cannot
contain a Null value because the Required property for this field is
set to True. Enter a value in this field.

I tried removing the required property for that field from the
assignments table (my feeble attempt at troubleshooting ) and the
client's name that autopopulated in the form still does not appear in
the table. This is where I am stuck.

(Of course, I have other data that needs to autopopulate too. I just
need to make sure the method works before I add the additional code in
the row source of the CUST ID control.)

[rszebras]

Can I get a little help here? Please. If I need to explain something in
more detail, or if you have questions, please reply.

Thanks for your help.

rs******@hotmai l.com wrote:

I inherited a database (as a novice at Access) and need to modify it to
make it more efficient, i.e., the assignment form needs to autopopulate
with the client's name, address, phone number, etc., when you select
the cust id. Found this cited as a good method:
http://www.mvps.org/access/forms/frm0058.htm

I have two tables (clients, assignments) and one form (assignment
input) in the database. I have a
control box called CUST ID in the assignment input form. I set the row
source type to table/query. I set the row source to the following:

SELECT Clients.Custome rID, Clients.Contact FROM Clients ORDER BY
CustomerID;

I then set the control source of the textbox (it was previously a combo
box with a drop down menu)to:

=[CUST ID].Column(1)

This results in the name of the client (contact) autopopulating when I
select the customer id. However, when I try to save the form I get this
error message: The field 'Assignment Data Sheet.Rec'd From' cannot
contain a Null value because the Required property for this field is
set to True. Enter a value in this field.

I tried removing the required property for that field from the
assignments table (my feeble attempt at troubleshooting ) and the
client's name that autopopulated in the form still does not appear in
the table. This is where I am stuck.

(Of course, I have other data that needs to autopopulate too. I just
need to make sure the method works before I add the additional code in
the row source of the CUST ID control.)

[purpleflash]

rs******@hotmai l.com wrote:

I inherited a database (as a novice at Access) and need to modify it to
make it more efficient, i.e., the assignment form needs to autopopulate
with the client's name, address, phone number, etc., when you select
the cust id. Found this cited as a good method:
http://www.mvps.org/access/forms/frm0058.htm

I have two tables (clients, assignments) and one form (assignment
input) in the database. I have a
control box called CUST ID in the assignment input form. I set the row
source type to table/query. I set the row source to the following:

SELECT Clients.Custome rID, Clients.Contact FROM Clients ORDER BY
CustomerID;

I then set the control source of the textbox (it was previously a combo
box with a drop down menu)to:

=[CUST ID].Column(1)

+++++++++++++++ +++++++++++++++ ++++++++++
I think your problem is in the control field it is expecting to have
the CUST ID value populated in this field when you select a value from
the dropdown box NOT the Customer Name which I guess is column(1)! Also
check that you haven't changed your bound field value! However it is
more correct to only display the Customer details at run time rather
than storing them in the second table (replication and denormalisation
of your data) You should always aim to try to store data once!

It would also be worth checking the relationships window as that may
explain why removing the required property has no real effect, it is
likely that an explicit constraint has been made between the two
tables!

Good luck
purpleflash
+++++++++++++++ +++++++++++++++ ++++++++++

This results in the name of the client (contact) autopopulating when I
select the customer id. However, when I try to save the form I get this
error message: The field 'Assignment Data Sheet.Rec'd From' cannot
contain a Null value because the Required property for this field is
set to True. Enter a value in this field.

I tried removing the required property for that field from the
assignments table (my feeble attempt at troubleshooting ) and the
client's name that autopopulated in the form still does not appear in
the table. This is where I am stuck.

(Of course, I have other data that needs to autopopulate too. I just
need to make sure the method works before I add the additional code in
the row source of the CUST ID control.)

[rszebras]

You may be right about the problem being in the control. As mentioned
previously, the control is a drop down box where the values from the
Clients table are displayed. In the present form of the database the
row source for the REC'D FROM control (or field of the assignments
table) is a follows:

SELECT Clients.Custome rID, Clients.Contact FROM Clients;

This apparently means that the name of the client (Clients.Contac t)
displays visually in the form but the CustomerID is also there but
hidden. I changed the content of the control by changing the control
source to read:

=[CUST ID].[Column](1)

Accordingly, only the contact is listed. I might try something like
this in the control source:

=[CUST ID].[Column](0), [Column](1)

Not sure if the syntax is right, but I will try to have the control
autopopulate with the Customer ID and Contact (but simply keep the
Customer ID hidden as it currently is). Does this sound valid? Or am I
rambling.

Anyway, thanks a lot, purpleflash. I will let you know the outcome of
my effort to change the control source code.

purpleflash wrote:

rs******@hotmai l.com wrote:

I inherited a database (as a novice at Access) and need to modify it to
make it more efficient, i.e., the assignment form needs to autopopulate
with the client's name, address, phone number, etc., when you select
the cust id. Found this cited as a good method:
http://www.mvps.org/access/forms/frm0058.htm

I have two tables (clients, assignments) and one form (assignment
input) in the database. I have a
control box called CUST ID in the assignment input form. I set the row
source type to table/query. I set the row source to the following:

SELECT Clients.Custome rID, Clients.Contact FROM Clients ORDER BY
CustomerID;

I then set the control source of the textbox (it was previously a combo
box with a drop down menu)to:

=[CUST ID].Column(1)

+++++++++++++++ +++++++++++++++ ++++++++++
I think your problem is in the control field it is expecting to have
the CUST ID value populated in this field when you select a value from
the dropdown box NOT the Customer Name which I guess is column(1)! Also
check that you haven't changed your bound field value! However it is
more correct to only display the Customer details at run time rather
than storing them in the second table (replication and denormalisation
of your data) You should always aim to try to store data once!

It would also be worth checking the relationships window as that may
explain why removing the required property has no real effect, it is
likely that an explicit constraint has been made between the two
tables!

Good luck
purpleflash
+++++++++++++++ +++++++++++++++ ++++++++++

This results in the name of the client (contact) autopopulating when I
select the customer id. However, when I try to save the form I get this
error message: The field 'Assignment Data Sheet.Rec'd From' cannot
contain a Null value because the Required property for this field is
set to True. Enter a value in this field.

I tried removing the required property for that field from the
assignments table (my feeble attempt at troubleshooting ) and the
client's name that autopopulated in the form still does not appear in
the table. This is where I am stuck.

(Of course, I have other data that needs to autopopulate too. I just
need to make sure the method works before I add the additional code in
the row source of the CUST ID control.)

[purpleflash]

rs******@hotmai l.com wrote:

You may be right about the problem being in the control. As mentioned
previously, the control is a drop down box where the values from the
Clients table are displayed. In the present form of the database the
row source for the REC'D FROM control (or field of the assignments
table) is a follows:

SELECT Clients.Custome rID, Clients.Contact FROM Clients;

This apparently means that the name of the client (Clients.Contac t)
displays visually in the form but the CustomerID is also there but
hidden. I changed the content of the control by changing the control
source to read:

=[CUST ID].[Column](1)

*************** ******
This code will attempt to store the data held in Column(1) when the
control needs to store the value in Column(0) to work. the source
should be just CUST ID.
*************** ******

>
Accordingly, only the contact is listed. I might try something like
this in the control source:

=[CUST ID].[Column](0), [Column](1)

*************** *******
The confusion here is between store and display I think - you need to
store ID and display NAME I don't think you should need to store NAME
as well!

Yep in combo boxes the displayed value is determined by the first
VISIBLE column value in the listing! If you change the bound value then
that is the value that will be put into the underlying table attribute!
To display the name for example select ID, Name from Clients set the
bound field to be column 1 (in properties the first column is 1 not 0
as it is in VBA!) and set the column count to 2 and the Column width
properties to 0cm,2cm and the total width to 2cm. then ID will be
stored but Name will be displayed!

If you set this up at table level (lookup tab) then any form will
inherit this setup when you drop the table attribute onto it! (even in
Datasheet view!)

If you really need to store the name as well you will need to add a
field to the underlying table and populate it from the combobox in code
(however this is really bad practice because it is always derived and
you are denormalising your table structure)

purpleflash

*************** **************

Not sure if the syntax is right, but I will try to have the control
autopopulate with the Customer ID and Contact (but simply keep the
Customer ID hidden as it currently is). Does this sound valid? Or am I
rambling.

Anyway, thanks a lot, purpleflash. I will let you know the outcome of
my effort to change the control source code.

purpleflash wrote:

rs******@hotmai l.com wrote:

I inherited a database (as a novice at Access) and need to modify it to
make it more efficient, i.e., the assignment form needs to autopopulate
with the client's name, address, phone number, etc., when you select
the cust id. Found this cited as a good method:
http://www.mvps.org/access/forms/frm0058.htm
>
I have two tables (clients, assignments) and one form (assignment
input) in the database. I have a
control box called CUST ID in the assignment input form. I set the row
source type to table/query. I set the row source to the following:
>
SELECT Clients.Custome rID, Clients.Contact FROM Clients ORDER BY
CustomerID;
>
I then set the control source of the textbox (it was previously a combo
box with a drop down menu)to:
>
=[CUST ID].Column(1)
>

+++++++++++++++ +++++++++++++++ ++++++++++
I think your problem is in the control field it is expecting to have
the CUST ID value populated in this field when you select a value from
the dropdown box NOT the Customer Name which I guess is column(1)! Also
check that you haven't changed your bound field value! However it is
more correct to only display the Customer details at run time rather
than storing them in the second table (replication and denormalisation
of your data) You should always aim to try to store data once!

It would also be worth checking the relationships window as that may
explain why removing the required property has no real effect, it is
likely that an explicit constraint has been made between the two
tables!

Good luck
purpleflash
+++++++++++++++ +++++++++++++++ ++++++++++

This results in the name of the client (contact) autopopulating when I
select the customer id. However, when I try to save the form I get this
error message: The field 'Assignment Data Sheet.Rec'd From' cannot
contain a Null value because the Required property for this field is
set to True. Enter a value in this field.
>
I tried removing the required property for that field from the
assignments table (my feeble attempt at troubleshooting ) and the
client's name that autopopulated in the form still does not appear in
the table. This is where I am stuck.
>
(Of course, I have other data that needs to autopopulate too. I just
need to make sure the method works before I add the additional code in
the row source of the CUST ID control.)