Stop tab from starting new record in form

[Dave1963]

G'day Again Gurus,

So far the advice I have received on these forums has worked a treat. I have created some control buttons using the built in functions of Access2007 and then using the script supplied here modified the control.
So far the controls can add new record, save record, exit form, open next form and close existing form, open previous form and close existing form.

My next question is when the user reaches the end of the form fields how do I stop the form from automatically starting a new record?
For example, I use the TAB key to move from field to field and when I tab out of the last field the form goes straight to the next empty record.
I want this to be a deliberate action performed by clicking the Add New Record button and not an accident of a key press.

The other thing is, how do I get the form to open with blank/empty fields and not at the first populated record?
Some of the data is updated frequently so the records need to be able to be edited but I need to prevent accidental editing of the existing records.

I am sure most of this is available in reference texts and I'll find it eventually but your help also puts my questions into context.

Thanks in advance.

Dave

 

[bob fitz]

1) Change the forms Cycle property to Current Record.

2) Put the following line of code in the forms On Open event:
DoCmd.GoToRecord , , acNewRec

 

[CJ_London]

To follow on from Bob's suggestion,

how do I get the form to open with blank/empty fields

Set the form's dataentry property to True or set the forms allow edits to False if you want to see the previous records - you can have a button called 'Edit Records' which can change this property to true if your user needs to edit it

 

[Dave1963]

You blokes are great. Bob's suggestion worked perfectly.

CJ I tried to use the button wizard to "find" a record but it brings up the standard MS find dialog you get with ctrl+f.
Can I create an edit record button that only looks in the ECPNo field for the record I want to enter?
I tried the Go To Record command but messed it up and need to start again.

 

[Pat Hartman]

I like to use the same form for editing data as I do for adding it so I don't use either Bob or CJ's method. I use the options of the OpenForm method to specify whether I want the form opened for editing or adding.

 

[Dave1963]

Pat, that is what I am trying to do. How do you select which record to edit?

 

[CJ_London]

You haven't described your form so I'm assuming you have a continous form with at least the header and detail showing. I'm also assuming your form properties are set to dataentry=false and allowedits =true....

On your form header put an unbound text box, we'll call it txtFind.

In the on change event of txtFind put the following code:

me.filter="[ECPNo] Like '" & txtFind.Text & "*'"
me.filteron=true

You may want something more sophisticated than this but see how you get on.

What this does is every time you enter a character in txtFind the form will limit the records available (a bit like the way a combo box works). So enter a 1 and the form will display all records wher ECPNo starts with 1, then enter a 2 and the records will be limited to those starting with 12 etc.

 

[Dave1963]

Thanks CJ.

I must be particularly dense today because it didn't work.
My form is set up almost as you assumed.
Default View = Single form
Recordset = Dynaset
Dataentry = False
Edits = true
Cycle = current record (from Bob's suggestions on how to stop the tab key from moving on to a new record at the end of the form.)

I have 3 records in my table so that I can see what is happening each time I build a piece of the db. The code should have found my record but it did nothing.

I am working with Access 2007 as that is the version we have at work.
I have two reference texts available to look up how thing s work and the principles behind the actions. These are Alison Balter's Mastering MS Office Access 2003 and Access 2007 VBA for Dummies.

 

[CJ_London]

I'll come back to you when I've worked out what 'didn't work' means

 

[Dave1963]

I am dense aren't I.

I changed the name of the unbound box to edit so of course it won't work as written.
I changed the code to edit.Text but it still doesn't work.

When I type the characters into the text box nothing happens in the form. I typed the full number (5 characters) into the box but no record appeared for editing. Once I had the full number in the text box I pressed enter but no record appeared for editing.

As I understand the purpose of the event Change (from your description and from my reference texts) as I enter characters into the text box the records should appear and filter towards the desired record.

 

[CJ_London]

try changing the form view to continuous.

I'm concerned you say this

no record appeared for editing

this should only happen if dataentry=true - the very least you should be seeing is a record - unless what you have typed in the 'Edit' text box does not exist

Without knowing your form design, it may be necessary to do it differently

 

[Dave1963]

I didn't know how to load just the form so here is the db I am trying to develop.
At the moment I am only focussing on the data entry and data editing part of the program.
There is one main table with three data entry forms. The forms are based upon who is required to enter data and at what stage of the program they enter the data.
The other parts are in preparation for when I start work on the reporting, scheduling, and charting of the data.

If you can assist me with getting the edit record working it will help me out enormously.

I have an idea of how I want the final presentation of the db to look but I will leave the pretty bits until after the db does what it is intended to do.

 

[CJ_London]

You said you changed the name of your text box, but you also needed to change the filter to NHIECPNo. However having now seen your form, I don't think it is the right way for you - my suggestion works better on a datasheet or continuous form of the multiple items style.

I suggest change your edit control to a combo box - which is closest to what your require the only thing you need to do is populate the recordsource of the edit combobox with

SELECT NHIECPNo FROM tblNHIECPData ORDER BY NHIECPNo

and in the after update event put

Me.Filter = "[NHIECPNo]= '" & Edit & "'"
Me.FilterOn = True

And don't forget to put the form back to single

 

[Dave1963]

When I tried this, the form still did not move to the requested record. However, I changed the combo box from unbound to bound by setting the control source to the ECP Number field and now it works as advertised.

My thoughts are that as an unbound box it would display the available records and filter to the number that I wanted but does not control the form so the form would not go to that record. As a bound box, it did control the form based on the field that I had set as the control.

Am I correct in my understanding of how this functions?

 

[CJ_London]

Am I correct in my understanding of how this functions?

No

I changed the combo box from unbound to bound by setting the control source to the ECP Number field and now it works as advertised.

I'm surprised - if you have bound it to a field then all you are doing is changing the field.

You did put the code in the after update event of the Edit control?

 

[Dave1963]

G'day CJ,

I did put the code into the After Update Event and I returned the Combo Box to an Unbound Control.
The ComboBox quite happily cycles through the available record numbers just the way you said it would but the form itself does not go to the selected record even if I press enter or click on the number once I have the one I want displayed in the ComboBox.
This is taking up a bit of your time and mine so until I have learnt more about how the text boxes and combo boxes work with forms I will use the built in find command in the wizard.
Thanks for your patience and your help so far. I'll revisit this issue when I am a little wiser.

Dave

 

[CJ_London]

I'm assuming your combo box is called Edit, if not, replace Edit with the name of your combobox

and in the after update event put


Code:
Me.Filter = "[NHIECPNo]= '" & Edit & "'"
Me.FilterOn = True
And don't forget to put the form back to single

 

[Dave1963]

Riddle me this Batman.

I have spent most of the day pouring over my reference texts and through old threads on the forum because whenever I type a value into the combo box the form does not go to the record that I am looking for.
Then as I am about to give up, I notice, at the bottom of the form, in the border is the counter that states record No of Total Records. Next to that is an icon and the word filtered beside it. If I click on the icon the form changes from filtered to unfiltered and back. Next to this is the word search and a small text box. If I type my record number in this box the form goes to the record that I want.

These boxes are so small that I overlooked them until just now. :banghead:
What? Why? How did they apear there and why are they doing what I have spent a couple of days trying to achieve through a succession of text boxes and Combo Boxes?

If I scran the Combo Box will this go away?

 

[CJ_London]

it appears because your form navigation buttons is set to yes.

You can also filter by right clicking on a field (or highlighting a part of a field before right clicking) and filtering from there

Filtering uses combo boxes et al is still relevant in many cases.

Filtering works best when you are filtering a list - i.e. form is continuous or datasheet.

Datasheets have their benefits because you can easily resize, hide , move and/or freeze columns

 

[Dave1963]

Aha. Thanks once again.