A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Autopopulate Problem, i.e., Null Value



 
 
Thread Tools Display Modes
  #1  
Old December 18th, 2006, 06:31 PM posted to microsoft.public.access.forms
rszebras
external usenet poster
 
Posts: 8
Default Autopopulate Problem, i.e., Null Value

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

Cust id is a control box. I entered the query in row source. Then I went to
the client's name control. It was previously a control box with a drop down
menu. I converted it to a text box and set the control source of the text
box. Tested the form and when cust id is selected the client's name is
autopopulated into the text box.

Problem: When I complete the form entry and try to save it, I receive the
following 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 the field.

How do I resolve this Null value problem in the text box I created?
--
rszebras
  #2  
Old December 18th, 2006, 07:16 PM posted to microsoft.public.access.forms
rszebras
external usenet poster
 
Posts: 8
Default Autopopulate Problem, i.e., Null Value

Please ignore my prior post. I figured out why I was getting the null value
problem. It's because the table associated with the form says that the field
is required. But that raises another problem.

The table that the form populates must have the client's name, address,
phone number, etc., stored there. If I use the following code in the control
source of the textbox to get the textbox to autopopulate, it doesn't do any
good if that data is not entered into the table associated with the form.
This is the code I'm using: =[CUST ID].[column](1)

Any ideas on how to fix this so the data that autopopulates actually goes
into the table?
--
rszebras
  #3  
Old December 18th, 2006, 09:01 PM posted to microsoft.public.access.forms
John Vinson
external usenet poster
 
Posts: 4,033
Default Autopopulate Problem, i.e., Null Value

On Mon, 18 Dec 2006 10:16:00 -0800, rszebras
wrote:

Please ignore my prior post. I figured out why I was getting the null value
problem. It's because the table associated with the form says that the field
is required. But that raises another problem.

The table that the form populates must have the client's name, address,
phone number, etc., stored there.


I think you're mistaken. It is almost NEVER necessary to copy data
redundantly from one table into another table. If you're assuming that
you must have all the information in one table in order to use it,
that assumption is simply wrong.

You're using a relational database. USE IT RELATIONALLY! Store the
name, address, etc. *once*, and only once, in the Client table where
it belongs. If you need that information in conjunction with data in
some other table, say for a Report, store the ClientID and use a Query
joining the Client table to this other table; base your Report *on
that query*, choosing your table's fields from your table, and the
client information fields from the client table.

John W. Vinson[MVP]
  #4  
Old December 18th, 2006, 10:01 PM posted to microsoft.public.access.forms
rszebras
external usenet poster
 
Posts: 8
Default Autopopulate Problem, i.e., Null Value

Thanks a lot for your input, John. Let me briefly describe the issue that
necessitates the storage of data in the second table. We have a clients table
that lists all the pertinent info about our clients. We have an assignments
table that lists each case we have received. We use a form to enter each new
assignment that is then stored in the assignments table.

I work for a forensic accident reconstruction firm and oftentimes our files
must be produced in legal proceedings. Let's assume we didn't "store" the
data in the assignments table but just accessed client info from the clients
table. If info changed about one of our clients, e.g., they transfer to a
different company, change their name, or get a new address, that new info
would update in the assignments table. But we need an unchanging record about
the case to establish the facts at the time.

So I am back to figuring out not only how to autopopulate the assignment
input form but have that data stored in the assignments table. 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 to:

=[CUST ID].Column(1)

This results in the name of the client (contact) autopopulating when I
select the customer id. However, it apparently does not get stored in the
assignments table, as I removed the required property for that field from the
assignments table and the client's name that autopopulated in the form 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


"John Vinson" wrote:

I think you're mistaken. It is almost NEVER necessary to copy data
redundantly from one table into another table. If you're assuming that
you must have all the information in one table in order to use it,
that assumption is simply wrong.

You're using a relational database. USE IT RELATIONALLY! Store the
name, address, etc. *once*, and only once, in the Client table where
it belongs. If you need that information in conjunction with data in
some other table, say for a Report, store the ClientID and use a Query
joining the Client table to this other table; base your Report *on
that query*, choosing your table's fields from your table, and the
client information fields from the client table.

John W. Vinson[MVP]

  #5  
Old December 19th, 2006, 09:26 AM posted to microsoft.public.access.forms
Tank
external usenet poster
 
Posts: 45
Default Autopopulate Problem, i.e., Null Value

Hi rszebras,

In studying your comments about autopopulating certain fields in the
Assignments table with values coming from the Clients table, (e.g. name,
address, telephone, etc.), I am assuming you have fields in your Assignments
table that match up with the fields in the Clients table. I’m assuming those
common fields may have the same names or slightly different names but are of
the same type in structure and are reserved for identical values.

For example, in your Clients table, you may have Fname, Mname, Lname,
Street, City, State, Zip, Telephone.
In your Assignments table, you may have Firstname, MiddleName,LastName,
StreetAddress, StateCode, PostalCode, Phone.

Since you’re using a text box named Cust ID (better to name it Cust_ID
without spaces) in your “Assignments data entry form” (whose record source is
the Assignments table), to display name, address, telephone, etc. from the
Clients table, you can use an Event Procedure in the CustID text box to copy
the values from the clients table to the Assignments table, assuming, as John
Vinson recommended and which I believe you are doing, that the two tables are
linked in some fashion that might resemble, [Clients].[CustID] =
[Assignments].[Cust_ID].

The transfer of these values can be accomplished in the following way:

In form design view (e.g. “Assignments Data Entry Form”), click the text
box, “CustID” and open the Property Sheet. Click the Event tab in the
Property Sheet and place your cursor on the line, “After Update”. When your
cursor is on that line, you will see two control buttons on the far right
side of that line. Click the first button and highlight the words, “Event
Procedure” on the drop-down list. Then click the second button (ellipsis…)
and you will be taken to the Event Procedure screen. Between the two
existing lines, “Private Sub Cust_ID_After Update () and “End Sub”, add your
event procedure, as shown below.

Let’s assume the Clients table uses “CustID” and your linked ID in the
Assignments table is named “Cust_ID”, and you have the following sample
fields whose values you want to transfer,

CLIENTS TABLE: FName, MName, LName, Street, City, State, Zip, Telephone.
ASSIGNMENTS TABLE: Firstname, MiddleName, LastName, StreetAddress, Town,
StateCode, PostalCode, Phone.

HERE IS A SUGGESTED EVENT PROCEDU
*******************************
Private Sub Cust_ID_AfterUpdate()

FirstName = FName
MiddleName = MName
LastName = LName
StreetAddress = Street
Town = City
StateCode = State
PostalCode = Zip
Phone = Telephone

End Sub
******************************

Of course, if you are using the same field names in both tables, you need to
use the [tablename].[fieldname] formatting Example: [Clients].[FName] =
[Assignments].[FName]

If you definitely want to lock in your value transfers (in the event someone
comes along later to mistakenly reenter the same Cust_ID number or a
different one, you could set up an expression for each field, such as,

If IsNull (FirstName) Then
Firstname = FName

You may not need to transfer values exactly as they are in the Clients
table. For example, you may need only the full name in your Assignments
table, not broken down by first name, middle name, last name. You could thus
batch some of your fields, if appropriate, for your Assignments usage at this
transfer stage, if practical. For example, you could have customized fields
in the Assignments table, such as “FullName”, FullAddress”, etc.

FullName = [Fname] &” “& [Mname]&” “&[Lname] or
FullAddress = [StreetAddress] & vbNewLine & [Town] &”, “& [StateCode] &” “&
[PostalCode]

The FullAddress example shows how you can make a mailing label-like format
by stringing several fields together --- all from the AfterUpdate () Event
Procedure of the Cust_ID text box in your “Assignments Data Entry Form”.

But of course, practically everything I say above is going contrary to good
database development, as John Vinson, and many, many others emphasize.
However, you’re reasons for duplicating the data is also sound. Building the
database to meet your company’s needs is paramount and on occasion may
supersede normal development protocols. I would only hope that not too much
information needs to be transferred in this fashion, otherwise you could be
faced with a labor-intensive operation, particularly if you have to
continually select certain fields for transfer record by record.
- - - -
Tank

  #6  
Old December 19th, 2006, 09:51 PM posted to microsoft.public.access.forms
rszebras
external usenet poster
 
Posts: 8
Default Autopopulate Problem, i.e., Null Value

Hi Tank:

Thanks so much for your detailed explanation. Let me just give you an
update. Ignore what I said about the data having to be "stored" in the
assignments table. I was told that's the way our database is set up. Wrong! I
went into the clients table and changed the last name of one of our clients
to test it. Voila! All the data in the assignments table changed as well. (Of
course, I've been doing my experiments with a copy of our database.)

Our company wanted everything to remain constant as described previously,
but that's not the way it was designed. Accordingly, I'm not changing it. It
stays the way it is. I was just asked to modify the database so when the
cust_id is selected, all the other data is autopopulated.

Now that you have the latest info, here are my comments. Your assumptions in
paragraphs 1 and 2 are correct. We have a clients table and an assignments
table and some of the fields are pretty much identical. In the next series of
paragraphs you describe using an event procedure in the After Update box. I
assume that procedure is valid in light of the new information I provided
above since later on you describe a procedure for "locking in values" and the
code to do that (which I am not going to do).

I plan to use your "suggested event procedure" as a model. First, however, I
want to point out that my CUST ID control in the assignment input form is not
a text box but a combo box. Does that make any difference with using the
event procedure in the After Update box?

Second, right now all the other controls for the company, address, phone,
etc., in the assignment input form are combo boxes with drop down menus.
Should I still use the procedure I referenced in my prior posts by setting
the row source query in the CUST ID control and the control source of the
textbox so the info I need appears in the form automatically?

Thanks, again, for taking time to provide such a detailed response. I really
appreciate your help, Tank.
--
rszebras


"Tank" wrote:

Hi rszebras,

In studying your comments about autopopulating certain fields in the
Assignments table with values coming from the Clients table, (e.g. name,
address, telephone, etc.), I am assuming you have fields in your Assignments
table that match up with the fields in the Clients table. I’m assuming those
common fields may have the same names or slightly different names but are of
the same type in structure and are reserved for identical values.

For example, in your Clients table, you may have Fname, Mname, Lname,
Street, City, State, Zip, Telephone.
In your Assignments table, you may have Firstname, MiddleName,LastName,
StreetAddress, StateCode, PostalCode, Phone.

Since you’re using a text box named Cust ID (better to name it Cust_ID
without spaces) in your “Assignments data entry form” (whose record source is
the Assignments table), to display name, address, telephone, etc. from the
Clients table, you can use an Event Procedure in the CustID text box to copy
the values from the clients table to the Assignments table, assuming, as John
Vinson recommended and which I believe you are doing, that the two tables are
linked in some fashion that might resemble, [Clients].[CustID] =
[Assignments].[Cust_ID].

The transfer of these values can be accomplished in the following way:

In form design view (e.g. “Assignments Data Entry Form”), click the text
box, “CustID” and open the Property Sheet. Click the Event tab in the
Property Sheet and place your cursor on the line, “After Update”. When your
cursor is on that line, you will see two control buttons on the far right
side of that line. Click the first button and highlight the words, “Event
Procedure” on the drop-down list. Then click the second button (ellipsis…)
and you will be taken to the Event Procedure screen. Between the two
existing lines, “Private Sub Cust_ID_After Update () and “End Sub”, add your
event procedure, as shown below.

Let’s assume the Clients table uses “CustID” and your linked ID in the
Assignments table is named “Cust_ID”, and you have the following sample
fields whose values you want to transfer,

CLIENTS TABLE: FName, MName, LName, Street, City, State, Zip, Telephone.
ASSIGNMENTS TABLE: Firstname, MiddleName, LastName, StreetAddress, Town,
StateCode, PostalCode, Phone.

HERE IS A SUGGESTED EVENT PROCEDU
*******************************
Private Sub Cust_ID_AfterUpdate()

FirstName = FName
MiddleName = MName
LastName = LName
StreetAddress = Street
Town = City
StateCode = State
PostalCode = Zip
Phone = Telephone

End Sub
******************************

Of course, if you are using the same field names in both tables, you need to
use the [tablename].[fieldname] formatting Example: [Clients].[FName] =
[Assignments].[FName]

If you definitely want to lock in your value transfers (in the event someone
comes along later to mistakenly reenter the same Cust_ID number or a
different one, you could set up an expression for each field, such as,

If IsNull (FirstName) Then
Firstname = FName

You may not need to transfer values exactly as they are in the Clients
table. For example, you may need only the full name in your Assignments
table, not broken down by first name, middle name, last name. You could thus
batch some of your fields, if appropriate, for your Assignments usage at this
transfer stage, if practical. For example, you could have customized fields
in the Assignments table, such as “FullName”, FullAddress”, etc.

FullName = [Fname] &” “& [Mname]&” “&[Lname] or
FullAddress = [StreetAddress] & vbNewLine & [Town] &”, “& [StateCode] &” “&
[PostalCode]

The FullAddress example shows how you can make a mailing label-like format
by stringing several fields together --- all from the AfterUpdate () Event
Procedure of the Cust_ID text box in your “Assignments Data Entry Form”.

But of course, practically everything I say above is going contrary to good
database development, as John Vinson, and many, many others emphasize.
However, you’re reasons for duplicating the data is also sound. Building the
database to meet your company’s needs is paramount and on occasion may
supersede normal development protocols. I would only hope that not too much
information needs to be transferred in this fashion, otherwise you could be
faced with a labor-intensive operation, particularly if you have to
continually select certain fields for transfer record by record.
- - - -
Tank

  #7  
Old December 20th, 2006, 06:25 AM posted to microsoft.public.access.forms
Tank
external usenet poster
 
Posts: 45
Default Autopopulate Problem, i.e., Null Value

Hi rszebras,

Whether you use a plain text box or a combo box, you still will be inputting
a value into the appropriate field, and the Event Procedure will work.

I’m a bit surprised with your description that “all the other controls for
the company, address, phone, etc., in the assignment input form are combo
boxes with drop down menus”.

The use of combo boxes with drop down menus (i.e. values) tells me that the
assignment input form actually is designed for the user to manually duplicate
the information that already exists in the Clients table. As John Vinson was
pointing out, this is really unnecessary and not very design-advanced. It
would be best to re-design the input form, since you can take full advantage
of inputting the proper Cust_ID to pull in the appropriate data from the
clients table, as Joyn pointed out. Since you experimented and found that
your current assignment input form is doing that now, there’s really no
reason to have the combo boxes. But since they’re there, I guess you don’t
really have to get rid of them, other than it adds confusion as to how the
various text boxes get populated.

Since you decided not to alter the database, the Event Procedure is not
valuable to you, as it would physically add values to the appropriate fields
that exist in the assignments table (vs. simply displaying the values coming
from the Clients table). (Ref: “Our company wanted everything to remain
constant as described previously, but that's not the way it was designed.
Accordingly, I'm not changing it. It stays the way it is. I was just asked to
modify the database so when the cust_id is selected, all the other data is
autopopulated”).

Based on your new information, it appears you don’t have to do anything.
You are populating the appropriate field text boxes in your assignment input
form when you enter the proper Cust_ID for a given record.

You may want to make a copy of the database and experiment with the Event
Procedure and with John Vinson’s suggestions to see if the input form can be
improved.

Good luck,
- - - - -
Tank

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.