coding problems

[Afrosheen]

Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second one #2 is
Training. They have a relationship. Poor tables. This is what I'm try to do.
#1 is active and when i check a box I want table #2 to also get the check
box. Here is my coding.

Private Sub trainer_Click()
If trainer = True Then <---- Table #1
Me.tbl_training [check] = True <-----Table #2
Else
Me.tbl_training [check] = False
End If
End Sub

I keep getting an error. I thought I was putting the code in correctly. I
must be suffering from dain bramage.

Your help would be appreciated.

Thanks

 

[pietlinden]

Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second one #2 is
Training. They have a relationship. Poor tables.  This is what I'm try to do.
#1 is active and when i check a box I want table #2 to also get the check
box. Here is my coding.

Private Sub trainer_Click()
  If trainer = True Then   <---- Table #1
     Me.tbl_training [check] = True  <-----Table #2
  Else
     Me.tbl_training [check] = False
  End If
End Sub

I keep getting an error. I thought I was putting the code in correctly. I
must be suffering from dain bramage.

Your help would be appreciated.

Thanks

if the two controls are always going to have the same value, then (1)
one of them is redundant...(2) in the afterUpdate event of one, set
the other to have the same value.

Me.chkTraining1 = me.chkTraining2

 

[Rob Parker]

Piet's point (in another answer) about redundant data is well made.
However, his solution almost certainly won't work because the second field
you are trying to update is not in the table which is the form's
recordsource - or at least, from what you've written, I don't think it is;
I'm assuming that when you say " #1 is active" that you mean that it is the
recordsource of a form which you are using for data entry/editing.

You don't say what the relationship between your tables is, so it's
difficult to offer any real help. But if you need to do this (and, as Piet
says, it's likely that you don't), you'll need to create a query (which must
be updateable (see http://allenbrowne.com/ser-61.html for more information);
and if your relationship is a one-to-many one, your query may only be
updateable for fields in one or other table, not both) and use that as the
recordsource for your form.

A couple of other points:

1. I'm assuming that Trainer is the name of a checkbox control on your
form, and also the name of the field to which it is bound (a Yes/No field in
table 1). I'd strongly suggest that you give the control a different name
to its bound field (I'd name it chkTrainer); that way, Access won't get
confused and possibly produce errors.

2. You should not use the Click event of a checkbox to attempt to change
something else when the checkbox changes, since it may not occur when the
control's value changes; eg. if the user tabs to the control, and changes
its state by pressing the space-bar, the Click event will not occur, and
your code will not run. You should use the AfterUpdate event.

3. If you still decide that you must update a field in a separate table
(and I suggest that you carefully examine you data structure to ensure that
it's correct - as Piet says, it's likely that you've got redundant data (or
some other problem)), and you can't construct a suitable updateable query,
you can instead write some code to run an update query to amend the data in
the second table.

HTH,

Rob

 

[Afrosheen]

Thanks for the information.

The relationship between the two tables is a many to one. The many is the
Roster table and the one is the Training table. I also hate to be redundant
and I wish I knew enough about Access to just use one statement for many
operations. In Dbase or Foxpro you could do that by calling the procedure.
But unfortunately I don't. I'm really a newbe with about 1months experience.

The reason I need to place a check mark in the training table is for a
future reference. If it can't be done then I'll have to find a work around.

Thanks for the help

Piet's point (in another answer) about redundant data is well made.
However, his solution almost certainly won't work because the second field
you are trying to update is not in the table which is the form's
recordsource - or at least, from what you've written, I don't think it is;
I'm assuming that when you say " #1 is active" that you mean that it is the
recordsource of a form which you are using for data entry/editing.

You don't say what the relationship between your tables is, so it's
difficult to offer any real help. But if you need to do this (and, as Piet
says, it's likely that you don't), you'll need to create a query (which must
be updateable (see http://allenbrowne.com/ser-61.html for more information);
and if your relationship is a one-to-many one, your query may only be
updateable for fields in one or other table, not both) and use that as the
recordsource for your form.

A couple of other points:

1. I'm assuming that Trainer is the name of a checkbox control on your
form, and also the name of the field to which it is bound (a Yes/No field in
table 1). I'd strongly suggest that you give the control a different name
to its bound field (I'd name it chkTrainer); that way, Access won't get
confused and possibly produce errors.

2. You should not use the Click event of a checkbox to attempt to change
something else when the checkbox changes, since it may not occur when the
control's value changes; eg. if the user tabs to the control, and changes
its state by pressing the space-bar, the Click event will not occur, and
your code will not run. You should use the AfterUpdate event.

3. If you still decide that you must update a field in a separate table
(and I suggest that you carefully examine you data structure to ensure that
it's correct - as Piet says, it's likely that you've got redundant data (or
some other problem)), and you can't construct a suitable updateable query,
you can instead write some code to run an update query to amend the data in
the second table.

HTH,

Rob

Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second one
#2 is Training. They have a relationship. Poor tables. This is what
I'm try to do. #1 is active and when i check a box I want table #2 to
also get the check box. Here is my coding.

Private Sub trainer_Click()
If trainer = True Then <---- Table #1
Me.tbl_training [check] = True <-----Table #2
Else
Me.tbl_training [check] = False
End If
End Sub

I keep getting an error. I thought I was putting the code in
correctly. I must be suffering from dain bramage.

Your help would be appreciated.

Thanks

 

[Rob Parker]

It can be done. The real problem is if it needs to be done. You haven't
told us anything about the fields in your tables, or the details of the
relationship between them. If you do that, then you'll certainly get more
help.

Rob

Thanks for the information.

The relationship between the two tables is a many to one. The many is the
Roster table and the one is the Training table. I also hate to be
redundant
and I wish I knew enough about Access to just use one statement for many
operations. In Dbase or Foxpro you could do that by calling the procedure.
But unfortunately I don't. I'm really a newbe with about 1months
experience.

The reason I need to place a check mark in the training table is for a
future reference. If it can't be done then I'll have to find a work
around.

Thanks for the help

Piet's point (in another answer) about redundant data is well made.
However, his solution almost certainly won't work because the second
field
you are trying to update is not in the table which is the form's
recordsource - or at least, from what you've written, I don't think it
is;
I'm assuming that when you say " #1 is active" that you mean that it is
the
recordsource of a form which you are using for data entry/editing.

You don't say what the relationship between your tables is, so it's
difficult to offer any real help. But if you need to do this (and, as
Piet
says, it's likely that you don't), you'll need to create a query (which
must
be updateable (see http://allenbrowne.com/ser-61.html for more
information);
and if your relationship is a one-to-many one, your query may only be
updateable for fields in one or other table, not both) and use that as
the
recordsource for your form.

A couple of other points:

1. I'm assuming that Trainer is the name of a checkbox control on your
form, and also the name of the field to which it is bound (a Yes/No field
in
table 1). I'd strongly suggest that you give the control a different
name
to its bound field (I'd name it chkTrainer); that way, Access won't get
confused and possibly produce errors.

2. You should not use the Click event of a checkbox to attempt to change
something else when the checkbox changes, since it may not occur when the
control's value changes; eg. if the user tabs to the control, and changes
its state by pressing the space-bar, the Click event will not occur, and
your code will not run. You should use the AfterUpdate event.

3. If you still decide that you must update a field in a separate table
(and I suggest that you carefully examine you data structure to ensure
that
it's correct - as Piet says, it's likely that you've got redundant data
(or
some other problem)), and you can't construct a suitable updateable
query,
you can instead write some code to run an update query to amend the data
in
the second table.

HTH,

Rob

Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second one
#2 is Training. They have a relationship. Poor tables. This is what
I'm try to do. #1 is active and when i check a box I want table #2 to
also get the check box. Here is my coding.

Private Sub trainer_Click()
If trainer = True Then <---- Table #1
Me.tbl_training [check] = True <-----Table #2
Else
Me.tbl_training [check] = False
End If
End Sub

I keep getting an error. I thought I was putting the code in
correctly. I must be suffering from dain bramage.

Your help would be appreciated.

Thanks

 

[Afrosheen]

Sorry, I guess my question was way off. Must have had a brain cramp.

What I really need to do is if the Instructor box is checked in the roster
table it will trigger the afterupdate to place a staffid, lname, and fname in
a new record in the training table fields. That way I will not have other
staff in the training table.Then there's a toggle button to bring up the
fields in the training table to check off or on.

The relationships are {I believe} is the Staff Id. I have both set up in
each table
I also have a lname and fname in both tables.

I hope this gave you enough information.

It can be done. The real problem is if it needs to be done. You haven't
told us anything about the fields in your tables, or the details of the
relationship between them. If you do that, then you'll certainly get more
help.

Rob

Thanks for the information.

The relationship between the two tables is a many to one. The many is the
Roster table and the one is the Training table. I also hate to be
redundant
and I wish I knew enough about Access to just use one statement for many
operations. In Dbase or Foxpro you could do that by calling the procedure.
But unfortunately I don't. I'm really a newbe with about 1months
experience.

The reason I need to place a check mark in the training table is for a
future reference. If it can't be done then I'll have to find a work
around.

Thanks for the help

Piet's point (in another answer) about redundant data is well made.
However, his solution almost certainly won't work because the second
field
you are trying to update is not in the table which is the form's
recordsource - or at least, from what you've written, I don't think it
is;
I'm assuming that when you say " #1 is active" that you mean that it is
the
recordsource of a form which you are using for data entry/editing.

You don't say what the relationship between your tables is, so it's
difficult to offer any real help. But if you need to do this (and, as
Piet
says, it's likely that you don't), you'll need to create a query (which
must
be updateable (see http://allenbrowne.com/ser-61.html for more
information);
and if your relationship is a one-to-many one, your query may only be
updateable for fields in one or other table, not both) and use that as
the
recordsource for your form.

A couple of other points:

1. I'm assuming that Trainer is the name of a checkbox control on your
form, and also the name of the field to which it is bound (a Yes/No field
in
table 1). I'd strongly suggest that you give the control a different
name
to its bound field (I'd name it chkTrainer); that way, Access won't get
confused and possibly produce errors.

2. You should not use the Click event of a checkbox to attempt to change
something else when the checkbox changes, since it may not occur when the
control's value changes; eg. if the user tabs to the control, and changes
its state by pressing the space-bar, the Click event will not occur, and
your code will not run. You should use the AfterUpdate event.

3. If you still decide that you must update a field in a separate table
(and I suggest that you carefully examine you data structure to ensure
that
it's correct - as Piet says, it's likely that you've got redundant data
(or
some other problem)), and you can't construct a suitable updateable
query,
you can instead write some code to run an update query to amend the data
in
the second table.

HTH,

Rob


Afrosheen wrote:
Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second one
#2 is Training. They have a relationship. Poor tables. This is what
I'm try to do. #1 is active and when i check a box I want table #2 to
also get the check box. Here is my coding.

Private Sub trainer_Click()
If trainer = True Then <---- Table #1
Me.tbl_training [check] = True <-----Table #2
Else
Me.tbl_training [check] = False
End If
End Sub

I keep getting an error. I thought I was putting the code in
correctly. I must be suffering from dain bramage.

Your help would be appreciated.

Thanks

 

[Rob Parker]

Sorry, I still don't understand exactly what you are trying to do. And I
still suspect that you've got your database design wrong - it's almost never
correct to be creating new records in a separate table based on a change in
an existing record in another table. And, if you've got StaffID, Fname and
Lname in both tables, and you're trying to populate them concurrently,
you've almost certainly got things wrong.

Since you won't share you table fields and their relationships, I'm unable
to help you further. Maybe someone else can make sense of what you're
attempting (but I doubt it).

Rob

Sorry, I guess my question was way off. Must have had a brain cramp.

What I really need to do is if the Instructor box is checked in the
roster table it will trigger the afterupdate to place a staffid,
lname, and fname in a new record in the training table fields. That
way I will not have other staff in the training table.Then there's a
toggle button to bring up the fields in the training table to check
off or on.

The relationships are {I believe} is the Staff Id. I have both set up
in each table
I also have a lname and fname in both tables.

I hope this gave you enough information.

It can be done. The real problem is if it needs to be done. You
haven't told us anything about the fields in your tables, or the
details of the relationship between them. If you do that, then
you'll certainly get more help.

Rob

Thanks for the information.

The relationship between the two tables is a many to one. The many
is the Roster table and the one is the Training table. I also hate
to be redundant
and I wish I knew enough about Access to just use one statement for
many operations. In Dbase or Foxpro you could do that by calling
the procedure. But unfortunately I don't. I'm really a newbe with
about 1months experience.

The reason I need to place a check mark in the training table is
for a future reference. If it can't be done then I'll have to find
a work around.

Thanks for the help

:

Piet's point (in another answer) about redundant data is well made.
However, his solution almost certainly won't work because the
second field
you are trying to update is not in the table which is the form's
recordsource - or at least, from what you've written, I don't
think it is;
I'm assuming that when you say " #1 is active" that you mean that
it is the
recordsource of a form which you are using for data entry/editing.

You don't say what the relationship between your tables is, so it's
difficult to offer any real help. But if you need to do this
(and, as Piet
says, it's likely that you don't), you'll need to create a query
(which must
be updateable (see http://allenbrowne.com/ser-61.html for more
information);
and if your relationship is a one-to-many one, your query may only
be updateable for fields in one or other table, not both) and use
that as the
recordsource for your form.

A couple of other points:

1. I'm assuming that Trainer is the name of a checkbox control on
your form, and also the name of the field to which it is bound (a
Yes/No field in
table 1). I'd strongly suggest that you give the control a
different name
to its bound field (I'd name it chkTrainer); that way, Access
won't get confused and possibly produce errors.

2. You should not use the Click event of a checkbox to attempt to
change something else when the checkbox changes, since it may not
occur when the control's value changes; eg. if the user tabs to
the control, and changes its state by pressing the space-bar, the
Click event will not occur, and your code will not run. You
should use the AfterUpdate event.

3. If you still decide that you must update a field in a separate
table (and I suggest that you carefully examine you data structure
to ensure that
it's correct - as Piet says, it's likely that you've got redundant
data (or
some other problem)), and you can't construct a suitable updateable
query,
you can instead write some code to run an update query to amend
the data in
the second table.

HTH,

Rob


Afrosheen wrote:
Thanks for reading my question.

I have two tables that I'm working with #1 is Roster, the second
one #2 is Training. They have a relationship. Poor tables. This
is what I'm try to do. #1 is active and when i check a box I want
table #2 to also get the check box. Here is my coding.

Private Sub trainer_Click()
If trainer = True Then <---- Table #1
Me.tbl_training [check] = True <-----Table #2
Else
Me.tbl_training [check] = False
End If
End Sub

I keep getting an error. I thought I was putting the code in
correctly. I must be suffering from dain bramage.

Your help would be appreciated.

Thanks