inconsistent behavior on opening form

[Garry]

I have a simple form based on a table of products. The form is opened by a
macro in Add mode. Input data on the form is Item_No, Description, & Retail
Price. There are 2 command buttons on the form – “SAVE†& “DONEâ€. cmdSave
runs a macro to move to the next record, cmdDone, closes the form and Save is
set to YES. ItemNo, Description, RetailPrice & cmsSave have the tab stop
property set to YES. cmdDone has the tabstop property set to NO. The form’s
cycle property is set to Current Record. The form is used to enter multiple
new items in the products table. Everything works, MOST OF THE TIME - &
that’s what is troublesome. Normally when I run the macro, the form opens
with the cursor “blinking†in the ItemNo field – as it should. If I have a
lot of items to enter, I usually enter them 10 or so at a time, then click
“DONE†& reopen the form to add more – like saving a word document on
occasion so you don’t loose everything if the computer decides to “belly-upâ€.
Normally all works fine, but once in awhile when I reopen the form, focus is
on the cmdDone button??? Even though the tab stop property is set to NO. It
will do this repeatedly, until I “Compact & Repair the Database†– then all
works fine again for awhile. Inconsistencies in software bother me & I want
to release this application for use by my wife who is very intimidated by
computers. Is there something I should be doing to correct this, or is there
a way in VBA to execute the “Compact & Repair Database†function??

 

[Eric D via AccessMonster.com]

Tools/Options - Click on the General Tab - Put a check mark in the box
labelled "Compact on Close".

As I understand your issue, here is what I would do to resolve the problem.
The FIRST thing - Change Save to NO. You're saving the form, not the data,
when you set this option to yes. When you click on Done, that button has the
focus and YOU are telling it to save the Form in that state.

Each time you fill the form, then tab to the first field, you should see all
fields clear, allowing you to enter new data. Once you move off of that
record (data you just entered), you are posting the record to the database.
There should be no need to click on Done every 10 records or so.

Without stepping through your code, I cannot begin to guess why this happens
"sometimes".

 

[Garry]

I changed Save to NO in the close macro. Now when I click DONE then reopen
the form the DONE button has focus as before. However, if I close the form &
then reopen it again, it opens correctly with the cursor in the ItemNo field
(before I changed Save to NO, the form would open with focus on the DONE
button no matter how many times I reopened it until I did a "Compact & Repair
Database"). I am fairly new to Access & started using macros because it was
easy, but after becomming somewhat familiar with VBA I'm wondering if I
should totally forget macros. By the way I am using Access 2000 & DAO.

 

[David]

Garry,
It's a matter of personal preference, but VBA is more robust. You can
convert macro's to VBA to see how VBA works for your specific tasks.

Here is a link that uses FLASH to cover the basic concepts of how to utilize
VBA in your application.

http://www.datapigtechnologies.com/

 

[tina]

comments inline.

I have a simple form based on a table of products. The form is opened by a
macro in Add mode. Input data on the form is Item_No, Description, & Retail
Price. There are 2 command buttons on the form - "SAVE" & "DONE". cmdSave
runs a macro to move to the next record, cmdDone, closes the form and Save is
set to YES.

the Save argument of a Close action applies to the object being closed - not
to the data that the object displays. so you're saving the settings of the
form, not the current record in the form.

btw, if you change the Cycle property to AllRecords, then your wife can just
Tab or Enter in the last control (tables have fields, forms have controls)
in the TabOrder, and automatically move to the next record. if you prefer to
leave the Cycle as CurrentRecord, then the button to move to the next record
is useful - but i wouldn't call it "Save". to a user who's not familiar with
Access, having a Save button implies that if you don't click Save, the
record will *not* be saved. so your wife might enter a record, then decide
she doesn't want it, and just click Done, thinking that the record will
"disappear", when instead it will be saved automatically.

ItemNo, Description, RetailPrice & cmsSave have the tab stop
property set to YES. cmdDone has the tabstop property set to NO. The form's
cycle property is set to Current Record. The form is used to enter multiple
new items in the products table. Everything works, MOST OF THE TIME - &
that's what is troublesome. Normally when I run the macro, the form opens
with the cursor "blinking" in the ItemNo field - as it should.

all other considerations aside, you can force the cursor to a particular
control consistently by adding a macro GoToControl action or VBA code
SetFocus action to the form's Load event, to move the cursor to the desired
control every time the form opens.

If I have a
lot of items to enter, I usually enter them 10 or so at a time, then click
"DONE" & reopen the form to add more - like saving a word document on
occasion so you don't loose everything if the computer decides to

"belly-up".

absolutely unnecessary in Access; clicking DONE only saves the current
record - when the form closes - the previously entered records have already
saved to disk. when using a form, the data you enter is immediately saved to
the underlying table when you a) manually save the record by using a
SaveRecord command, or b) move backward or forward to another record in the
form, or c) move from a main form into a subform, within in the same
mainform record, or d) close the form.

Normally all works fine, but once in awhile when I reopen the form, focus is
on the cmdDone button??? Even though the tab stop property is set to NO.
It
will do this repeatedly, until I "Compact & Repair the Database" - then all
works fine again for awhile.

since you're using Access 2000, recommend you turn OFF the Name AutoCorrect
option, if you haven't already. it causes some weird errors, and is
generally a pain in the neck; many experienced developers turn it off
immediately in every database they create. in the database window, on the
menu bar, click Tools | Options | General tab and uncheck the box next to
"Track name AutoCorrect info". then compact/repair the db.

Inconsistencies in software bother me & I want
to release this application for use by my wife who is very intimidated by
computers. Is there something I should be doing to correct this, or is there
a way in VBA to execute the "Compact & Repair Database" function??

on the same General tab (see above) you can checkmark the Compact On Close
option. also recommend that you make a back up copy of the database
periodically.

hth