Changing data before an update...

[jbfraleigh]

I'd like to be able to format the text that a user enters before it is saved into a table, but I've been unsuccessful to date.

I thought that I might be able to use the BeforeUpdate event. Unfortunately, Access won't let me modify the field that it is about to update (it generates error code 2115).

Any thoughts on how I can modify the data after the user is done with it, but before Access saves it to the table???

Things I've tried (or thought about):

1) LostFocus or OnExit event - Executes after the BeforeUpdate event.

2) OnChange event - Will execute (and modify the data) while the user is still entering data.

3) KeyPress event - The control may lose focus before the user has finished entering data (example: the user types a few characters, notices an error in another field and clicks to that field to fix it). This will trigger an update event when the data may not be valid...

Thanks

 

[Elana]

I insert the format code under the AfterUpdate event for each control I want to format. Seems to work just fine.

 

[Pat Hartman]

Access gets confused when you refer to fields in code when the controlsource name (data tab) is the same as the control name (other tab) in the properties sheet.

I modify the control names after the wizard does the initial build of the form to be:
txtTABLE_FIELD_NAME.

That way, I can refer to the control as me.txtTABLE_FIELD_NAME and to the field in the record source as me.TABLE_FIELD_NAME.

 

[joebater]

Hi all,

This isn't an answer I'm afraid - I'm having very similar problems!

I have a form with a combo box and a text box. I would like the value selected from the combo box to be written to the test box.

I have code:

Dim strComboValue as String 'form module variable

Private Sub Combo1_AfterUpdate()
strComboValue = Combo1.Value
txtTextBox1.SetFocus
txtTextBox1.Text = strComboValue
End Sub

When the code gets to the line "txtTextBox1.Text = ..." an error 2115 is reported. Neither BeforeUpdate nor ValidationRule properties have been altered in anyway (this has been stripped down to a virgin form!)

Reading through the other answers doesn't help that much - they all seem to be doing even more complicated things than I am!!!

I would be pathetically grateful for any assistance!

blue skies
j.

 

[indesisiv]

Can you use

Private Sub Combo1_AfterUpdate()
txtTextBox1.SetFocus
txtTextBox1 = me.combo1.Value
End Sub

If you do not reference the .text bit of the text box it seems to work fine.

Hope this helps

Steve

 

[Dave Eyley]

For data input it is prudent to check on the quality of the data before it gets saved to tables.

I have often used unbound fields on the input form to gather the information, do the checks, and then save the record to the table(s). This way, you are in complete control of table records.

The downside to this, and there is always a downside, is that it involves more code. And you have to think more about the possible input errors so that you catch them all. That's where the fun starts.

Creating a database is a piece of cake - it's making it fool-proof that's the difficulty.

 

[Rich]

Why would you want the value selected in a combo to also be displayed in a text box?

 

[joebater]

Hi

Thanks for your answers! I'm not able to check right now as the AutoExec macro which brought up the Splash form is currently killing the database and windows! (considering all it is does is DoCmd.OpenForm "frmSplash", I'm quite impressed!!!)

Once that's sorted I'll give it a try - one thing I'm confused about is that I'm not (intentionally) putting any data into tables but rather using the combo box to select a particular item, then putting that item into a textbox so that the user is kept with a reminder of what has been selected, and that a local copy is kept to be used as a term in an SQL generated query at a later point...

While I don't question the validity of Dave's point, I didn't think I was altering any of the underlying data... Am I?

blue skies
j.

 

[joebater]

Rich,

Perhaps stylistically it is a bit redundant, but I don't think it's the heart of the issue. The textbox is filled with the selected value from the combo box despite crashing (according to the debugger) at the line which sets the .text value...

Still mystified!

blue skies
j.

 

[Rich]

Me.Text1=Me.MyCombo where the value returned will reflect the bound column