I have a plethora of unbound controls on my form which I use to input data into a separate reporting table that's not bound to this form. The problem is that I need a way to trigger an event every time the record is changed on the form so I can save the data in the reporting table. The old RecordExit event would do this fine, but now that Microsoft decided to remove it from Access 2002, I'm left scrambling. I tried the BeforeUpdate event which seemed to be triggered every time I changed records but now I have to dirty a control that IS bound to the form's table. Any ideas? Thank you.
Unbound controls on form trigger record events
- Thread starter bb3261
- Start date
Not quite sure what you're describing here.
If I understand you correctly, you need a way to add records to a table without having any bound controls on your form because (I assume) they would be populated already when you open the form.
If it's simply a case of needing to add a NEW record to a table, then you can bind the form to the table and set the Data Entry property to Yes. Your TextBoxes will be clean when you open the form.
If you want to edit existing records and still have clean TextBoxes on your form then you might wish to explore using a Command Button (labeled 'Save', for example). In the On_Click Event behind this button, open a recordset based on your table - or a query, whichever - and use the methods available to update the record.
After you open the RecordSet (rst, for example)...
With rst
.Edit
!YourField = Me.YourUnboundTextBox
.Update
End With
I hope I'm not too far off the mark here. It's a little difficult to understand exactly what you're asking.
Shep
If I understand you correctly, you need a way to add records to a table without having any bound controls on your form because (I assume) they would be populated already when you open the form.
If it's simply a case of needing to add a NEW record to a table, then you can bind the form to the table and set the Data Entry property to Yes. Your TextBoxes will be clean when you open the form.
If you want to edit existing records and still have clean TextBoxes on your form then you might wish to explore using a Command Button (labeled 'Save', for example). In the On_Click Event behind this button, open a recordset based on your table - or a query, whichever - and use the methods available to update the record.
After you open the RecordSet (rst, for example)...
With rst
.Edit
!YourField = Me.YourUnboundTextBox
.Update
End With
I hope I'm not too far off the mark here. It's a little difficult to understand exactly what you're asking.
Shep
Thanks for trying to help, Shep. I'm sorry it was a little confusing. Here's the situation, hopefully explained more clearly:
I have a form which IS bound to a particular table, so some of the controls on the form are tied to that table. However, there are also other controls on the form which are not bound to this table, and I want to get the data from these unbound controls into a completely separate reporting table. I want this to happen whenever the user switches records or closes the form. I could use a command button to trigger some code, but I'd like it to happen automatically, hence the reliance on the BeforeUpdate event which isn't triggered if only the unbound controls are updated. Does that make any more sense? Thanks for the effort.
I have a form which IS bound to a particular table, so some of the controls on the form are tied to that table. However, there are also other controls on the form which are not bound to this table, and I want to get the data from these unbound controls into a completely separate reporting table. I want this to happen whenever the user switches records or closes the form. I could use a command button to trigger some code, but I'd like it to happen automatically, hence the reliance on the BeforeUpdate event which isn't triggered if only the unbound controls are updated. Does that make any more sense? Thanks for the effort.
Yep, understand fully now.
How about this? I tried it...seems to work:
Private Sub UnboundTextBox_AfterUpdate()
Me.BoundTextBox1 = Me.BoundTextBox1
End Sub
You could use this behind any or all unbound textboxes that might be edited.
It's rather simplistic and it might not be suitable or acceptable for you to mess with your bound data this way, but it does trigger the Before_Update event behind the form.
Shep
How about this? I tried it...seems to work:
Private Sub UnboundTextBox_AfterUpdate()
Me.BoundTextBox1 = Me.BoundTextBox1
End Sub
You could use this behind any or all unbound textboxes that might be edited.
It's rather simplistic and it might not be suitable or acceptable for you to mess with your bound data this way, but it does trigger the Before_Update event behind the form.
Shep
What about creating a subform to hold the data from the "unbound" table? You could then bind the subform controls to that other table and use the subform's events to trap the various record-related events. The you put the subform on the main form and remove the unbound controls which you've currently got.
If you wanted to do anything specific when the user clicked out of the subform region you've always got the LostFocus event of the main form's subform control.
Simon
If you wanted to do anything specific when the user clicked out of the subform region you've always got the LostFocus event of the main form's subform control.
Simon
