Full Version: Writing Form Data To Table
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form which, when completed all the data is written to a table with a command button. I have a couple of unbound textboxes which also need to be written to the table at the same time. They have to remain unbound because they pull their data from some of the comboboxes on the form. I'm pretty sure there is a simple vba code that can do this, I just can't seem to find it. For the sake of an example, one of my textboxes is txtIDNum, and the table is called tblPoints and the data needs to be written to tblPoints.IDNum.
It is probably not a good idea to duplicate storage of a piece of data. Whenever you need it, you can get it via a query or DLookup or some other way. However, I will answer your question.
The way I do this is via a piece of code in the BeforeUpdate event of the form. You'll need to open the code window to do it.
First, go ahead and bind the unbound text boxes to the correct field in the table.
Then, in the BeforeUpdate event of the form itself, in the code window, this code will do what you need:
Me.NameOfTextBox = Me.NameOfComboBox
It is not clear to me whether or not you are getting each piece of data from the bound column of the combo boxes, or from a different column. You may need to reach into the columns collection of the chosen row.
If that is so, then you can get at the columns collection like this:
Me.NameOfTextBox = Me.NameOfComboBox.Column(1)
Remember that the columns collection of a combo box is ZERO-based, so column(1) is actually the second column in the chosen row.
m not sure it's true that they need to be unbound or that the information actually needs to be written to the table. It's hard to tell without specifics. But if instead of pulling the value into the textbox from the combo, you pushed it from the combo to the textbox (in the AfterUpdate event) then your textbox could be bound. On the other hand, if your combobox value comes from another table, then there is probably no need to actually store it again, you can join it in a query.
But without more explanation, I can't say for sure. It's just that in all my years in Access (15+), I've never run into a situation where there wasn't a better solution.
I'm sure you're right about duplicating data. I tend to go for the overkill.
Thank you
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.