Default Value Blues

[dedub]

OK, I've scoured the forums and read all the help in access I can read but I can't quiet come up with my solution.

I simply want to statically assign a field in my table from a text box after I go to a new record. In other words, my form opens... I type text in a box that is bound to a field, I go to my next record and it magically appears again and again. (sarcasm after a day of pulling my hair out). I can do it all day long by putting text in the default value properties box but I need to do it at run time because each day it changes and I don't want to type it in 100 times. I tried doing with code from examples on this forum. It worked but only for 1 record. I could not globally set the default value of that text box at run time for some reason.
any suggestions?

 

[Wiz47]

OK, I've scoured the forums and read all the help in access I can read but I can't quiet come up with my solution.

I simply want to statically assign a field in my table from a text box after I go to a new record. In other words, my form opens... I type text in a box that is bound to a field, I go to my next record and it magically appears again and again. (sarcasm after a day of pulling my hair out). I can do it all day long by putting text in the default value properties box but I need to do it at run time because each day it changes and I don't want to type it in 100 times. I tried doing with code from examples on this forum. It worked but only for 1 record. I could not globally set the default value of that text box at run time for some reason.
any suggestions?

Private Sub CboReport_Title_1_DblClick(Cancel As Integer)
    On Error GoTo Err_Update_Records
    Dim strsql As String

    strsql = "UPDATE Employees SET Employees.Report_Title_1 = Forms!Employees!CboReport_Title_1"

    DoCmd.RunSQL strsql

Exit_Err_Update_Records:
    Exit Sub

Err_Update_Records:
    If Error = 3059 Then
        Resume Exit_Err_Update_Records
    End If
End Sub

I have this run on the doubleclick event for the textbox in one of my forms where I want to change the value for that field in ALL the records in the table. I use the doubleclick event to ensure that the user REALLY wants to do this as it DOES change it to whatever they type in for every record.

 

[dedub]

Wiz, I tried your code and it worked but there are two things a little off that will keep me from using it.

1. I don't want to change ALL the records at one time.
2. I don't want to be warned about changing records ( because I don't want to change them in bulk.)

What I want to do is set it once in my text box and forget it. It gets copied over and over with no user intervention. Then if a user want to manually change it, he does, and then that value gets copied thereafter, over and over until it is either changed again, or the dB is closed. I would think it would be a simple text box copy and paste right when the record cycles to a new one but boy was I wrong.

 

[RuralGuy]

Here's a suggestion from MS:
How to fill a record with data from a previous record automatically in Access 2000 and 2002 and Office Access 2003

 

[dedub]

Gosh, it's close but.... Microsoft is statically assigning a value and hiding the text box, and using a module to do it. That is more work than setting the default value in properties.
In VB it would be easy, why is VBA difficult?
Isn't there a simple * on new record, text1.text = text1.text * and what ever was in the text box gets copied over to the next records field?

 

[RuralGuy]

Isn't there a simple * on new record, text1.text = text1.text * and what ever was in the text box gets copied over to the next records field?

...simply stated, NO! You could also try setting the Default value of your control in the AfterUpdate event of the form. This would have the effect of making the last value of that control the default value for that control in the next record.

 

[dedub]

Don't kid yourself Rural, there is a simple way and you showed me.

Private Sub Form_AfterUpdate()
from.SetFocus
Me!from.DefaultValue = from.Text
Text2.SetFocus
End Sub

All I had to do was set focus to the control and then put it where I wanted it.
I think I tried every possible combination of DefaultValue except that one.
Rural you made my night
Thanks

 

[RuralGuy]

Don't use the Text property, use the Value property instead. The Text property requires the control to have the focus and the .Value propery does not and is the default property so you do not need to specify it if you don't want.

Private Sub Form_AfterUpdate()
Me.from.DefaultValue = Me.from
End Sub

 

[dedub]

You bet I will, and thanks again!

 

[Wiz47]

Wiz, I tried your code and it worked but there are two things a little off that will keep me from using it.

1. I don't want to change ALL the records at one time.
2. I don't want to be warned about changing records ( because I don't want to change them in bulk.)

What I want to do is set it once in my text box and forget it. It gets copied over and over with no user intervention. Then if a user want to manually change it, he does, and then that value gets copied thereafter, over and over until it is either changed again, or the dB is closed. I would think it would be a simple text box copy and paste right when the record cycles to a new one but boy was I wrong.

Sorry, I misunderstood your request. It sounded like you wanted to change all the records based on the new value. I see that you wanted to only change the current record and subsequent updates until the value changed again. I think RG's solution was right on the money - I'm glad you got it sorted out.