473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Text Autopopulated in Form Doesn't Display in Table

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.CustomerID, 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.)

Dec 19 '06 #1
4 2722
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******@hotmail.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.CustomerID, 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.)
Dec 19 '06 #2
rs******@hotmail.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.CustomerID, 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.)
Dec 20 '06 #3
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.CustomerID, Clients.Contact FROM Clients;

This apparently means that the name of the client (Clients.Contact)
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******@hotmail.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.CustomerID, 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.)
Dec 20 '06 #4


rs******@hotmail.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.CustomerID, Clients.Contact FROM Clients;

This apparently means that the name of the client (Clients.Contact)
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******@hotmail.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.CustomerID, 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.)
Dec 21 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: dmiller23462 | last post by:
Hi guys....I have absolutely NO IDEA what I'm doing with Javascript but my end result is I need two text boxes to stay hidden until a particular option is selected....I've cobbled together the...
11
by: bala | last post by:
hi!!! i need to display a disclaimer which is two page in length in a word document. i also need to format the text. the idea is something as follows on opening the application, a form which...
9
by: Susan Bricker | last post by:
Greetings. I am having trouble populating text data that represents data in my table. Here's the setup: There is a People Table (name, address, phone, ...) peopleID = autonumber key There...
6
by: Alpha | last post by:
I have several textboxes that I need to chang the text when the selection row is changed in a datagrid. I have the following code. This textbox displayes the initial selection but when I click on...
11
by: jimstruckster | last post by:
I have a table with 10 rows, I want all rows except for the first to be hidden when the page first opens up. If the user puts a value in a text box in the first row then I want the second row to...
3
by: acecraig100 | last post by:
I am fairly new to Javascript. I have a form that users fill out to enter an animal to exhibit at a fair. Because we have no way of knowing, how many animals a user may enter, I created a table...
2
by: jw01 | last post by:
I have a form in which there is one combo box and three text boxes: Combo Box: -> Item A -> Item B -> Item C TextBox1: TextBox2: ...
1
by: littlealex | last post by:
IE6 not displaying text correctly - IE 7 & Firefox 3 are fine! Need some help with this as fairly new to CSS! In IE6 the text for the following page doesn't display properly - rather than being...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.