How to Clear Form Controls for Next Entry

should take you to a new record. If there is something more complicated about your form that won't allow this to work post the details about your form.

something that's really simple is to just place a control button into the form. Simply choose the control button option from the toolbox when in design mode for the form and access will walk you through the rest and write the VBA code for you!

Hi there MSeda and vkong85

Thank you both for your replies. MSeda, I think the code and everything for the form will be too much to post here. The form itself have 34 fields and there is quite a lot of code behind it. Surely there must be some code that empties all controls on a form regardless of your code?

vkong85, I have tried Access's Add New Record button, but it does not work. Seems like it is missing a trigger to empty all form controls.

MSeda only said to post your code if the code

DoCmd.GoToRecord , , acNewRec

didn't work, but it really should! Have you tried it yet? If you place it behind a button it will go to a new record, saving the record just entered at the same time.

Hi there missinglinq

Sorry, I misunderstood him. Right here is the code for the New Rocrd Button :

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub




I have placed this button on a form which I let Access create automatically and it does waht it is supposed to, which is clear all populated fields on the form.

When doing the same on the form I have created manually, this code refuses to run.

Hope this helps.

Can we see the code from the form you created manually, where it refuses to run? Also, make sure on this form that the AllowAdditions property is set to YES.

Hi there missinglinq

Right now I am propably going to sound extremely dumb, but I am not sure to look where it falls over, as all the code I have done for this page so far works fine. The only addition to the code I have made to try and empty all the controls on it is the code I pasted in my last post. When I do run this, nothing happens, no error or nothing.You might have to beat me with a stick in the right direction man . :-)

I have also checked the AllowAdditions property and it is set to YES.

Going to the new record will only clear bound fields - if some of the form's controls are unbound and handled in code, they won't be cleared

Damn!!

All mine are unbound and I also have various check boxes and radio buttons. So there is no way clearing it using VBA?

You could do something like this:


Dim ctl as Control

For Each ctl In me.Controls
If ctl.ControlType = acComboBox or ctl.ControlType = acTextBox 'Add other control types here
ctl.Value = Null
End If
Next ctl

Sequin, you are a Hedgehog God!

Well that clears just about all my form controls. So brill thanks for that man. The only problem now is that when I use this code to empty the controls and then try to add a new record, it errors with a massive error.

Here is part of it:

Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion errorand didn't add 1 record(s) to the table due to key violations,0 records due to lock violations, and 0 records due to validation rule violations.........

Huh?

That confuses me, make no mistake....

I was thinking, could this be because I created my forms manually rather than using Access wizards?

Propable a dumb question, but hey not hurt asking.

I was thinking, could this be because I created my forms manually rather than using Access wizards?

Propable a dumb question, but hey not hurt asking.

You really want to work hard.
As far as I've got it you have a completely unbound form.
All your present code is devoted to updating underlying table(s) ?
If so I can tell you that in a properly bound form you need to call Me.Refresh method or normally don't need any code at all.

If you want to proceed this way, the code below is quite close to what Form object do with controls when new record inserted.

Next time will learn how to emulate Navigation buttons. :-)

Good new - you will get out this much more experienced than those working through wizards. I'm serious. This is the best way to learn.

Howzit Fishval

Thanks a lot for your response. I will have a go at that code and see if it does the jobs.

Well, like they say , if your dumb then you gotta suffer.

I can only use Access wizard for very simple things like forms for maintaining lookup tables.

The reason I am doing everything manually is because I am working on a very complex company project and the Access wizards cannot emulate the requirements of this project. So this means creating the forms and controls manually and writing all the underlying code. ( This is my first project and first time using Access and VBA and AS400.

But you are also right in that I am learning a hell of a lot. My progress has been huge in the last few months. It is very slow developing though and due to the complexity and my lack of knowledge in the tools I use it is sometimes hard to get certain funtions to work like this seamably simple problem I am trying to solve now.

But hey, just keep on swimming!!! :-)

Howzit all

Right Fishval, I have tried that code you posted but I am still getting that annoying error I posted in #12.

I had a lookup on the possible causes for this error, but all seems fine in my database and tables. Hmmmmm......

Howzit all

Right Fishval, I have tried that code you posted but I am still getting that annoying error I posted in #12.

I had a lookup on the possible causes for this error, but all seems fine in my database and tables. Hmmmmm......

Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion errorand didn't add 1 record(s) to the table due to key violations,0 records due to lock violations, and 0 records due to validation rule violations.........

Are you sure that your append query gets values before you clear controls?

Could you clarify where the table is actually located. I mean whether it is in your mdb or in other RDBMS?

Hi there Fishval

Yes it does get the values as it writes one entry into each respective table. But It refuses to write a second entry to the table and gives that error then.

Say in table A at the moment not all fields are popullated as the rest will be populated at a later stage in the process.(I will then use a SQL UPDATE statement for this)

Well, the tables I am writing to are all based on an IBM AS400 server, which propable does not help but does work when creating simple lookup tables....

Hi there Fishval

Yes it does get the values as it writes one entry into each respective table. But It refuses to write a second entry to the table and gives that error then.

Say in table A at the moment not all fields are popullated as the rest will be populated at a later stage in the process.(I will then use a SQL UPDATE statement for this)

Well, the tables I am writing to are all based on an IBM AS400 server, which propable does not help but does work when creating simple lookup tables....

Ok.

After you have updated record you create new blank record. Are you sure all fields in a record created can be Null value.
If not, the simple way to get rid of this at this stage is to set (in your form design view) default values for the corresponding form controls (e.g 0 instead of Null for Number field, "" instead of Null for text field). The record will be added with corresponding fields containing controls default values.

By the way is it really the only way to save record - add a new one and then update? Why not to run append query with values at the end before controls are cleared?

And the last question.
As far as I've understand you can access your tables via SQL. Why not to design form with SELECT query in RecordSource property?

I think you should take a distant look to the whole strategy.

couldn't you just use the following:

me.requery

that simple code should work, right?

I don't know if this will help you, but I use unbound forms alot so I have a set of functions that I use to build my sql strings.

These functions have greatly simplified using unbound forms for me, the design requires that controls on the form have the same name as the table field that they will populate, I enter locked into the tag for a control if I want it bypassed. Since it doesn't include fields that don't contain data it may help you avoid the error you are getting it also makes for a more efficient transaction.

FishVal,shiznaw,Mseda

Thank you all for your responses . I will read through it all carefully and try all your suggestions and get back to you with the results.

Just a bit of info to add from my side :

Where I have combo boxes on the form to present users with a list to select from, I use SQL SELECT queries in the Row Source. I also have in place numerous IF statements that runs through my RADIO buttons and also CHECK BOXES . On the bottom of each tabbed form I also have a SAVE button and behind each save button I have written SQL INSERT INTO statements to insert all form data into respective tables. Where I have RADIO BUTTONS I have not set any default selection.

Will get back to you all as soon as I can.

Right

FishVal I have tried your suggestion and changed control default values. What happens now when I save the form entries is that new data is only being written to one of the five tables. BUT. The new entry ( even though I have put in completely differnet values compared to the last entry) is exactly tthe same data as the first entry. So it looks like the data in the variables for the INSERT INTO statment is not emptied after each save action. I had a search but cannot find anything on how to empty a variable of current data. But hey, at least I can write multiple entries to one of the tables now which did not happen before so at least I am going in the right direction here.

shiznaw, I have tried your suggestion as well, but no luck I am afriad. No error or anything, just nothing happens.

MSeda your code will take me a while to work through( got to make sense of it).

Right I might sound like a real cowboy now, but I descided to just play around with some settings and see what happens. I opened my linked tables in datasheet view , clicked on the small dropdown error which is next to each column name (i selected random fields from each table) and then on the dropdown menu , right on the bottom is a box and in it a selection of tick boxes. I then deselected SELECT ALL and BLANKS and hey presto all of a sudden all my missing records appeared in my tables!!!?????

I did this for all the tables, then deleted all records form the tables and started entering new records via me form again and hey it is working fine!!

The only problem I have now( and this is crutial ofr the project) is that on the bottom of my form it only says 1 of 1 records even when I save multiple. However, when I look at the tables from access, on the bottom it will say something like 1 of 5 records. Do you guys know how I can get my form to say how many records there are?

I have also found that if I bound a form to a table, I then get the record count displayed at the bottom of the window ie. 1 of 10. But if I then navigate through these records by using the Access record navigation buttons on the bottom of the window, all I get is a blank page, no records.

This is starting to drive me slightly insane. My eyes have gone all crossed over from searhing the web for info/solutions.

There must surely be someone out there who would know the solution to this?.....

I have also found that if I bound a form to a table, I then get the record count displayed at the bottom of the window ie. 1 of 10. But if I then navigate through these records by using the Access record navigation buttons on the bottom of the window, all I get is a blank page, no records.

This is starting to drive me slightly insane. My eyes have gone all crossed over from searhing the web for info/solutions.

There must surely be someone out there who would know the solution to this?.....

Hi!

This is definitely a progress. You have linked table to the form. Have you linked form controls to bound table corresponding fields as well?

Check it in form design view > control properties > ControlSource. It should be set to table field you want to populate from and write to.

Good luck.

PS. Be aware that combination of Access form automation together with your code is likely to add duplicate data to tables. In any case some kind of conflict is sure to occur. I suggest you to avoid code running temporaly.

Hi FishVal

Thanks for replying. Well unfortunately I cannot bind the form to a table or the controls either. It is too late in the project for that and it does mess up m forms a bit if I do that as I found out.

What I might try and do to get my record count to be displayed on the form is to create my own controls that looks the same as that in Access and then to write the code behind it myself by making use of recordsets. I will then manipulate the data within to get the required results by using movefirst,movelast,movenext and so on.

I hope this makes sense. Hey I 'm off on a new journey of discovery!! Yee haaaa...

Hi all

Right I am back to square one now. I have descided to Bind my tabbed form to a query. The query includes reference to all the table fields data would be written to.

After binding this form, the Access built in record navigation on the bottom of the form now shows x of x records and I can also use the Next,Previous,First and Last buttons to navigate through the records.

The problem and this is the the main thing, is that I am unable to edit any exisiting records or enter any new ones. I have tried Access's new record button, but doesn't work. I then created my own button with VBA behind it to clear all form controls when clicked, doesn't work either. The form properties is set to Allow Edits and Data Entry.

Does any Access God out there have a possible answer?

Ok I think it has something to do with the query the form is based on now.

I used Access 2007 wizard to create a new form based on the query and I get the exact same result as my original form .

I also get the same error on both forms : This recordset is not updateable.

I have done a test by changing the query to an append and then also an update query, but ooohhh not good .

Hi all

Right I am back to square one now. I have descided to Bind my tabbed form to a query. The query includes reference to all the table fields data would be written to.

After binding this form, the Access built in record navigation on the bottom of the form now shows x of x records and I can also use the Next,Previous,First and Last buttons to navigate through the records.

The problem and this is the the main thing, is that I am unable to edit any exisiting records or enter any new ones. I have tried Access's new record button, but doesn't work. I then created my own button with VBA behind it to clear all form controls when clicked, doesn't work either. The form properties is set to Allow Edits and Data Entry.

Does any Access God out there have a possible answer?

Hi!

Really, some queries are not appendable. There are at least 4 reasons for query to be not appendable:

• query contains join based on Many-to-Many relationship
• query contains aggregate function (Min, Max, Avrg etc)
• query contains subquery
• something I used to know but have forgotten now :)

I recommend you to build your query step-by-step checking whether it is still updatable with each table addition. In general it is useful to localize a problem.
By the way, I'll be able to give you more useful advices if you will provide the tables metadata.

Hi there FishVal

Thanks for getting back to me. Right first here are all the points you were mentioning of why it would not work:

The query is a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.).

The query is a SQL-specific query (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.).

The field you are trying to edit is a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.). In this case, you may be able to edit the other fields.

The query is based on three or more tables and there is a many-to-one-to-many relationship.


Also , a bit of a breakthrough. I have spoken to some more cleverer programmers than me and I have been informed that Referential Integrity has not been set up on the AS400 I have my links set up to. So we are going through some literature now to see how to do this . I will get beack if we do get the answer to this. Speak again very soon.....

Right, unfortunately I am going to have to back track now. I will need to go back to having an unbound form. I will now try using recordsets to accomplish what I need to do, but not sure how this works(recordsets).

This is due to the reason that the nessecary changes cannot be made on the AS400 server to make the bound form work .

Right, looks like more studying recordsets now....

I think it's fair to say that this thread has a number of good answers embedded within it for various scenarios related to clearing Form Controls.

However, for me the one big lesson is that when trying to develop code of any sort it's most critical to deal with errors a step at a time.

Here, the OP, for reasons of their inexperience at the time & no criticism due for that as we were all there once, is trying fixes for various things as and when an idea either occurs to them or is suggested. This is a recipe for disaster, as clearly illustrated by the painful progress this thread shows.

Separate all individual problems and always fix one before proceeding to the next. The chance of one muddying the waters for others is just too high. It's also very tiring when skipping from one item to another when things aren't working.

Keep your discipline and attack the problems in an orderly and logical way. That way the pain and confusion is kept to the absolute minimum.

In a way, our (Bytes.com) insistence on one specific technical problem per thread can help members to keep to this.