Input Form Field Names in VBA

[Starwood]

I plan to write some VBA code for a command button on an Input Form. Can
anyone help with answers to the following questions:

1) In VBA, how do you reference the values (field names) of the fields in
the previous Input Form that was displayed?

2) In VBA, how do you reference the values (field names) of the fields in
the Input Form that is currently displayed?

3) Are the field values from the previously displayed Input Form stored in a
named buffer? If so, what is the VBA buffer name?

4) Are the field values of the current Input Form stored in a named buffer?
If so, what is the VBA buffer name?

Thanx in advance,

George L.

 

[Albert D. Kallal]

I plan to write some VBA code for a command button on an Input Form. Can
anyone help with answers to the following questions:

1) In VBA, how do you reference the values (field names) of the fields in
the previous Input Form that was displayed?

are you saying that the current form was called (opened) by the previous
form?

Sure, just declare a variable in the current form (module level)

dim frmPrevous as form


Now, in your on-load event, or on-open event, go:


set frmPrevous = screen.ActiveForm

(activeform does not change until the on-open, and on-load is complete..so,
at this point in time, activeForm is the previous calling form).

2) In VBA, how do you reference the values (field names) of the fields in
the Input Form that is currently displayed?

simply go:

me.NameOfContorlOnform

or

me!FieldName (if the control is not on the form)

The above is for code *in* the current form. If you need to reference field
values in another open form, then you can go

forms!NameOfForm!NameOfField

3) Are the field values from the previously displayed Input Form stored in
a
named buffer? If so, what is the VBA buffer name?

Do you mean previous form, or pervious record? (that is a GRAND CANYON OF A
DIFFERENT ISSUE!!!).

Or, do you mean the current record, and can we reference "old" values, and
updated (edited/changed) values?

For previous form, as mentioned, just reference the form name, or setup a
variable as per my example.

if you talking about previous record...no, there is not such a thing...

If you talking about previous values for the current record we are editing,
then yes, you can go:

me.NameOfContorl.OldValue

4) Are the field values of the current Input Form stored in a named
buffer?
If so, what is the VBA buffer name?

There is no buffer, but all of the controls and values can be referenced,
and they not been committed to the table (or query) until the users moves to
another record, closes the form etc.. You can also force a disk write of the
current data (edited) values by going:

if me.Dirty = True then
' changes have been made...write to disk
me.dirty = false
end if

So, if any editing has occurred, the me.Dirty property will be true. And, if
any editing has occurred, the forms before update event will fire before the
disk write. (this means you rarely have to use me.dirty to check if things
been changed, since any code in the before update event will not run if no
changes to the form been made.).

Normally, you don't need the above code to force a disk write, since moving
to a new record, closing the form etc will write out the data for you.

 

[Albert D. Kallal]

Sure, just declare a variable in the current form (module level)

dim frmPrevous as form


Now, in your on-load event, or on-open event, go:


set frmPrevous = screen.ActiveForm

when you do the above, then in code you can go:

frmPrevous!LastName

 

[John W. Vinson]

I plan to write some VBA code for a command button on an Input Form. Can
anyone help with answers to the following questions:

1) In VBA, how do you reference the values (field names) of the fields in
the previous Input Form that was displayed?

You can't, unless that form is open.

2) In VBA, how do you reference the values (field names) of the fields in
the Input Form that is currently displayed?

Forms don't contain fields - tables do. Forms contain Controls, which may or
may not be bound to a field. Not all fields in a table are necessarily bound
to controls on a form. To reference a Control on a form use

[Forms]![NameOfTheForm]![NameOfTheControl]

or, if the code is on the currently active form,

Me![NameOfTheControl]

3) Are the field values from the previously displayed Input Form stored in a
named buffer? If so, what is the VBA buffer name?
No.

4) Are the field values of the current Input Form stored in a named buffer?
If so, what is the VBA buffer name?

No.

You may be applying the logic of some different program to Access. A Form is
just a tool to display and edit data stored in a Table, often via a query. The
data is not stored in a Form, nor in a buffer; it's stored in the Table which
is designated in the form's Recordsource property.

John W. Vinson [MVP]

 

[Starwood]

Thanks for your answers. Yes, I am used to programming in conventional
programming languages and have done so since 1962 (dozens of other
languages).

At any rate. What I want to do should be very simple no matter whether you
use the term "controls" or "field values". The sequence goes something like
this:

1) Using an Input Form, display a current record in the database.
2) Use a command button (Save_Values) on the Input Form to somehow and
somewhere, save the values in this database record's fields.
3) Open a blank Input Form to enter a new ( but similar) record.

4) Use a command button (Duplicate_Values) on the Input Form to retrieve the
values saved in step 2 (or step 7) and make them the field values of the
current record in the Input Form.
5) Manually modify the fields that require it, including (of course) the key
field.
6) Use the Save_Values command button to save the new set of field values.
7) Add the new record to the database.
6) Loop back to step 4).

What I'm trying to accomplish is what we called in the "old days" batch data
entry of a number of similar records without having to re-key numerous
amounts of duplicate data.

If there is a standard way to do this in Access, I've never found out how.
Therefore it seems like VBA code and two command buttons should be one way
to accomplish this.

Thanx again.

George L.

 

[John W. Vinson]

Thanks for your answers. Yes, I am used to programming in conventional
programming languages and have done so since 1962 (dozens of other
languages).

Well... I remember having to "unlearn" a lot of the procedural thinking that I
was used to when I made the transition from programming languages to
databases. It really is a different mindset.

At any rate. What I want to do should be very simple no matter whether you
use the term "controls" or "field values".

I'm using the terms because *these are different things, with different
properties, with different uses*. A Field in a table is stored data; a Control
on a form is a tool for interacting with that stored data. They are NOT
interchangable!

The sequence goes something like
this:

1) Using an Input Form, display a current record in the database.
2) Use a command button (Save_Values) on the Input Form to somehow and
somewhere, save the values in this database record's fields.
3) Open a blank Input Form to enter a new ( but similar) record.
4) Use a command button (Duplicate_Values) on the Input Form to retrieve the
values saved in step 2 (or step 7) and make them the field values of the
current record in the Input Form.
5) Manually modify the fields that require it, including (of course) the key
field.
6) Use the Save_Values command button to save the new set of field values.
7) Add the new record to the database.
6) Loop back to step 4).
What I'm trying to accomplish is what we called in the "old days" batch data
entry of a number of similar records without having to re-key numerous
amounts of duplicate data.

If there is a standard way to do this in Access, I've never found out how.

There is, though it's not builtin. Ordinarily - with properly normalized
tables - this will not be needed all that often, and only for a few fields;
having many repeating values in a table suggests that you may need to have two
tables in a one to many relationship.

It is, of course, sometimes needed and it's not hard to do. On each Form
Control that you want to have auto-duplicated, put the following code in its
AfterUpdate event:

Private Sub controlname_AfterUpdate()
Me.controlname.DefaultValue = Chr(34) & Me.controlname & Chr(34)
End Sub


If the current value in the form is (say) 813, this will put

"813"

(with the quotemarks, Chr(34)) into the control's Default Value property.
Regardless of the field datatype, the Default Value needs to be a string.

Sorry I didn't clearly understand what your first post was trying to
communicate!

John W. Vinson [MVP]

 

[Albert D. Kallal]

In addition to the answer of setting the field default value as John
suggested, in Excel, or ms-access, you can also hit ctrl-' and it will copy
the previous value into the current field....

Interesting, I not aware that we have program access to this previous
value.....

And, you could "code" a solution that saves the list of current values into
a array, or collection. The code would be placed in the after update event
of the form. And, then for the new record, you could use the before insert
event, and have that code fill in the values.

However, since you need to control, define, and layout which controls on the
form are to be copied (and of course skip the primary key), then John's
suggestion is going to be much less code, and gives you the "choice" of
which controls on he form you want to copy...

 

[John W. Vinson]

Interesting, I not aware that we have program access to this previous
value.....

Me.Controlname.PreviousValue - though that isn't really quite the same thing
(it's the value that was in the control at the form's Current event).

John W. Vinson [MVP]