Store value for later use

[Guest]

I have been working on a form which I ended up having to base the fields off
of a query. To make a long story short the top part is the main form and is
bound to a simply query that displays fields in two tables. Below is several
subforms that are bound to a response table. The top part is currently
designed so that I select a person and the other fields at the top populate.
The bottom subforms are where they select answers to questions and the
results are all stored in a table called response. This all is working except
one thing. Each of the set of questions is in its own subform. I know I could
have designed this different but based on other feedback I received and
trouble I ran into getting this created this seemed to be the best solution
for the way I wanted data entry to work and also it seems to work so I left
it so each question is built in its own subform. In these sets of subforms
maybe the first 10 are based upon one paper form the user has, the next set
of 15 are based on another paper form, and so on.

To make this long story short I need to be able to have the user type a date
somewhere on the form and that date be stored to some temporay date table or
just stored somewhere so that when each question is answered in the subform,
it will use whatever date is there and post it to the response table. I have
no clue on how to do this. I first tried to create an unbound text box and
format it as a short date but I have no way to know how I can tell the
subforms to link back to whatever is in that field and post it to the date
field in the response table for that set of questions. I then thought if I
could find a way to make it so I type a date and after focus have it store
that date to some table, I may then be able to find a way to make them use
the date in the table and post it to the response date field. I am just
looking for an easy way to do this without getting into complicated coding. I
know there may be a more efficient way of doing all of this but I have my
entire project completed except implementing the date. Any help is
appreciated.

Thanks,
Dale

 

[tina]

an unbound textbox control on the main form should work okay. in each
subform's BeforeUpdate event, add code to write the date from that control
into the current record. assuming that you only want the date added to *new*
records (not changed on *existing* records that are edited), try the
following, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!DateField = Me.Parent!UnboundTextbox
End If

End Sub

change the DateField and UnboundTextbox to the correct names, of course. and
make sure that the response table's date field in included in the subform's
RecordSource. (if you don't know how to add a VBA event procedure to a form,
see "Create a VBA event procedure" at
http://home.att.net/~california.db/instructions.html for illustrated
instructions.

hth

 

[Guest]

I must have done something wrong because this is not working for me. What I
did is create a basic unbound textbox. I went to BeforeUpdate and selected
the ... to bring up the VBA program. I then pasted the text you gave and made
a change so that it is like this:

Private Sub Text64_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me![Response]![Date] = Me.Parent!Text64
End If

End Sub

Text64 is the basic title of the unbound box
[Response]![Date] is the table and field that I want the date to be placed in.

I then go to my subform which is bound to the Response table and I happened
to have an unbound text box there so I placed =[Response]![Date] for the
control source of it.

This does not seem to work as nothing happens. The field remains blank when
the data is posted. where did I go wrong?

 

[tina]

as i said in my previous post, add the code to *each subform's BeforeUpdate
event*, NOT to the main form textbox control's event. change the code back
to the way i gave it to you, *removing* the table reference; you can't set a
reference to a table that way in code, and you don't need to reference the
table at all in this code, since the subform is already bound to the table.

as for setting the ControlSource of an unbound textbox on the subform, you
can do that - though i see no need, since the date already shows on the main
form. note that setting the ControlSource of a control to an expression
makes it a "display only" control, commonly called a calculated control; in
other words, it is still unbound, has no effect on data in the underlying
table. at any rate, the correct syntax would be

=[Forms]![NameOfMainForm]![NameOfMainFormTextboxControl]

btw, if you really have a field in your table called "Date", suggest you
either change it (can be difficult when all the queries, forms, reports, etc
have already been built) - or at least make sure you never use that as a
fieldname in future databases. see
http://home.att.net/~california.db/tips.html#aTip5 for more information.

hth

I must have done something wrong because this is not working for me. What I
did is create a basic unbound textbox. I went to BeforeUpdate and selected
the ... to bring up the VBA program. I then pasted the text you gave and made
a change so that it is like this:

Private Sub Text64_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me![Response]![Date] = Me.Parent!Text64
End If

End Sub

Text64 is the basic title of the unbound box
[Response]![Date] is the table and field that I want the date to be placed in.

I then go to my subform which is bound to the Response table and I happened
to have an unbound text box there so I placed =[Response]![Date] for the
control source of it.

This does not seem to work as nothing happens. The field remains blank when
the data is posted. where did I go wrong?

an unbound textbox control on the main form should work okay. in each
subform's BeforeUpdate event, add code to write the date from that control
into the current record. assuming that you only want the date added to *new*
records (not changed on *existing* records that are edited), try the
following, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!DateField = Me.Parent!UnboundTextbox
End If

End Sub

change the DateField and UnboundTextbox to the correct names, of course. and
make sure that the response table's date field in included in the subform's
RecordSource. (if you don't know how to add a VBA event procedure to a form,
see "Create a VBA event procedure" at
http://home.att.net/~california.db/instructions.html for illustrated
instructions.

hth


fields
off and
is I
could next
set a
date table
or I
have

 

[Guest]

Thanks Tina,

I got it to work once I placed it in the subform. I ended up having to use
it in On Dirty because of how the layout was in order for it to populate when
I wanted it to but is working great. Many thanks.

Dale

as i said in my previous post, add the code to *each subform's BeforeUpdate
event*, NOT to the main form textbox control's event. change the code back
to the way i gave it to you, *removing* the table reference; you can't set a
reference to a table that way in code, and you don't need to reference the
table at all in this code, since the subform is already bound to the table.

as for setting the ControlSource of an unbound textbox on the subform, you
can do that - though i see no need, since the date already shows on the main
form. note that setting the ControlSource of a control to an expression
makes it a "display only" control, commonly called a calculated control; in
other words, it is still unbound, has no effect on data in the underlying
table. at any rate, the correct syntax would be

=[Forms]![NameOfMainForm]![NameOfMainFormTextboxControl]

btw, if you really have a field in your table called "Date", suggest you
either change it (can be difficult when all the queries, forms, reports, etc
have already been built) - or at least make sure you never use that as a
fieldname in future databases. see
http://home.att.net/~california.db/tips.html#aTip5 for more information.

hth

I must have done something wrong because this is not working for me. What I
did is create a basic unbound textbox. I went to BeforeUpdate and selected
the ... to bring up the VBA program. I then pasted the text you gave and made
a change so that it is like this:

Private Sub Text64_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me![Response]![Date] = Me.Parent!Text64
End If

End Sub

Text64 is the basic title of the unbound box
[Response]![Date] is the table and field that I want the date to be placed in.

I then go to my subform which is bound to the Response table and I happened
to have an unbound text box there so I placed =[Response]![Date] for the
control source of it.

This does not seem to work as nothing happens. The field remains blank when
the data is posted. where did I go wrong?

an unbound textbox control on the main form should work okay. in each
subform's BeforeUpdate event, add code to write the date from that control
into the current record. assuming that you only want the date added to *new*
records (not changed on *existing* records that are edited), try the
following, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!DateField = Me.Parent!UnboundTextbox
End If

End Sub

change the DateField and UnboundTextbox to the correct names, of course. and
make sure that the response table's date field in included in the subform's
RecordSource. (if you don't know how to add a VBA event procedure to a form,
see "Create a VBA event procedure" at
http://home.att.net/~california.db/instructions.html for illustrated
instructions.

hth


I have been working on a form which I ended up having to base the fields
off
of a query. To make a long story short the top part is the main form and
is
bound to a simply query that displays fields in two tables. Below is
several
subforms that are bound to a response table. The top part is currently
designed so that I select a person and the other fields at the top
populate.
The bottom subforms are where they select answers to questions and the
results are all stored in a table called response. This all is working
except
one thing. Each of the set of questions is in its own subform. I know I
could
have designed this different but based on other feedback I received and
trouble I ran into getting this created this seemed to be the best
solution
for the way I wanted data entry to work and also it seems to work so I
left
it so each question is built in its own subform. In these sets of subforms
maybe the first 10 are based upon one paper form the user has, the next
set
of 15 are based on another paper form, and so on.

To make this long story short I need to be able to have the user type a
date
somewhere on the form and that date be stored to some temporay date table
or
just stored somewhere so that when each question is answered in the
subform,
it will use whatever date is there and post it to the response table. I
have
no clue on how to do this. I first tried to create an unbound text box and
format it as a short date but I have no way to know how I can tell the
subforms to link back to whatever is in that field and post it to the date
field in the response table for that set of questions. I then thought if I
could find a way to make it so I type a date and after focus have it store
that date to some table, I may then be able to find a way to make them use
the date in the table and post it to the response date field. I am just
looking for an easy way to do this without getting into complicated
coding. I
know there may be a more efficient way of doing all of this but I have my
entire project completed except implementing the date. Any help is
appreciated.

Thanks,
Dale

 

[tina]

you're welcome

Thanks Tina,

I got it to work once I placed it in the subform. I ended up having to use
it in On Dirty because of how the layout was in order for it to populate when
I wanted it to but is working great. Many thanks.

Dale

as i said in my previous post, add the code to *each subform's BeforeUpdate
event*, NOT to the main form textbox control's event. change the code back
to the way i gave it to you, *removing* the table reference; you can't set a
reference to a table that way in code, and you don't need to reference the
table at all in this code, since the subform is already bound to the table.

as for setting the ControlSource of an unbound textbox on the subform, you
can do that - though i see no need, since the date already shows on the main
form. note that setting the ControlSource of a control to an expression
makes it a "display only" control, commonly called a calculated control; in
other words, it is still unbound, has no effect on data in the underlying
table. at any rate, the correct syntax would be

=[Forms]![NameOfMainForm]![NameOfMainFormTextboxControl]

btw, if you really have a field in your table called "Date", suggest you
either change it (can be difficult when all the queries, forms, reports, etc
have already been built) - or at least make sure you never use that as a
fieldname in future databases. see
http://home.att.net/~california.db/tips.html#aTip5 for more information.

hth

I must have done something wrong because this is not working for me.

What
I

did is create a basic unbound textbox. I went to BeforeUpdate and selected
the ... to bring up the VBA program. I then pasted the text you gave

and
made

a change so that it is like this:

Private Sub Text64_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me![Response]![Date] = Me.Parent!Text64
End If

End Sub

Text64 is the basic title of the unbound box
[Response]![Date] is the table and field that I want the date to be

placed
in.

I then go to my subform which is bound to the Response table and I happened
to have an unbound text box there so I placed =[Response]![Date] for the
control source of it.

This does not seem to work as nothing happens. The field remains blank when
the data is posted. where did I go wrong?


:

an unbound textbox control on the main form should work okay. in each
subform's BeforeUpdate event, add code to write the date from that control
into the current record. assuming that you only want the date added

to
*new*

records (not changed on *existing* records that are edited), try the
following, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!DateField = Me.Parent!UnboundTextbox
End If

End Sub

change the DateField and UnboundTextbox to the correct names, of

course.
and

make sure that the response table's date field in included in the subform's
RecordSource. (if you don't know how to add a VBA event procedure to

a
form,

see "Create a VBA event procedure" at
http://home.att.net/~california.db/instructions.html for illustrated
instructions.

hth


I have been working on a form which I ended up having to base the fields
off
of a query. To make a long story short the top part is the main

form
and

is
bound to a simply query that displays fields in two tables. Below is
several
subforms that are bound to a response table. The top part is currently
designed so that I select a person and the other fields at the top
populate.
The bottom subforms are where they select answers to questions and the
results are all stored in a table called response. This all is working
except
one thing. Each of the set of questions is in its own subform. I

know
I

could
have designed this different but based on other feedback I

received
and

trouble I ran into getting this created this seemed to be the best
solution
for the way I wanted data entry to work and also it seems to work so I
left
it so each question is built in its own subform. In these sets of subforms
maybe the first 10 are based upon one paper form the user has, the next
set
of 15 are based on another paper form, and so on.

To make this long story short I need to be able to have the user

type
a

date
somewhere on the form and that date be stored to some temporay

date
table

or
just stored somewhere so that when each question is answered in the
subform,
it will use whatever date is there and post it to the response

table.
I

have
no clue on how to do this. I first tried to create an unbound text

box
and

format it as a short date but I have no way to know how I can tell the
subforms to link back to whatever is in that field and post it to

the
date

field in the response table for that set of questions. I then

thought
if I

could find a way to make it so I type a date and after focus have

it
store

that date to some table, I may then be able to find a way to make

them
use

the date in the table and post it to the response date field. I am just
looking for an easy way to do this without getting into complicated
coding. I
know there may be a more efficient way of doing all of this but I

have
my

entire project completed except implementing the date. Any help is
appreciated.

Thanks,
Dale