'Causing' an entry in a field ...

[Sue Compelling]

Hi

Using ACCESS2000.

My DB is for a specific project that is monitoring the QA statndards of
cellsite construction. For each site to be 'signed off' - there needs to be
26 documents submitted for assessment.

So - each record has the CellSite #, Address, Territory, Status and then the
26 doc fields. The [doc x] fields] are populated from option boxes.

My requirement is ... I would like to default the STATUS of "complete" in
the [status] field once all the [doc x ] fields are denoted complete (or 3).

TIA

Sue

 

[Allen Browne]

Instead of storing the Complete field in your table, create a query and type
an expression like this into the Field row:
IsComplete: Not (([Doc1] Is Null) Or ([Doc2] Is Null) Or ...)
Then use the query anywhere where you need this field.

It might be a better design to create a related table, where one site can
have many documents. You can then count the number of documents to ensure
they are complete. Post back if you want further help to normalize your
design like that.

 

[John W. Vinson]

Hi

Using ACCESS2000.

My DB is for a specific project that is monitoring the QA statndards of
cellsite construction. For each site to be 'signed off' - there needs to be
26 documents submitted for assessment.

So - each record has the CellSite #, Address, Territory, Status and then the
26 doc fields. The [doc x] fields] are populated from option boxes.

My requirement is ... I would like to default the STATUS of "complete" in
the [status] field once all the [doc x ] fields are denoted complete (or 3).

TIA

Sue

My concerns in the other thread (about normalization) still apply; and ideally
the STATUS field should not exist in your table but instead be calculated on
demand... but you can do this in the Form's BeforeUpdate event. Poll through
the 26 fields and if they're all equal to 3 set the STATUS. Something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any other validity checking goes first>
Dim iDoc As Integer
For iDoc = 1 to 26
If Me.Controls("[Doc " & iDoc & "]) <> 3 Then Exit Sub
Next i
Me!STATUS = "Complete"
End Sub

 

[Sue Compelling]

Oh no - a TRIPLE growling about normalisation. You experts must hate us
hacks doing dodgey design.

Anyway - John - your response looks possibly the easiest for me to pursue ..

If I told you that the table is called [Sites], the Status field is called
[Status] and the 26 doc fields (cringe) are called [Doc1] through to [Doc26]
.... what is the EXACT code that I would need to use.

Also - I don't fully understand each line of your code so if you have time
to explain what each bit is trying to achieve I'd be really grateful ...

Cheers

Sue

--
Sue Compelling

Hi

Using ACCESS2000.

My DB is for a specific project that is monitoring the QA statndards of
cellsite construction. For each site to be 'signed off' - there needs to be
26 documents submitted for assessment.

So - each record has the CellSite #, Address, Territory, Status and then the
26 doc fields. The [doc x] fields] are populated from option boxes.

My requirement is ... I would like to default the STATUS of "complete" in
the [status] field once all the [doc x ] fields are denoted complete (or 3).

TIA

Sue

My concerns in the other thread (about normalization) still apply; and ideally
the STATUS field should not exist in your table but instead be calculated on
demand... but you can do this in the Form's BeforeUpdate event. Poll through
the 26 fields and if they're all equal to 3 set the STATUS. Something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any other validity checking goes first>
Dim iDoc As Integer
For iDoc = 1 to 26
If Me.Controls("[Doc " & iDoc & "]) <> 3 Then Exit Sub
Next i
Me!STATUS = "Complete"
End Sub

 

[John W. Vinson]

Oh no - a TRIPLE growling about normalisation. You experts must hate us
hacks doing dodgey design.

Anyway - John - your response looks possibly the easiest for me to pursue ..

If I told you that the table is called [Sites], the Status field is called
[Status] and the 26 doc fields (cringe) are called [Doc1] through to [Doc26]
... what is the EXACT code that I would need to use.

Also - I don't fully understand each line of your code so if you have time
to explain what each bit is trying to achieve I'd be really grateful ...

Listen to the good folks... normalization is Good For You...

But, holding my rising gorge in check, here's your code as best I can tell:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iDoc As Integer ' reserve space for a variable named iDoc
For iDoc = 1 to 26 ' loop that variable from 1 to 26
' Assuming that your fields AND your form controls are named
' Doc1, ..., Doc26, construct a text string by concatenating
' "Doc" to the value of iDoc. Me means "the current form";
' the Controls collection is the list of all controls on the form.
' The syntax Me.Controls("Doc3") is another way of referencing
' the control, an alternative to the more common Me!Doc3.

' Bail out of the routine if any one of the DocX controls has
' a value other than 3
If Me.Controls("Doc" & iDoc) <> 3 Then Exit Sub
Next iDoc ' end of the loop
' If you get all through the loop without hitting a non-3,
' set the Status
Me!STATUS = "Complete"
End Sub

 

[Sue Compelling]

John - you're a legend in your own lunchtime - it works, it works! (and just
keep swallowing that bile).

As a result of your tips and the badgering I had a quick crack at
'normalising' and I still couldn't get the Data Entry form (with it's double
subforms) to look anywhere near as intuitive, easy to use or easy of the eye
as my cobbled together version.

Are you interested in me emailing you them both (little 1.5 MB) to see
whether you will acquiesce to non-normalisation?

Cheers
--
Sue Compelling

Oh no - a TRIPLE growling about normalisation. You experts must hate us
hacks doing dodgey design.

Anyway - John - your response looks possibly the easiest for me to pursue ..

If I told you that the table is called [Sites], the Status field is called
[Status] and the 26 doc fields (cringe) are called [Doc1] through to [Doc26]
... what is the EXACT code that I would need to use.

Also - I don't fully understand each line of your code so if you have time
to explain what each bit is trying to achieve I'd be really grateful ...

Listen to the good folks... normalization is Good For You...

But, holding my rising gorge in check, here's your code as best I can tell:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iDoc As Integer ' reserve space for a variable named iDoc
For iDoc = 1 to 26 ' loop that variable from 1 to 26
' Assuming that your fields AND your form controls are named
' Doc1, ..., Doc26, construct a text string by concatenating
' "Doc" to the value of iDoc. Me means "the current form";
' the Controls collection is the list of all controls on the form.
' The syntax Me.Controls("Doc3") is another way of referencing
' the control, an alternative to the more common Me!Doc3.

' Bail out of the routine if any one of the DocX controls has
' a value other than 3
If Me.Controls("Doc" & iDoc) <> 3 Then Exit Sub
Next iDoc ' end of the loop
' If you get all through the loop without hitting a non-3,
' set the Status
Me!STATUS = "Complete"
End Sub

 

[John W. Vinson]

John - you're a legend in your own lunchtime - it works, it works! (and just
keep swallowing that bile).

As a result of your tips and the badgering I had a quick crack at
'normalising' and I still couldn't get the Data Entry form (with it's double
subforms) to look anywhere near as intuitive, easy to use or easy of the eye
as my cobbled together version.

Are you interested in me emailing you them both (little 1.5 MB) to see
whether you will acquiesce to non-normalisation?

Cheers

Well, at present, only if you're interested in my emailing you an invoice at
my current consulting rates. Sorry!

 

[Paul Shapiro]

I can tell you from personal experience a long time ago that the day will
come when there are 29 required documents. If your tables are in a
normalized structure, no program changes will be required. With your present
structure, all your calculations, forms and reports will need revision. The
revised structure is not very difficult. Something like:

Table Site (siteID, siteName)

Table Document(documentCode, documentName)

Table SiteDocument(siteID, documentCode, siteDocumentStatusTypeCode)

Table SiteDocumentStatusType(siteDocumentStatusTypeCode,
siteDocumentStatusTypeName, isComplete)

Now your site form would have a single subform for the site documents,
instead of 26 fixed controls for the current 26 documents.

Your calculated status would be something like:

Select S.siteID, count(iif(D.isComplete, 1, Null) as CompletedDocumentCount
From Site as S
Left Join SiteDocument As D On D.siteID=S.siteID
Group By S.siteID

You could expand this to include the count of the total number of required
documents, so you got a yes/no for siteDocumentsCompleted.

Oh no - a TRIPLE growling about normalisation. You experts must hate us
hacks doing dodgey design.

Anyway - John - your response looks possibly the easiest for me to pursue
..

If I told you that the table is called [Sites], the Status field is called
[Status] and the 26 doc fields (cringe) are called [Doc1] through to
[Doc26]
... what is the EXACT code that I would need to use.

Also - I don't fully understand each line of your code so if you have time
to explain what each bit is trying to achieve I'd be really grateful ...

Cheers

Sue

--
Sue Compelling

Hi

Using ACCESS2000.

My DB is for a specific project that is monitoring the QA statndards of
cellsite construction. For each site to be 'signed off' - there needs
to be
26 documents submitted for assessment.

So - each record has the CellSite #, Address, Territory, Status and then
the
26 doc fields. The [doc x] fields] are populated from option boxes.

My requirement is ... I would like to default the STATUS of "complete"
in
the [status] field once all the [doc x ] fields are denoted complete (or
3).

TIA

Sue

My concerns in the other thread (about normalization) still apply; and
ideally
the STATUS field should not exist in your table but instead be calculated
on
demand... but you can do this in the Form's BeforeUpdate event. Poll
through
the 26 fields and if they're all equal to 3 set the STATUS. Something
like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any other validity checking goes first>
Dim iDoc As Integer
For iDoc = 1 to 26
If Me.Controls("[Doc " & iDoc & "]) <> 3 Then Exit Sub
Next i
Me!STATUS = "Complete"
End Sub

 

[Sue Compelling]

Hi Paul

On no ... a FOURTH growling -

I put the same offer to you that I put to John. I could send you my two DBs
(NOTE - this is NOT to get free software development or whatever - it's for
you to simply LOOK). One I normalised - using John's structure (similar to
yours) and the other was my cobbled-together-work-of-art. I think if you
looked at the two, I'm hoping I may win a moral victory here and have you say
- in THIS instance I think your way has made a much more user friendly,
intuitive and easy on the eye form - than a normalised subform thingy ....

Up for the challenge?

(ps - I really appreciate these forums as a learning tool and can't believe
how generous people like you and John et al are of their time!)

Cheers

Sue

--
Sue Compelling

I can tell you from personal experience a long time ago that the day will
come when there are 29 required documents. If your tables are in a
normalized structure, no program changes will be required. With your present
structure, all your calculations, forms and reports will need revision. The
revised structure is not very difficult. Something like:

Table Site (siteID, siteName)

Table Document(documentCode, documentName)

Table SiteDocument(siteID, documentCode, siteDocumentStatusTypeCode)

Table SiteDocumentStatusType(siteDocumentStatusTypeCode,
siteDocumentStatusTypeName, isComplete)

Now your site form would have a single subform for the site documents,
instead of 26 fixed controls for the current 26 documents.

Your calculated status would be something like:

Select S.siteID, count(iif(D.isComplete, 1, Null) as CompletedDocumentCount
From Site as S
Left Join SiteDocument As D On D.siteID=S.siteID
Group By S.siteID

You could expand this to include the count of the total number of required
documents, so you got a yes/no for siteDocumentsCompleted.

Oh no - a TRIPLE growling about normalisation. You experts must hate us
hacks doing dodgey design.

Anyway - John - your response looks possibly the easiest for me to pursue
..

If I told you that the table is called [Sites], the Status field is called
[Status] and the 26 doc fields (cringe) are called [Doc1] through to
[Doc26]
... what is the EXACT code that I would need to use.

Also - I don't fully understand each line of your code so if you have time
to explain what each bit is trying to achieve I'd be really grateful ...

Cheers

Sue

--
Sue Compelling

On Sun, 31 Aug 2008 17:35:01 -0700, Sue Compelling

Hi

Using ACCESS2000.

My DB is for a specific project that is monitoring the QA statndards of
cellsite construction. For each site to be 'signed off' - there needs
to be
26 documents submitted for assessment.

So - each record has the CellSite #, Address, Territory, Status and then
the
26 doc fields. The [doc x] fields] are populated from option boxes.

My requirement is ... I would like to default the STATUS of "complete"
in
the [status] field once all the [doc x ] fields are denoted complete (or
3).

TIA

Sue

My concerns in the other thread (about normalization) still apply; and
ideally
the STATUS field should not exist in your table but instead be calculated
on
demand... but you can do this in the Form's BeforeUpdate event. Poll
through
the 26 fields and if they're all equal to 3 set the STATUS. Something
like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any other validity checking goes first>
Dim iDoc As Integer
For iDoc = 1 to 26
If Me.Controls("[Doc " & iDoc & "]) <> 3 Then Exit Sub
Next i
Me!STATUS = "Complete"
End Sub