Passing Value from unbound text box to a table from a Data Entry Form
I have what I think should be a simple question, but I'm probably making it harder than it needs to be.
I'm hoping to set up a form that allows an end-user to input financial allocations on a person-by-person basis for financial commitments we have. A person must be entered into the DB before they can be allocated $. The cbo_PersonNum referred to below is driven from the list of persons who were entered into the DB.
I have a value cbo_PersonNum on a data entry form that displays four values:
1) Person Number
2) Last Name
3) Preferred Name
4) Department
When a value is selected, I want to pass the other values to three separate text boxes below cbo_PersonNum. So, after a value has been selected I want
1) txt_LastName = cbo_PersonNum.Column(1)
2) txt_PreferredName = cbo_PersonNum.Column(2)
3) txt_Department = cbo_PersonNum.Column(3)
I have the part where the Text boxes display the value (the text box control source(s) is the the right side of the equations above). However, when I save the record, since the Text Boxes are essentially unbound (Control Source is not a field on the underlying table), the values do not pass to the table, so this is where I need help. I need to pass these values to the table and be sure that the values cannot be changed from what the cbo_PersonNumber passes.
I'm open to considering other object types than a text box, I just need to pass the values into the table, even though may be better database normalization not to do so.
Thanks for any help.
I'm hoping to set up a form that allows an end-user to input financial allocations on a person-by-person basis for financial commitments we have. A person must be entered into the DB before they can be allocated $. The cbo_PersonNum referred to below is driven from the list of persons who were entered into the DB.
I have a value cbo_PersonNum on a data entry form that displays four values:
1) Person Number
2) Last Name
3) Preferred Name
4) Department
When a value is selected, I want to pass the other values to three separate text boxes below cbo_PersonNum. So, after a value has been selected I want
1) txt_LastName = cbo_PersonNum.Column(1)
2) txt_PreferredName = cbo_PersonNum.Column(2)
3) txt_Department = cbo_PersonNum.Column(3)
I have the part where the Text boxes display the value (the text box control source(s) is the the right side of the equations above). However, when I save the record, since the Text Boxes are essentially unbound (Control Source is not a field on the underlying table), the values do not pass to the table, so this is where I need help. I need to pass these values to the table and be sure that the values cannot be changed from what the cbo_PersonNumber passes.
I'm open to considering other object types than a text box, I just need to pass the values into the table, even though may be better database normalization not to do so.
Thanks for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Never stated it was an invalid request. I just strongly recommend against it. Since there is no way to guarantee that someone won't change the text values associated with (LastName, PreferredName, and Department) in this second table, you could end up with data integrity issues.
If you really need to store those values in the same table as PersonNum, then bind the three textboxes to the associated fields in the data table. Then, in the AfterUpdate event of cbo_PersonNum, run the three lines of code you mentioned above. They will update those text fields. The only other thing I would do is Lock those three textboxes so the user cannot manually change those values except by selecting a new Person from cbo_PersonNum.
If you really need to store those values in the same table as PersonNum, then bind the three textboxes to the associated fields in the data table. Then, in the AfterUpdate event of cbo_PersonNum, run the three lines of code you mentioned above. They will update those text fields. The only other thing I would do is Lock those three textboxes so the user cannot manually change those values except by selecting a new Person from cbo_PersonNum.
If, for some reason, you cannot bind those three fields, you could create an UPDATE query that will fill those three fields in in the FORM_AfterUpdate event.
Private Sub Form_AFTERUPDATE
strSQL = "UPDATE tbl2 SET [LastName] = """ & me.txtLastName & """, " _
& "[PreferredName] = """ & me.txtPreferredName & """, " _
& "[Department] = """ & me.txtDepartment & """ " _
& "WHERE [ID] = " & me.txtID
Currentdb.Execute strSQL, dbfailonerror
End Sub
Private Sub Form_AFTERUPDATE
strSQL = "UPDATE tbl2 SET [LastName] = """ & me.txtLastName & """, " _
& "[PreferredName] = """ & me.txtPreferredName & """, " _
& "[Department] = """ & me.txtDepartment & """ " _
& "WHERE [ID] = " & me.txtID
Currentdb.Execute strSQL, dbfailonerror
End Sub
ASKER
I did just that (changed the after update property of cbo_PersonNum to fill the text boxes) then bound the text boxes to the underlying table.
ASKER