Return form control to the default value via vba function

[DRH192]

Hello All,

I am looking for a way of writing a single module that will loop through all the forms in my database and append code to all controls that are enabled text boxes.

I have basicly built a calculator system. Each calculator form has a number of unbound text boxes for variables to be entered into. All have default values. The default values are either strings, integers or double data types.

I want to write a function that when a text box is double clicked, its contents is reset to the default value.

Any help would be greatly appreciated.

Many Thanks

 

[Remou]

If you are prepared to use Function keys, or Control + Key, this can be done with very little code by taking advantage of the KeyPreview property. If you insist on the Double-Click event, it is probably easiest to create the code in the Immediate window and paste it into the form:

Sub CreateSubs()
DoCmd.OpenForm "frmForm", acDesign
Set frm = Forms!frmForm

For Each ctl In frm.Controls
    If ctl.ControlType = acTextBox Then
        strText = "Private Sub " & ctl.Name & "_DblClick(Cancel As Integer)" _
        & vbCrLf & vbTab & "Me." & ctl.Name & " = Null" _
        & vbCrLf & "End Sub"
        Debug.Print strText
    End If
Next

DoCmd.Close acForm, frm.Name
End Sub

 

[DRH192]

If function keys or control + keys achieve the same result of resetting the control to the default value, then yes, I would be happy to use them.

One problem, I don't know what function keys or control + keys are or how to use them?

 

[Remou]

Here is some information:

http://msdn2.microsoft.com/en-us/library/aa196210(office.11).aspx

This can be used like so:

Private Sub Form_Load()
    Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyF2
            [blue]Screen.ActiveControl = Null[/blue]
        Case vbKeyF3
            ' Process F3 key events.
        Case vbKeyF4
            ' Process F4 key events.
        Case Else
           ' MsgBox KeyCode
    End Select
End Sub

Get back if you run into problems.

 

[DRH192]

Thats a smart little trick, I like that a lot.

Now for the second part of the puzzle.... setting the value back to the default value of that control? I have tried doing this before and I couldn't get it to work.

Any ideas?

 

[Remou]

How about:

Me.txtX=Me.txtX.DefaultValue

 

[DRH192]

Thats great, I have it working now, but it only seems to work on one form?

I created the following piece of code:

Public Sub CustomKeyDown(frm As Form, KeyCode As Integer)

Dim ctl As Control
Dim currentCtl As String

Select Case KeyCode

Case vbKeyF2
frm.ActiveControl = ""
Case vbKeyF3
If left(frm.ActiveControl.DefaultValue, 1) = "=" Then
frm.ActiveControl = Eval(SF_splitRight(frm.ActiveControl.DefaultValue, "="))
Else
frm.ActiveControl = frm.ActiveControl.DefaultValue
End If
Case vbKeyF4

currentCtl = frm.ActiveControl.Name

For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Then
If ctl.Enabled = True Then
ctl.SetFocus
CustomKeyDown frm, vbKeyF3
End If
End If
Next ctl

frm.Controls(currentCtl).SetFocus

' Process F4 key events.
Case Else
' MsgBox KeyCode
End Select

End Sub

And call it from each of my forms like this:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
CustomKeyDown Me, KeyCode
End Sub

I have put this code into each of my forms, expecting it to work, but nothing works outside the form I first called CustomKeyDown from?

Any Ideas?

 

[DRH192]

ignore that last post, I left out setting keypreview to true. numpty!!!!