auto entry into second table after update

Everything is now working except when I edit a record there is no automatic entry into the log table.

"Wayne Morgan" wrote:

1) Use an If statement in the code to see if the value is one you find
acceptable. What is acceptable is dependent on your requirements and how
much detail you want to go into doing the check. Trying to get one that is
"different" will be very difficult to code. Just adding an extra space
between two words will make it "different".

Example:
If Len(Me.txtNote)20 Then
Msgbox "A note this short can't be a valid note"
End If

In the form's Properties sheet, remove the Close button from the form. Place
your own button on the form to close it. In the Click event of this button,
check the value of the note's textbox as indicated above. If it's not
acceptable, pop-up the message. If it is acceptable, set a form level "flag"
variable to True, save the record (it should save automatically when you
close the form, but there is a bug where this doesn't happen sometimes), and
close the form. In the form's UnLoad event, check the value of this form
level "flag" variable.

If bolFlag True Then
Cancel = True
End If

This won't let the form close unless you set the value of bolFlag to True.

2) Cancel = True will cancel the event in progress. Some of the events, such
as a form's Open, Unload, and BeforeUpdate and a control's BeforeUpdate can
be cancelled.

3) See #1

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
That worked! I'm almost there. Now, about the Notes field...you said
don't let the user close the form until they fill it in. "In the form's
BeforeUpdate event, check the value of the note textbox and if it's not what
you want, set Cancel=True."

1) How do I check the value?
2) What does Cancel=True do?
3) How do I make sure that the user enters a note that is different than
the last one?


"Wayne Morgan" wrote:

by the way, View/Edit Form is a subform of another form (i dont know
if
that makes a difference)

Yes, that makes a difference. A subform is not "Open" in its own right
and
is therefore not part of the Forms collection. To get to the subform you
have to refer to the main form holding it then follow the path down to
the
subform.

Forms!frmMainForm!ctlSubformControl.Form!ctlContro lOnSubform

ctlSubformControl is a control on the main form. This control holds the
subform. To get the name of this control, open the main form in design
mode,
open the Properties sheet, and click on the subform ONE time. The
Properties
sheet should show the name of the subform control. If you click on the
subform a second time, you'll be in the subform and the Properties sheet
will show the name of the subform, not the control holding it.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
1) yes I did change the name...so the default value of the text box
on
the pop up form is =[Forms]![View/Edit Form]![Circuit ID1]
where View/Edit Form is my main form and Circuit ID1 is the name of the
text
box on my main form...by the way, View/Edit Form is a subform of another
form (i dont know if that makes a difference)

2) no they don't have the same name

I'm leaving work for the day so I won't be able to reply until
tomorrow.
Please check tomorrow. Thanks for your help...I really appreciate it!

"Wayne Morgan" wrote:

If you're getting #Name, you should be close. There are a couple of
things
to check. 1) Did you change txtIDTextbox and MainForm to the names
you
are
using for your textbox and form? 2) Do the textbox and the field it
is
bound
to have the same name? If so, change the name of the textbox. The
easiest
way to do this is usually to just add a prefix to the name. For
example,
if
the field is called ID then call the textbox txtID. The name of the
text
box
is on the Other tab of its Properties sheet. Also, the main form
must
still
be open to read the data from it, but since this is being done from
a
pop-up
form that shouldn't be a problem.

I just tested it to double check and it works.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
I tried the first way. In the txtIDTextbox it says '#Name?' ...
I
was
going to try the second way, but I couldn't find the AfterUpdate
section
of
the form in VBA.

"Wayne Morgan" wrote:

There are 2 ways to pass this, which ever you prefer.

1) You can set the default value of the textbox on the pop-up
form
to
the
value of the textbox on the main form that holds this value.

Example:
=Forms!MainForm!txtIDTextbox

2) You could pass this value in the OpenArgs argument of the
OpenForm
call
then check the OpenArgs value in the pop-up's Open or Load event
and
assign
the value to the textbox on the pop-up form.

Example:
DoCmd.OpenForm "FormName",,,, acFormAdd,acDialog,
CStr(Me.txtIDTextbox)

Then in the Open or Load event of the pop-up:
Me.txtIDTextbox = Me.OpenArgs
Me.txtDateTextbox = Date

The Date could also be done as in #1.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message
...
Ok, I'm up to the point where I have to " Pass the value of
the ID
field
of the current
record...". How do I pass it?

"Wayne Morgan" wrote:

Build a form based on the log table. In the AfterUpdate event
of
the
current
form, open the new form as a pop-up (WindowMode argument set
to
acDialog
and
the DataMode argument to acFormAdd). This will pause the code
until
the
pop-up is closed or hidden. Pass the value of the ID field of
the
current
record in the OpenArgs argument or simply retrieve it when
the
form
opens
(i.e. =Forms!MainForm!txtIDTextbox for the DefaultValue of
the
textbox).
Place this ID in a Locked textbox so the user can't change
it.
Fill
in
the
date in another Locked textbox by setting its DefaultValue to
=Date()
or
=Now() if you want the time as well. There would be a 3rd
textbox
for
the
user to insert a note. Don't let them close the form until
they
fill
in
the
note field. To do this, in the form's BeforeUpdate event,
check
the
value
of
the note textbox and if it's not what you want, set
Cancel=True.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message
...
I have 2 tables. One is a main table containing all
information
about
my
records. The second table is a log table. What I want is
an
automatic
entry made into the log table after a user edits any record
through
a
form
bound to the main table. Then I want the focus on the form
to
go to
the
note field and force the user to enter a new note. The new
note
would
also
show in the log table as well.

How do I do this?

Are you popping up the form in the main form's AfterUpdate event? The entry
will be written to the log table when the pop-up is filled out and its
record saved. The pop-up form should be bound to the log table or a query
between them if you prefer.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
Everything is now working except when I edit a record there is no
automatic entry into the log table.

"Wayne Morgan" wrote:

1) Use an If statement in the code to see if the value is one you find
acceptable. What is acceptable is dependent on your requirements and how
much detail you want to go into doing the check. Trying to get one that
is
"different" will be very difficult to code. Just adding an extra space
between two words will make it "different".

Example:
If Len(Me.txtNote)20 Then
Msgbox "A note this short can't be a valid note"
End If

In the form's Properties sheet, remove the Close button from the form.
Place
your own button on the form to close it. In the Click event of this
button,
check the value of the note's textbox as indicated above. If it's not
acceptable, pop-up the message. If it is acceptable, set a form level
"flag"
variable to True, save the record (it should save automatically when you
close the form, but there is a bug where this doesn't happen sometimes),
and
close the form. In the form's UnLoad event, check the value of this form
level "flag" variable.

If bolFlag True Then
Cancel = True
End If

This won't let the form close unless you set the value of bolFlag to
True.

2) Cancel = True will cancel the event in progress. Some of the events,
such
as a form's Open, Unload, and BeforeUpdate and a control's BeforeUpdate
can
be cancelled.

3) See #1

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
That worked! I'm almost there. Now, about the Notes field...you said
don't let the user close the form until they fill it in. "In the
form's
BeforeUpdate event, check the value of the note textbox and if it's not
what
you want, set Cancel=True."

1) How do I check the value?
2) What does Cancel=True do?
3) How do I make sure that the user enters a note that is different
than
the last one?


"Wayne Morgan" wrote:

by the way, View/Edit Form is a subform of another form (i dont
know
if
that makes a difference)

Yes, that makes a difference. A subform is not "Open" in its own
right
and
is therefore not part of the Forms collection. To get to the subform
you
have to refer to the main form holding it then follow the path down
to
the
subform.

Forms!frmMainForm!ctlSubformControl.Form!ctlContro lOnSubform

ctlSubformControl is a control on the main form. This control holds
the
subform. To get the name of this control, open the main form in
design
mode,
open the Properties sheet, and click on the subform ONE time. The
Properties
sheet should show the name of the subform control. If you click on
the
subform a second time, you'll be in the subform and the Properties
sheet
will show the name of the subform, not the control holding it.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
1) yes I did change the name...so the default value of the text
box
on
the pop up form is =[Forms]![View/Edit Form]![Circuit
ID1]
where View/Edit Form is my main form and Circuit ID1 is the name of
the
text
box on my main form...by the way, View/Edit Form is a subform of
another
form (i dont know if that makes a difference)

2) no they don't have the same name

I'm leaving work for the day so I won't be able to reply until
tomorrow.
Please check tomorrow. Thanks for your help...I really appreciate
it!

"Wayne Morgan" wrote:

If you're getting #Name, you should be close. There are a couple
of
things
to check. 1) Did you change txtIDTextbox and MainForm to the
names
you
are
using for your textbox and form? 2) Do the textbox and the field
it
is
bound
to have the same name? If so, change the name of the textbox.
The
easiest
way to do this is usually to just add a prefix to the name. For
example,
if
the field is called ID then call the textbox txtID. The name of
the
text
box
is on the Other tab of its Properties sheet. Also, the main form
must
still
be open to read the data from it, but since this is being done
from
a
pop-up
form that shouldn't be a problem.

I just tested it to double check and it works.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
I tried the first way. In the txtIDTextbox it says '#Name?'
....
I
was
going to try the second way, but I couldn't find the AfterUpdate
section
of
the form in VBA.

"Wayne Morgan" wrote:

There are 2 ways to pass this, which ever you prefer.

1) You can set the default value of the textbox on the
pop-up
form
to
the
value of the textbox on the main form that holds this value.

Example:
=Forms!MainForm!txtIDTextbox

2) You could pass this value in the OpenArgs argument of the
OpenForm
call
then check the OpenArgs value in the pop-up's Open or Load
event
and
assign
the value to the textbox on the pop-up form.

Example:
DoCmd.OpenForm "FormName",,,, acFormAdd,acDialog,
CStr(Me.txtIDTextbox)

Then in the Open or Load event of the pop-up:
Me.txtIDTextbox = Me.OpenArgs
Me.txtDateTextbox = Date

The Date could also be done as in #1.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message
...
Ok, I'm up to the point where I have to " Pass the value
of
the ID
field
of the current
record...". How do I pass it?

"Wayne Morgan" wrote:

Build a form based on the log table. In the AfterUpdate
event
of
the
current
form, open the new form as a pop-up (WindowMode argument
set
to
acDialog
and
the DataMode argument to acFormAdd). This will pause the
code
until
the
pop-up is closed or hidden. Pass the value of the ID
field of
the
current
record in the OpenArgs argument or simply retrieve it
when
the
form
opens
(i.e. =Forms!MainForm!txtIDTextbox for the DefaultValue
of
the
textbox).
Place this ID in a Locked textbox so the user can't
change
it.
Fill
in
the
date in another Locked textbox by setting its
DefaultValue to
=Date()
or
=Now() if you want the time as well. There would be a 3rd
textbox
for
the
user to insert a note. Don't let them close the form
until
they
fill
in
the
note field. To do this, in the form's BeforeUpdate event,
check
the
value
of
the note textbox and if it's not what you want, set
Cancel=True.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message

...
I have 2 tables. One is a main table containing all
information
about
my
records. The second table is a log table. What I want
is
an
automatic
entry made into the log table after a user edits any
record
through
a
form
bound to the main table. Then I want the focus on the
form
to
go to
the
note field and force the user to enter a new note. The
new
note
would
also
show in the log table as well.

How do I do this?

In both the main form and the subform's AfterUpdate event I have the pop up form opening. I first only had it in the subform's AfterUpdate. Either way the form pops up (I have the pop up form bound to the Log table).

The pop up has a button that will close the form if the length of the note is greater than 5 characters - if not a Msgbox pops up. So after the form is closed how can I get it to update the log table? With an append query?

"Wayne Morgan" wrote:

Are you popping up the form in the main form's AfterUpdate event? The entry
will be written to the log table when the pop-up is filled out and its
record saved. The pop-up form should be bound to the log table or a query
between them if you prefer.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
Everything is now working except when I edit a record there is no
automatic entry into the log table.

"Wayne Morgan" wrote:

1) Use an If statement in the code to see if the value is one you find
acceptable. What is acceptable is dependent on your requirements and how
much detail you want to go into doing the check. Trying to get one that
is
"different" will be very difficult to code. Just adding an extra space
between two words will make it "different".

Example:
If Len(Me.txtNote)20 Then
Msgbox "A note this short can't be a valid note"
End If

In the form's Properties sheet, remove the Close button from the form.
Place
your own button on the form to close it. In the Click event of this
button,
check the value of the note's textbox as indicated above. If it's not
acceptable, pop-up the message. If it is acceptable, set a form level
"flag"
variable to True, save the record (it should save automatically when you
close the form, but there is a bug where this doesn't happen sometimes),
and
close the form. In the form's UnLoad event, check the value of this form
level "flag" variable.

If bolFlag True Then
Cancel = True
End If

This won't let the form close unless you set the value of bolFlag to
True.

2) Cancel = True will cancel the event in progress. Some of the events,
such
as a form's Open, Unload, and BeforeUpdate and a control's BeforeUpdate
can
be cancelled.

3) See #1

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
That worked! I'm almost there. Now, about the Notes field...you said
don't let the user close the form until they fill it in. "In the
form's
BeforeUpdate event, check the value of the note textbox and if it's not
what
you want, set Cancel=True."

1) How do I check the value?
2) What does Cancel=True do?
3) How do I make sure that the user enters a note that is different
than
the last one?


"Wayne Morgan" wrote:

by the way, View/Edit Form is a subform of another form (i dont
know
if
that makes a difference)

Yes, that makes a difference. A subform is not "Open" in its own
right
and
is therefore not part of the Forms collection. To get to the subform
you
have to refer to the main form holding it then follow the path down
to
the
subform.

Forms!frmMainForm!ctlSubformControl.Form!ctlContro lOnSubform

ctlSubformControl is a control on the main form. This control holds
the
subform. To get the name of this control, open the main form in
design
mode,
open the Properties sheet, and click on the subform ONE time. The
Properties
sheet should show the name of the subform control. If you click on
the
subform a second time, you'll be in the subform and the Properties
sheet
will show the name of the subform, not the control holding it.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
1) yes I did change the name...so the default value of the text
box
on
the pop up form is =[Forms]![View/Edit Form]![Circuit
ID1]
where View/Edit Form is my main form and Circuit ID1 is the name of
the
text
box on my main form...by the way, View/Edit Form is a subform of
another
form (i dont know if that makes a difference)

2) no they don't have the same name

I'm leaving work for the day so I won't be able to reply until
tomorrow.
Please check tomorrow. Thanks for your help...I really appreciate
it!

"Wayne Morgan" wrote:

If you're getting #Name, you should be close. There are a couple
of
things
to check. 1) Did you change txtIDTextbox and MainForm to the
names
you
are
using for your textbox and form? 2) Do the textbox and the field
it
is
bound
to have the same name? If so, change the name of the textbox.
The
easiest
way to do this is usually to just add a prefix to the name. For
example,
if
the field is called ID then call the textbox txtID. The name of
the
text
box
is on the Other tab of its Properties sheet. Also, the main form
must
still
be open to read the data from it, but since this is being done
from
a
pop-up
form that shouldn't be a problem.

I just tested it to double check and it works.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
I tried the first way. In the txtIDTextbox it says '#Name?'
....
I
was
going to try the second way, but I couldn't find the AfterUpdate
section
of
the form in VBA.

"Wayne Morgan" wrote:

There are 2 ways to pass this, which ever you prefer.

1) You can set the default value of the textbox on the
pop-up
form
to
the
value of the textbox on the main form that holds this value.

Example:
=Forms!MainForm!txtIDTextbox

2) You could pass this value in the OpenArgs argument of the
OpenForm
call
then check the OpenArgs value in the pop-up's Open or Load
event
and
assign
the value to the textbox on the pop-up form.

Example:
DoCmd.OpenForm "FormName",,,, acFormAdd,acDialog,
CStr(Me.txtIDTextbox)

Then in the Open or Load event of the pop-up:
Me.txtIDTextbox = Me.OpenArgs
Me.txtDateTextbox = Date

The Date could also be done as in #1.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message
...
Ok, I'm up to the point where I have to " Pass the value
of
the ID
field
of the current
record...". How do I pass it?

"Wayne Morgan" wrote:

Build a form based on the log table. In the AfterUpdate
event
of
the
current
form, open the new form as a pop-up (WindowMode argument
set
to
acDialog
and
the DataMode argument to acFormAdd). This will pause the
code
until
the
pop-up is closed or hidden. Pass the value of the ID
field of
the
current
record in the OpenArgs argument or simply retrieve it
when
the
form
opens
(i.e. =Forms!MainForm!txtIDTextbox for the DefaultValue
of
the
textbox).
Place this ID in a Locked textbox so the user can't
change
it.
Fill
in
the
date in another Locked textbox by setting its
DefaultValue to
=Date()
or
=Now() if you want the time as well. There would be a 3rd
textbox
for
the
user to insert a note. Don't let them close the form
until
they
fill
in
the
note field. To do this, in the form's BeforeUpdate event,
check
the
value
of
the note textbox and if it's not what you want, set
Cancel=True.

--
Wayne Morgan
Microsoft Access MVP


"Tony" wrote in message

...
I have 2 tables. One is a main table containing all
information
about
my
records. The second table is a log table. What I want
is
an
automatic
entry made into the log table after a user edits any
record
through
a
form
bound to the main table. Then I want the focus on the
form
to
go to
the
note field and force the user to enter a new note. The
new
note
would
also
show in the log table as well.

How do I do this?

If the pop-up and the controls on it are bound to the log table, it should
update automatically. However, as I mentioned earlier, there is a bug where
it sometimes won't, so it would be best to force the save before closing the
form.

By "bound to the table" I mean that the Record Source for the form should be
set to the log table (or a query between the table and form) and each of the
3 textboxes on the form should have their Control Source set to the
associated fields in the table. To save the record before closing the form,
run the command

If Me.Dirty = True Then Me.Dirty = False

before closing the form. This will force a save of the changes you typed
into the notes textbox and the 2 values in the date and id textboxes that
were entered automatically.

--
Wayne Morgan
MS Access MVP


"Tony" wrote in message
...
In both the main form and the subform's AfterUpdate event I have the pop
up form opening. I first only had it in the subform's AfterUpdate. Either
way the form pops up (I have the pop up form bound to the Log table).

The pop up has a button that will close the form if the length of the note
is greater than 5 characters - if not a Msgbox pops up. So after the form
is closed how can I get it to update the log table? With an append query?