Help in clearing form data!!!

[Phille]

Hi!

I'm loosing my nerves!!! I normaly use Access 2003 but for the moment Access 2007 Trial and i'm not familiar with the VBA language nor SQL.

I'm making a form where i add data into text/list boxes and then press a button to run an append query to have it inserted into a table. Simple, yes but how can i make the data to clear from my form on for example mouse up (when I have clicked the button). I know one can use the setvalue command by using macros, but the pain in specifing every single object and having "millions" of macros isn't really an attractive option. So couldn't it be done using the expression builder??

Tank you

[MMcCarthy]

Hi!

I'm loosing my nerves!!! I normaly use Access 2003 but for the moment Access 2007 Trial and i'm not familiar with the VBA language nor SQL.

I'm making a form where i add data into text/list boxes and then press a button to run an append query to have it inserted into a table. Simple, yes but how can i make the data to clear from my form on for example mouse up (when I have clicked the button). I know one can use the setvalue command by using macros, but the pain in specifing every single object and having "millions" of macros isn't really an attractive option. So couldn't it be done using the expression builder??

Tank you

In VBA the following command will clear all unbound controls

Me.Refresh

Mary

[Phille]

Thank you for the fast response,

I try to add the command but when I click my button the screen flickers slightly but nothing happens, my textbox still has the text i wrote???

Any advice?

Thanks

[nico5038]

Try:
me.RecordSource=me.RecordSource

Looks funny, but should work

Nic;o)

[Phille]

Still completly lost.

I've made a new form thats not bound to enything added a textbox (unbound) and a button. I set the OnClick to both Me.Refresh and me.RecordSource=me.RecordSource.

------------------------------------------------------------------
Private Sub Command2_Click()
Me.Refresh
End Sub
------------------------------------------------------------------
Private Sub Command2_Click()
Me.RecordSource=Me.RecordSource
End Sub
------------------------------------------------------------------

So what am i doing wrong???

[nico5038]

For emptying a field on an unbound form use:

Me.Fieldname = ""
Me.refresh

Nic;o)

[Phille]

yiiiihaaa!!!

It worked thanks everybody, 2 days of misserable pain is over.

If someone still has a command for clearing the whole record/form please post but with this I will already come a long way.

Thanks again

[NeoPa]

This routine (Public in a non-object module) should do it for you.
There is flexibility to treat different object types differently with a small change to the Select Case statement.

'ClearUnbound empties all controls on an unbound form.
Public Sub ClearUnbound(frmMe As Form)
    Dim varCtrl As Variant
 
    For Each varCtrl In frmMe.Controls
        With varCtrl
            Select Case .ControlType
            Case acCheckBox, acComboBox, acListBox, acTextBox
                .Value = Null
            End Select
        End With
    Next varCtrl
End Sub

[Phille]

Thanks

But I can't get it to work it starts whining about the privet sub

Private Sub Command45_Click()
'ClearUnbound empties all controls on an unbound form.
Public Sub ClearUnbound(frmMe As Form)
    Dim varCtrl As Variant
 
    For Each varCtrl In frmMe.Controls
        With varCtrl
            Select Case .ControlType
            Case acCheckBox, acComboBox, acListBox, acTextBox
                .Value = Null
            End Select
        End With
    Next varCtrl
End Sub
End Sub

Please remember that for the moment being, Iknow nothing about VBA

[NeoPa]

That's because you're not doing it quite right (All right - it's all wrong).
That's not a problem though, we all have to start somewhere.
Follow these instructions :

1. Open the database in Access.
2. Switch to the VBA Window (Alt-F11).
3. Insert / Module.
4. Copy the ClearUnbound procedure in.
5. Switch back to Access (Alt-F11 again).
6. Open your form in Design Mode.
7. Right-click on your Command45 control and select Build Event...
8. Type in the code below :
   

   Call ClearUnbound(frmMe:=Me)

9. Debug / Compile ... (Project Name).
10. Save.

[Phille]

Thanks allot

I used to be a computer administrator at a small firm but never had the need of programming, so I just let it be. Then one day someone came in and asked me if I knew anything about the "For Dummies" series of books (which I didn't), but I answerd "Oh them, they are for morrons".

Guess what! I just ordered 400 pages of "Programming Access 2007 For Dummies"

What do we learn from this? Just keep your mouth shut or your a morron : )

Anyways, Thanks it worked like a charm, and you have lightened a few lights for me, though have quite a few more to go and I'll be there.

[NeoPa]

Well, we'll be happy to help light you on your way :)
Oh, and by the way, do you think we all haven't been there or thereabouts ourselves once?

[Phille]

Yes I know everyone has been here, it was just the preassure valve that bursted.

You know the fealing? You think you know what you are doing but it just won't work, copy/pasting, reading helpfiles etc for hours.

This is called anger management : )

[MMcCarthy]

Well, we'll be happy to help light you on your way :)
Oh, and by the way, do you think we all haven't been there or thereabouts ourselves once?

Speak for yourself. I always knew everything. I was borned that way :D

[NeoPa]

Yes I know everyone has been here, it was just the preassure valve that bursted.

You know the fealing? You think you know what you are doing but it just won't work, copy/pasting, reading helpfiles etc for hours.

This is called anger management : )

I suppose that's one way of doing it - if you can't find anyone nearby to punch I suppose :D

[NeoPa]

Speak for yourself. I always knew everything. I was programmed that way :D

Fair point Mary.

[MMcCarthy]

Fair point Mary.

Don't think I didn't notice the edit. How double damn dare you. :D

[NeoPa]

Don't think I didn't notice the edit. How double damn dare you. :D

Well - it was worth a try :D (I didn't tamper with your post notice).

[Phille]

Hi again

So! I haven't yet receved my book so mean while I'm still stuck knowing a little less than nothing about VBA programming.

Could there be anything done about the code giving an error when there are controlled objects on the form. For the moment I have a textbox that gets data depending of values from other controls. For example just ignoring those objects.

Thanks in advance

[NeoPa]

I've changed the original code somewhat. Considering this is a function to clear unbound controls, it's only right that there should be a check to see if the control is unbound ;)
See if this works better for you.

'ClearUnbound empties all controls on an unbound form.
Public Sub ClearUnbound(frmMe As Form)
    Dim varCtrl As Variant
 
    For Each varCtrl In frmMe.Controls
        With varCtrl
            Select Case .ControlType
            Case acCheckBox, acComboBox, acListBox, acTextBox
                If IsNull(.ControlSource) Then .Value = Null
            End Select
        End With
    Next varCtrl
End Sub

[Phille]

Thanks for the response but now it dosen't clear anything??
Any other suggestions?

Thanks

[NeoPa]

Sorry. I expect the check of (.ControlSource) should be for an empty string rather than Null. Try this amended version :

'ClearUnbound empties all controls on an unbound form.
Public Sub ClearUnbound(frmMe As Form)
    Dim varCtrl As Variant
 
    For Each varCtrl In frmMe.Controls
        With varCtrl
            Select Case .ControlType
            Case acCheckBox, acComboBox, acListBox, acTextBox
                If .ControlSource = "" Then .Value = Null
            End Select
        End With
    Next varCtrl
End Sub

[Phille]

IT WORKS!!!

Thanks a million : )

[NeoPa]

Everyone sounds surprised when my code works! :D

Seriously, I'm pleased it's done the trick.