?
Solved

Passing Value from unbound text box to a table from a Data Entry Form

Posted on 2010-11-09
5
Medium Priority
?
1,583 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:UTSWPeds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1050 total points
ID: 34094809
I would not store that information in this second table.  Store the [Person Number] that appears to be bound to cbo_PersonNum.  With that, and your Person table, you can always generate the values you want in a query.

However, when working with a bound form, you will need to use the forms current event to populate those same textboxes with the values of the various columns that are in the combo boxes RowSource, but which dont get displayed.

So, in addition to having those three lines of code in the cbo_PersonNum_AfterUpdate event, you need to put those same three lines in the Form_Current event
0
 

Author Closing Comment

by:UTSWPeds
ID: 34094918
There is a reason I want to pass the values directly into the table form the form, as was mentioned in the initial question.  Advice on generally accepted programming techniques is appreciated, but in some instances there is a reason why there is an exception, and it should not be assumed that it is an invalid request.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 34095325
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 34095353
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
0
 

Author Comment

by:UTSWPeds
ID: 34096383
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.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question