Update fields in a table within a Form

[Sondra]

I have created a table that contains the following:

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason
BinderA
BinderB
BinderC
etc...BinderABD

I have created an entry form for the information when
originally distributed to the binders
DocumentID
Version
DocumentTitle
BinderA
BinderB
BinderC
etc...BinderABD

Then I created an obsoletion form for the information when
it is no longer a valid procedure.

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason

I originally had toggle buttons for the binders on both
forms for the user to press when the document must go to
the binder.

But now I want to set it up so the user can Obsolete the
Document and it will automatically set the Binder YES/NO
option to NO. When a Document is obsolete it is obsolete
from all binders.

My first thought was:

Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
Me.togBinderA = 0
Me.togBinderB = 0
Me.togBinderC = 0
Me.togBinderD = 0
....etc
End If
End Sub

But that is not working as we continue to add more binders.

Can someone help me to develop code that would
automatically update the table for those binders to NO
when the chkObsolete is pressed?

I know this isn't correct, bu I was thinking something
like this:
Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
[MYTable]![BinderA] = 0
[MYTable]![BinderB] = 0
[MYTable]![BinderC] = 0
[MYTable]![BinderD] = 0
....etc
End If
End Sub

Any advise would be great.

Thanks in advance.

 

[Nikos Yannacopoulos]

Sondra,

To begin with, there is a regular many-to-many relationship between
Documents and binders, so the right way to reflect that in your data
structure would be a separate table called, say, tblDocsInBinders,
joined on DocumentID to tblDocuments and on BinderID to tblBinders,
instead of having a field for each binder in your docs table. You have
already hit one of the reasons why you should do that: each new binder
added is now a major design change: tavble design, form design... and
who knows what else further down. Therefore, if it's not too late for
you to modify your design, I urge you to do so, knowing you'll get all
the help you need here.
Still, if you're not prepared to do that, there's a cheap and dirty
trick to deal with your immediate problem (while the rest will still
come up!). The trick consists in naming the binder fields consistently,
e.g. all starting with "togBinder" while no other field does, so you can
loop through all the controls on the form and set the value of those
with a name like togBinderXX. The code would look something like:

Private Sub chkobsolete_AfterUpdate()
If Me.chkObsolete Then
For Each ctl in Me.Controls
If Left(ctl.Name, 9) = "togBinder" Then
ctl = 0
End If
Next
End If
End Sub

HTH,
Nikos

I have created a table that contains the following:

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason
BinderA
BinderB
BinderC
etc...BinderABD

I have created an entry form for the information when
originally distributed to the binders
DocumentID
Version
DocumentTitle
BinderA
BinderB
BinderC
etc...BinderABD

Then I created an obsoletion form for the information when
it is no longer a valid procedure.

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason

I originally had toggle buttons for the binders on both
forms for the user to press when the document must go to
the binder.

But now I want to set it up so the user can Obsolete the
Document and it will automatically set the Binder YES/NO
option to NO. When a Document is obsolete it is obsolete
from all binders.

My first thought was:

Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
Me.togBinderA = 0
Me.togBinderB = 0
Me.togBinderC = 0
Me.togBinderD = 0
....etc
End If
End Sub

But that is not working as we continue to add more binders.

Can someone help me to develop code that would
automatically update the table for those binders to NO
when the chkObsolete is pressed?

I know this isn't correct, bu I was thinking something
like this:
Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
[MYTable]![BinderA] = 0
[MYTable]![BinderB] = 0
[MYTable]![BinderC] = 0
[MYTable]![BinderD] = 0
....etc
End If
End Sub

Any advise would be great.

Thanks in advance.

 

[Guest]

Nikos:

Thank you for your advise. Here is my concerns about
normalizing and maybe an expert like yourself can help me
identify my worries.

1. I create a table that containes Document Information
DocumentNumber
DocumentTitle
DocumentVersion

2. I create a table that contains Binder Information
BinderA
BinderB
BinderC
DocumentNumber

3. I create a relationship between Binder and Document
Information, using Document Number as the relationship
field.

4. User enters Document Info into the Document Table

5. User enters Binder Information into the Binder Table

This all makes perfect sense to this point. But I don't
want to have the user have to enter the same document
information into both tables. So If I normalize how does
the data from one table connects to the other. In other
words.

1. Create a query to link both tables

2. Creat a form to enter data.

Does this enter the document number for the binder?

Sorry I'm very confused on this issue. Any guidance would
be great.

Thanks.

-----Original Message-----
Sondra,

To begin with, there is a regular many-to-many relationship between
Documents and binders, so the right way to reflect that in your data
structure would be a separate table called, say, tblDocsInBinders,
joined on DocumentID to tblDocuments and on BinderID to tblBinders,
instead of having a field for each binder in your docs table. You have
already hit one of the reasons why you should do that: each new binder
added is now a major design change: tavble design, form design... and
who knows what else further down. Therefore, if it's not too late for
you to modify your design, I urge you to do so, knowing you'll get all
the help you need here.
Still, if you're not prepared to do that, there's a cheap and dirty
trick to deal with your immediate problem (while the rest will still
come up!). The trick consists in naming the binder fields consistently,
e.g. all starting with "togBinder" while no other field does, so you can
loop through all the controls on the form and set the value of those
with a name like togBinderXX. The code would look something like:

Private Sub chkobsolete_AfterUpdate()
If Me.chkObsolete Then
For Each ctl in Me.Controls
If Left(ctl.Name, 9) = "togBinder" Then
ctl = 0
End If
Next
End If
End Sub

HTH,
Nikos

I have created a table that contains the following:

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason
BinderA
BinderB
BinderC
etc...BinderABD

I have created an entry form for the information when
originally distributed to the binders
DocumentID
Version
DocumentTitle
BinderA
BinderB
BinderC
etc...BinderABD

Then I created an obsoletion form for the information when
it is no longer a valid procedure.

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason

I originally had toggle buttons for the binders on both
forms for the user to press when the document must go to
the binder.

But now I want to set it up so the user can Obsolete the
Document and it will automatically set the Binder YES/NO
option to NO. When a Document is obsolete it is obsolete
from all binders.

My first thought was:

Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
Me.togBinderA = 0
Me.togBinderB = 0
Me.togBinderC = 0
Me.togBinderD = 0
....etc
End If
End Sub

But that is not working as we continue to add more binders.

Can someone help me to develop code that would
automatically update the table for those binders to NO
when the chkObsolete is pressed?

I know this isn't correct, bu I was thinking something
like this:
Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
[MYTable]![BinderA] = 0
[MYTable]![BinderB] = 0
[MYTable]![BinderC] = 0
[MYTable]![BinderD] = 0
....etc
End If
End Sub

Any advise would be great.

Thanks in advance.

.

 

[Nikos Yannacopoulos]

Sondra,

If I understand this correctly, you are still not very clear on the
design; do you mean the table in your step 2 below has one record per
document (joined to the docs table on DocumentNumber), and one yes/no
field for each Binder, or am I reading it all wrong?
If this is indeed what it is, then the relationship between the two
tables is a 1-to-1, which effectively means it's just as good as if you
had it all in one table, and the design is poor in that it fails to
reflect the many-to-many relationship that exists between documents and
binders in reality, and incurs all sorts of design (and performance)
problems. The standard way to reflect a many-to-many relationship in
your data structure is through the use of in intermediate table, which
has a 1-to-many relationship with each of the entity tables (docs and
binders in this case). This design, in your case, would look something like:

tblDocuments
DocumentNumber (Primary Key)
DocumentTitle
DocumentVersion
....


tblBinders
B_ID (Primary Key) (e.g. A, B, C... or 1, 2, 3...etc)
B_Name (e.g. BinderA or Faxes-In or whatever)
....

Now, the real relationship between the two, in palin English, is that "a
document may be filed in one or more binders, and each binder may hold
one or more documents". Let's call that filing, for the sake of the
example. To reflect this in our data structure we now need a new table like:

tblFiling
F_ID (Primary key, autonumber - optional)
DocumentNumber (Foreign Key -join on tblDocuments)
B_ID (Foreign Key -join on tblBinders)

So, for each document, there are as many records in tblFiling, as the
number of binders it is filed into. Creating the relationships above in
your database's Relationships (enforcing referential integrity is a good
idea) would give you a robust data structure. You can then do anything
using a combination of those tables in a wuery, as required. For new
(document) data entry, for instance, you can use a form on tblDocuments
with a subform on tblFiling, joined on DocumentNumber, to enter/edit
binders for the document.

HTH,
Nikos

Nikos:

Thank you for your advise. Here is my concerns about
normalizing and maybe an expert like yourself can help me
identify my worries.

1. I create a table that containes Document Information
DocumentNumber
DocumentTitle
DocumentVersion

2. I create a table that contains Binder Information
BinderA
BinderB
BinderC
DocumentNumber

3. I create a relationship between Binder and Document
Information, using Document Number as the relationship
field.

4. User enters Document Info into the Document Table

5. User enters Binder Information into the Binder Table

This all makes perfect sense to this point. But I don't
want to have the user have to enter the same document
information into both tables. So If I normalize how does
the data from one table connects to the other. In other
words.

1. Create a query to link both tables

2. Creat a form to enter data.

Does this enter the document number for the binder?

Sorry I'm very confused on this issue. Any guidance would
be great.

Thanks.

-----Original Message-----
Sondra,

To begin with, there is a regular many-to-many

relationship between

Documents and binders, so the right way to reflect that

in your data

structure would be a separate table called, say,
tblDocsInBinders,

joined on DocumentID to tblDocuments and on BinderID to
tblBinders,

instead of having a field for each binder in your docs

table. You have

already hit one of the reasons why you should do that:

each new binder

added is now a major design change: tavble design, form

design... and

who knows what else further down. Therefore, if it's not

too late for

you to modify your design, I urge you to do so, knowing

you'll get all

the help you need here.
Still, if you're not prepared to do that, there's a cheap

and dirty

trick to deal with your immediate problem (while the rest

will still

come up!). The trick consists in naming the binder fields
consistently,

e.g. all starting with "togBinder" while no other field

does, so you can

loop through all the controls on the form and set the

value of those

with a name like togBinderXX. The code would look

something like:

Private Sub chkobsolete_AfterUpdate()
If Me.chkObsolete Then
For Each ctl in Me.Controls
If Left(ctl.Name, 9) = "togBinder" Then
ctl = 0
End If
Next
End If
End Sub

HTH,
Nikos

I have created a table that contains the following:

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason
BinderA
BinderB
BinderC
etc...BinderABD

I have created an entry form for the information when
originally distributed to the binders
DocumentID
Version
DocumentTitle
BinderA
BinderB
BinderC
etc...BinderABD

Then I created an obsoletion form for the information

when

it is no longer a valid procedure.

DocumentID
Version
DocumentTitle
ObsoleteDoc
ObsoleteDate
ObsoleteReason

I originally had toggle buttons for the binders on both
forms for the user to press when the document must go

to

the binder.

But now I want to set it up so the user can Obsolete

the

Document and it will automatically set the Binder

YES/NO

option to NO. When a Document is obsolete it is

obsolete

from all binders.

My first thought was:

Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
Me.togBinderA = 0
Me.togBinderB = 0
Me.togBinderC = 0
Me.togBinderD = 0
....etc
End If
End Sub

But that is not working as we continue to add more

binders.

Can someone help me to develop code that would
automatically update the table for those binders to NO
when the chkObsolete is pressed?

I know this isn't correct, bu I was thinking something
like this:
Private Sub chkobsolete_AfterUpdate()

If Me.chkObsolete Then
[MYTable]![BinderA] = 0
[MYTable]![BinderB] = 0
[MYTable]![BinderC] = 0
[MYTable]![BinderD] = 0
....etc
End If
End Sub

Any advise would be great.

Thanks in advance.

.