a form as front-end to a table of execution parameters

[Allen_N]

I often want to save user-input parameters between runs, but Access seems to
make this much more difficult than it should be, so I wonder if I'm doing it
wrong.

I can't get the right behaviour from the form, which contains 2 Text Boxes
and has a 2-column, 1-row table as its Record Source. The text boxes have a
date-based input mask, and the table contains the corresponding fields as
type Date/Time. Currently, I have set the form properties as:

Allow Filters = true
Allow Edits = true
Allow Deletions = true
Allow Additions = true
Data Entry = false

Here are some of the annoying things it does.

(1) If I change the date in the 2nd text box and press Enter, both text
boxes go blank. When I open the table, however, the last data that I managed
to get to 'take' are still there.

(2) If I change the date in the 1st text box and press Tab, then open the
table, I find that neither field has changed.

(3) If I change the date in the 2nd text box and press Tab, both text boxes
go blank, and I find that neither field has changed.

In the past, I've got around this kind of nonsense by building layers of
code between the form and the table, but it shouldn't have to be that way. Is
there a web page or some basic rules that explain how to set this up properly?

Thanks!

 

[tina]

that sounds really strange to me, Allen; i'd think it would be an extremely
simple setup...

i can't begin to guess what questions to ask to unearth the problem. if
you'd like me to take a look at the db (if it's written in a version between
A97 and A2003, that is), post back and i'll give you directions on how to
email it to me. (and no, there's no issue of money here; i willingly abide
by and support the spirit and the letter of these newsgroups - FREE
peer-to-peer support.)

hth

 

[Allen_N]

Thanks Tina.

I've cut a copy of the db down to just the relevant objects, if you'd like
to tell me where to send it.

Since posting, I discovered the Cycle property, which I now use to prevent
the text boxes going blank. However, there is still the issue of the table
not being refreshed when the text boxes are changed.

Cheers!

-- Allen

 

[Marshall Barton]

I often want to save user-input parameters between runs, but Access seems to
make this much more difficult than it should be, so I wonder if I'm doing it
wrong.

I can't get the right behaviour from the form, which contains 2 Text Boxes
and has a 2-column, 1-row table as its Record Source. The text boxes have a
date-based input mask, and the table contains the corresponding fields as
type Date/Time. Currently, I have set the form properties as:

Allow Filters = true
Allow Edits = true
Allow Deletions = true
Allow Additions = true
Data Entry = false

Here are some of the annoying things it does.

(1) If I change the date in the 2nd text box and press Enter, both text
boxes go blank. When I open the table, however, the last data that I managed
to get to 'take' are still there.

(2) If I change the date in the 1st text box and press Tab, then open the
table, I find that neither field has changed.

(3) If I change the date in the 2nd text box and press Tab, both text boxes
go blank, and I find that neither field has changed.

In the past, I've got around this kind of nonsense by building layers of
code between the form and the table, but it shouldn't have to be that way. Is
there a web page or some basic rules that explain how to set this up properly?

Tina is probably right about it being strange, but if you
only have a one row table, then don't you want to set
AllowAdditions to No (False)

If AllowAdditions is set to True (Yes) then the Tab/Enter
keys from the second text box should be saving the record
and then taking you to a new (blank) record so you can add
more values in an additional record.

 

[Allen_N]

Thanks Marshall.

Thats seems right, but I had previously tried many combinations of settings
and decided to stay as close to default as possible until I could find out
how they interact. Some settings caused the text boxes to disappear when I
switched out of Design View.

I seem to have got past the disappearing data problem (by setting Cycle to
Current Record, but the table still doesn't update when I leave a text box.

 

[tina]

okay. make sure you read Marsh's post - that may be enough to clear up the
issue you're having. if not, i'm still willing to see if i can help out. pls
do the following:

1) compact the "cut down" database, and zip if necessary to get the db under
1 MB if possible (i have dial up, so downloading/uploading is slow, slow,
slow).
2) refer to this newsgroup in the email Subject line, so i don't delete it
as spam.
3) refer to this thread's Subject line in email body, pls, so i can find it
for reference.
4) go to my website at the following link and follow the directions to
"decode" my email address. IN ADDITION to those directions, change the
number 2 in the address to a number 1.
http://home.att.net/~california.db/tips.html#aTip11

5) Marsh's point was well-taken. pls tell me in the email if you are trying
to save only the most recently entered parameters "between runs", or
multiple sets of parameters.
6) and last but not least, tell me in the email what version of Access the
db is written in, pls.

it's Monday evening 8/27 my time; if i get your email on Tuesday, i'll try
to dig into it on Tuesday evening after work.

hth

 

[Dirk Goldgar]

In

Thanks Marshall.

Thats seems right, but I had previously tried many combinations of
settings and decided to stay as close to default as possible until I
could find out how they interact. Some settings caused the text boxes
to disappear when I switched out of Design View.

I seem to have got past the disappearing data problem (by setting
Cycle to Current Record, but the table still doesn't update when I
leave a text box.

The table will not normally be updated until one of several things
happens:

(a) You go to a new record on the form ... but you've set Cycle to
"Current Record", so that's not going to happen automatically,

or

(b) You close the form, forcing Access to save the "dirty" record,

or

(c) You explicitly tell Access to save the record, by pressing
Shift+Enter or choosing menu item Records -> Save Record or Records ->
Refresh,

or

(d) You execute one of several possible statements in code (or execute a
macro action) that forces Access to save the record.

If you're doing one of these things, and yet the modified record isn't
being saved, then there's something wrong. If you're not doing any of
these things, then from your description, Access is behaving as
designed; in which case the only question is, do you need to change
this behavior? If so, there are ways to do it. You can force Access to
save the record as soon as you modify any of the fields on the form, if
that's what you want. It'll take a little code, but it's no big deal.
However, people usually want to delay saving a record until they've made
all the changes to it that they intend to make, and then indicate in
some fashion that they're satisfied with them.

 

[John Spencer]

It sounds to me as if you are moving to the next record. Have you set
the form's Cycle property to this record only. Also, you may (or may
not) want to set the Allow Additions property to no to prevent cycling
to the NEW record.

1) The result I would expect if you were cycling to a new record after
the tab.

2) The result I would expect since you did not cycle to a new record by
moving from control 1 to control 2 - you would still be on the same
record and no save action has yet occurred.

3) This one I don't have an explanation for.

If you were doing this with a local table you could have one record in
the table and just modify it as needed.

If you are using a linked central table (split database) then you might
want to have one record per user to keep the user parameters and tie the
relevant record to a user id.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

If the table is only supposed to contain one record, you
really want to set AllowAdditions to No. With this setting,
the form will display nothing **only** when the table does
not have the one record.

Be sure to read Dirk's post about saving the values.

 

[Allen_N]

Thanks, Marshall.

Following your and Dirk's advice, I've made the requisite settings (Allow
Edits = Yes; Allow Deletions = No; Allow Additions = No; Data Entry = No) and
put "Me.Refresh" in two event routines: Form_Deactivate, and that of the
command button which is the form's raison d'être. This updates the table as
required, and seems sufficiently bulletproof to unleash on the users. (And,
as I'd hoped, it's simpler than writing a routine to read/write an INI file
to store execution parameters.)

Thanks again, all!

If the table is only supposed to contain one record, you
really want to set AllowAdditions to No. With this setting,
the form will display nothing **only** when the table does
not have the one record.

Be sure to read Dirk's post about saving the values.
--
Marsh
MVP [MS Access]

Thats seems right, but I had previously tried many combinations of settings
and decided to stay as close to default as possible until I could find out
how they interact. Some settings caused the text boxes to disappear when I
switched out of Design View.

I seem to have got past the disappearing data problem (by setting Cycle to
Current Record, but the table still doesn't update when I leave a text box.

 

[Allen_N]

Oh, and I had also set Cycle = Current Record.

Thanks, Marshall.

Following your and Dirk's advice, I've made the requisite settings (Allow
Edits = Yes; Allow Deletions = No; Allow Additions = No; Data Entry = No) and
put "Me.Refresh" in two event routines: Form_Deactivate, and that of the
command button which is the form's raison d'être. This updates the table as
required, and seems sufficiently bulletproof to unleash on the users. (And,
as I'd hoped, it's simpler than writing a routine to read/write an INI file
to store execution parameters.)

Thanks again, all!

If the table is only supposed to contain one record, you
really want to set AllowAdditions to No. With this setting,
the form will display nothing **only** when the table does
not have the one record.

Be sure to read Dirk's post about saving the values.
--
Marsh
MVP [MS Access]

Thats seems right, but I had previously tried many combinations of settings
and decided to stay as close to default as possible until I could find out
how they interact. Some settings caused the text boxes to disappear when I
switched out of Design View.

I seem to have got past the disappearing data problem (by setting Cycle to
Current Record, but the table still doesn't update when I leave a text box.

:

Allen_N wrote:

I often want to save user-input parameters between runs, but Access seems to
make this much more difficult than it should be, so I wonder if I'm doing it
wrong.

I can't get the right behaviour from the form, which contains 2 Text Boxes
and has a 2-column, 1-row table as its Record Source. The text boxes have a
date-based input mask, and the table contains the corresponding fields as
type Date/Time. Currently, I have set the form properties as:

Allow Filters = true
Allow Edits = true
Allow Deletions = true
Allow Additions = true
Data Entry = false

Here are some of the annoying things it does.

(1) If I change the date in the 2nd text box and press Enter, both text
boxes go blank. When I open the table, however, the last data that I managed
to get to 'take' are still there.

(2) If I change the date in the 1st text box and press Tab, then open the
table, I find that neither field has changed.

(3) If I change the date in the 2nd text box and press Tab, both text boxes
go blank, and I find that neither field has changed.

In the past, I've got around this kind of nonsense by building layers of
code between the form and the table, but it shouldn't have to be that way. Is
there a web page or some basic rules that explain how to set this up properly?


Tina is probably right about it being strange, but if you
only have a one row table, then don't you want to set
AllowAdditions to No (False)

If AllowAdditions is set to True (Yes) then the Tab/Enter
keys from the second text box should be saving the record
and then taking you to a new (blank) record so you can add
more values in an additional record.