By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Help in clearing form data!!!

P: 22
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
Jan 17 '07 #1
Share this Question
Share on Google+
23 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Me.Refresh
Mary
Jan 17 '07 #2

P: 22
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
Jan 17 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Try:
me.RecordSource=me.RecordSource

Looks funny, but should work

Nic;o)
Jan 17 '07 #4

P: 22
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???
Jan 17 '07 #5

nico5038
Expert 2.5K+
P: 3,072
For emptying a field on an unbound form use:

Me.Fieldname = ""
Me.refresh

Nic;o)
Jan 17 '07 #6

P: 22
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
Jan 17 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. 'ClearUnbound empties all controls on an unbound form.
  2. Public Sub ClearUnbound(frmMe As Form)
  3.     Dim varCtrl As Variant
  4.  
  5.     For Each varCtrl In frmMe.Controls
  6.         With varCtrl
  7.             Select Case .ControlType
  8.             Case acCheckBox, acComboBox, acListBox, acTextBox
  9.                 .Value = Null
  10.             End Select
  11.         End With
  12.     Next varCtrl
  13. End Sub
Jan 18 '07 #8

P: 22
Thanks

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command45_Click()
  2. 'ClearUnbound empties all controls on an unbound form.
  3. Public Sub ClearUnbound(frmMe As Form)
  4.     Dim varCtrl As Variant
  5.  
  6.     For Each varCtrl In frmMe.Controls
  7.         With varCtrl
  8.             Select Case .ControlType
  9.             Case acCheckBox, acComboBox, acListBox, acTextBox
  10.                 .Value = Null
  11.             End Select
  12.         End With
  13.     Next varCtrl
  14. End Sub
  15. End Sub
Please remember that for the moment being, Iknow nothing about VBA
Jan 19 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
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 :
    Expand|Select|Wrap|Line Numbers
    1. Call ClearUnbound(frmMe:=Me)
  9. Debug / Compile ... (Project Name).
  10. Save.
Jan 19 '07 #10

P: 22
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.
Jan 19 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
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?
Jan 19 '07 #12

P: 22
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 : )
Jan 19 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 19 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
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
Jan 20 '07 #15

NeoPa
Expert Mod 15k+
P: 31,186
Speak for yourself. I always knew everything. I was programmed that way :D
Fair point Mary.
Jan 20 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Fair point Mary.
Don't think I didn't notice the edit. How double damn dare you. :D
Jan 20 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
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).
Jan 20 '07 #18

P: 22
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
Jan 31 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. 'ClearUnbound empties all controls on an unbound form.
  2. Public Sub ClearUnbound(frmMe As Form)
  3.     Dim varCtrl As Variant
  4.  
  5.     For Each varCtrl In frmMe.Controls
  6.         With varCtrl
  7.             Select Case .ControlType
  8.             Case acCheckBox, acComboBox, acListBox, acTextBox
  9.                 If IsNull(.ControlSource) Then .Value = Null
  10.             End Select
  11.         End With
  12.     Next varCtrl
  13. End Sub
Jan 31 '07 #20

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

Thanks
Feb 1 '07 #21

NeoPa
Expert Mod 15k+
P: 31,186
Sorry. I expect the check of (.ControlSource) should be for an empty string rather than Null. Try this amended version :
Expand|Select|Wrap|Line Numbers
  1. 'ClearUnbound empties all controls on an unbound form.
  2. Public Sub ClearUnbound(frmMe As Form)
  3.     Dim varCtrl As Variant
  4.  
  5.     For Each varCtrl In frmMe.Controls
  6.         With varCtrl
  7.             Select Case .ControlType
  8.             Case acCheckBox, acComboBox, acListBox, acTextBox
  9.                 If .ControlSource = "" Then .Value = Null
  10.             End Select
  11.         End With
  12.     Next varCtrl
  13. End Sub
Feb 1 '07 #22

P: 22
IT WORKS!!!

Thanks a million : )
Feb 1 '07 #23

NeoPa
Expert Mod 15k+
P: 31,186
Everyone sounds surprised when my code works! :D

Seriously, I'm pleased it's done the trick.
Feb 1 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.