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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|