clear all controls on a subform

[imrosie]

Hello,

Does anyone have a simple cmd to clear all controls on Subform without using the SetFocus? Two of the controls are expressions, so they can't be set to Null. Then after clear return control to the main form?

thanks
Rosie

[Jim Doherty]

Hello,

Does anyone have a simple cmd to clear all controls on Subform without using the SetFocus? Two of the controls are expressions, so they can't be set to Null. Then after clear return control to the main form?

thanks
Rosie

Hi Rosie,

Call the following sub function from a button mounted on the main form. As you examine the sub function and calling sub it you will see that it loops through controls that it is working with (IN THE SUBFORM)

It sets to null all those fields where your textbox naming convention first three characters is 'txt' and skips over those controls it can't set to NULL without reporting an error hence the On Error Resume Next statement

(You could also achieve this using 'TypeOf' for txtbox control identification if you wished to but I am assuming you may wish to be more specific in the controls you wish to work with)

This example merely targets only those controls you specifically want to point to, hence the 'txt' prefix.

When you click the button in the main form the actual focus remains on the main forms command button.

If you wanted then to advance to a specific control after the clear click event you could include a command to setfocus or Goto a specific control of your choosing

 
Sub BlankTXTControls(f As Form)
' Copies Null to all controls on the form
' Assumes control names are prefixed by 'txt'
On Error GoTo Err_BlankTXTControls
Dim i As Integer, C As Control
For i = 0 To f.Count - 1
    Set C = f(i)
    If Left$(C.Name, 3) = "txt" Then
     C = Null
    End If
Next i
Exit_BlankTXTControls:
    Exit Sub 
Err_BlankTXTControls:
    MsgBox Err.Description
    Resume Exit_BlankTXTControls
End Sub
 

You then use the following code in the on click event of the cmdclear button which as you can see passes the name of the subform (in this case the subform is named Submain) and makes the CALL to the sub function doing the work.

 Private Sub cmdClear_Click()
On Error Resume Next 
Call BlankTXTControls(Me!Submain.Form)
 
'and here if you wanted to then advance to another control
'you would enter the setfocus command or the DoCmd GotoControl
'of your choosing
 
End Sub
 

Regards

Jim

[imrosie]

Hi Rosie,

Call the following sub function from a button mounted on the main form. As you examine the sub function and calling sub it you will see that it loops through controls that it is working with (IN THE SUBFORM)

It sets to null all those fields where your textbox naming convention first three characters is 'txt' and skips over those controls it can't set to NULL without reporting an error hence the On Error Resume Next statement

(You could also achieve this using 'TypeOf' for txtbox control identification if you wished to but I am assuming you may wish to be more specific in the controls you wish to work with)

This example merely targets only those controls you specifically want to point to, hence the 'txt' prefix.

When you click the button in the main form the actual focus remains on the main forms command button.

If you wanted then to advance to a specific control after the clear click event you could include a command to setfocus or Goto a specific control of your choosing

 
Sub BlankTXTControls(f As Form)
' Copies Null to all controls on the form
' Assumes control names are prefixed by 'txt'
On Error GoTo Err_BlankTXTControls
Dim i As Integer, C As Control
For i = 0 To f.Count - 1
    Set C = f(i)
    If Left$(C.Name, 3) = "txt" Then
     C = Null
    End If
Next i
Exit_BlankTXTControls:
    Exit Sub 
Err_BlankTXTControls:
    MsgBox Err.Description
    Resume Exit_BlankTXTControls
End Sub
 

You then use the following code in the on click event of the cmdclear button which as you can see passes the name of the subform (in this case the subform is named Submain) and makes the CALL to the sub function doing the work.

 Private Sub cmdClear_Click()
On Error Resume Next 
Call BlankTXTControls(Me!Submain.Form)
 
'and here if you wanted to then advance to another control
'you would enter the setfocus command or the DoCmd GotoControl
'of your choosing
 
End Sub
 

Regards

Jim

Jim,,,this is great...I'm going off to try this now. I'll report back for sake of others...I remember you (plate of spaghetti??)...I'm sure this will be good code. take care
Rosie

[imrosie]

Jim,,,this is great...I'm going off to try this now. I'll report back for sake of others...I remember you (plate of spaghetti??)...I'm sure this will be good code. take care
Rosie

Hi Jim,,,

I put the code in as follows:

Sub BlankTXTControls(f As Form)
On Error GoTo Err_BlankTXTControls
Dim i As Integer, C As Control
For i = 0 To f.Count - 1
    Set C = f(i)
    If Left$(C.Name, 3) = "txt" Then
     C = Null
    End If
Next i
Exit_BlankTXTControls:
    Exit Sub
Err_BlankTXTControls:
    MsgBox Err.Description
    Resume Exit_BlankTXTControls
End Sub
 

Private Sub clearsubform_Click()
On Error Resume Next
Call BlankTXTControls(Me![Order Details Subform].Form)
 

The name of the Me, main form is "Add an Order and Details"...
There is one combo box too on the subform. So far nothing happens...it just sits there with the controls still populated.
I"m trying make sure I've got the formatting right for the naming of the Me![subform].Form

thanks

[Jim Doherty]

Hi Jim,,,

I put the code in as follows:

Sub BlankTXTControls(f As Form)
On Error GoTo Err_BlankTXTControls
Dim i As Integer, C As Control
For i = 0 To f.Count - 1
Set C = f(i)
If Left$(C.Name, 3) = "txt" Then
C = Null
End If
Next i
Exit_BlankTXTControls:
Exit Sub
Err_BlankTXTControls:
MsgBox Err.Description
Resume Exit_BlankTXTControls
End Sub
 

Private Sub clearsubform_Click()
On Error Resume Next
Call BlankTXTControls(Me![Order Details Subform].Form)
 

The name of the Me, main form is "Add an Order and Details"...
There is one combo box too on the subform. So far nothing happens...it just sits there with the controls still populated.
I"m trying make sure I've got the formatting right for the naming of the Me![subform].Form

thanks

Rosie,

Remember this code targets only those controls where the name of any control to be cleared is prefixed with the characters 'txt' it will not work otherwise

If you wish to clear the combo on the subform then provided the name of the combo begins with the characters txt...... it will clear that also (that is why I did not go for the TYPEOF identification element of controls because I assumed you might have have a miraid of different control type ie combos textboxes and so on)

The code does work I have tested in this end :)

Regards

Jim

[imrosie]

Rosie,

Remember this code targets only those controls where the name of any control to be cleared is prefixed with the characters 'txt' it will not work otherwise

If you wish to clear the combo on the subform then provided the name of the combo begins with the characters txt...... it will clear that also (that is why I did not go for the TYPEOF identification element of controls because I assumed you might have have a miraid of different control type ie combos textboxes and so on)

The code does work I have tested in this end :)

Regards

Jim

Thanks Jim, I realize you tested it...so I assumed it must have worked for you.
Yes I was aware of the 'txt'...

The code doesn't clear the txt's so could it be that I've improperly formatted the string for the subform (which has spaces inthe name, Order Details Subform)?

In this part, which is the only area I really changed. thanks again.

Call BlankTXTControls(Me![Order Details Subform].Form)

Rosie

[Jim Doherty]

Thanks Jim, I realize you tested it...so I assumed it must have worked for you.
Yes I was aware of the 'txt'...

The code doesn't clear the txt's so could it be that I've improperly formatted the string for the subform (which has spaces inthe name, Order Details Subform)?

In this part, which is the only area I really changed. thanks again.

Call BlankTXTControls(Me![Order Details Subform].Form)

Rosie

Rosie

You have correctly wrapped the subform name in square brackets because you have spaces so that is not where your problem lies it must be something else.if you get stuck PM me with your email address and I'll send you this small db here and you can compare what I have with what you should have if you get me otherwise we could be ping ponging on here for something that is relatively straight forward

Jim