2nd post, date stamp

[nydia]

I'm trying to create a database that keeps track of our
clients.

What I need help on is a questionaire that is filled out
by am employee about a client. There is about 25 question
and the same questions are asked 4 different times (2
months after start date, 4months later, 6 months later
etc). Start date is in another table. Right now, I have
the table set up with the following fields

clientID
Term (there are 4 terms, term 1= 2 months after start date
term 2=4months later etc)
date completed
q1
q2
q3
etc, etc
comments.

what i want is when the employee finishes all the
questions and comments for a date to automatically fill in
the date completed field, and for the employee not to be
able to change that date. how can this be accomplished
and does the table structure look ok.

any help is greatly appreciated.

..

 

[Scott McDaniel]

Two methods come to mind:

1) If the user MUST complete this form entirely before saving, then just set
the Default value (in the table) of [date completed] = Now() or =Date()
2) If the user can start this record, then come back to it later to complete
it (and you want to store the date this record is finished), then you'll
need code to check this in your form's BeforeUpdate event:

Sub FormName_BeforeUpdate(Cancel = True)

If Not IsNull(Me.q1) And Not IsNull(Me.q2) etc etc Then
Me![date completed] = Now()
End If

This should work, although you may wish to move it to the form's Current
event.

Note that unless you have secured your database (including your tables),
anyone can navigate to the tables and change any data they wish.

 

[Nydia]

I'm not really sure what this means and maybe, i'm not
explaining it correctly. This questionaire is going to be
filled out atleast 4 times for each client (if not more),
I eventually am going to do a report that will show all
the questionaires that were done in a specified month. So
I would like to be able to see when the employee finished
the questionaire. You gave codes but if i put =now() will
it update that date each time a client opens that
questionaire (what happens if i open it to review it, i
dont want the date to change)

-----Original Message-----
Two methods come to mind:

1) If the user MUST complete this form entirely before saving, then just set
the Default value (in the table) of [date completed] = Now () or =Date()
2) If the user can start this record, then come back to it later to complete
it (and you want to store the date this record is finished), then you'll
need code to check this in your form's BeforeUpdate event:

Sub FormName_BeforeUpdate(Cancel = True)

If Not IsNull(Me.q1) And Not IsNull(Me.q2) etc etc Then
Me![date completed] = Now()
End If

This should work, although you may wish to move it to the form's Current
event.

Note that unless you have secured your database (including your tables),
anyone can navigate to the tables and change any data they wish.
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

I'm trying to create a database that keeps track of our
clients.

What I need help on is a questionaire that is filled out
by am employee about a client. There is about 25 question
and the same questions are asked 4 different times (2
months after start date, 4months later, 6 months later
etc). Start date is in another table. Right now, I have
the table set up with the following fields

clientID
Term (there are 4 terms, term 1= 2 months after start date
term 2=4months later etc)
date completed
q1
q2
q3
etc, etc
comments.

what i want is when the employee finishes all the
questions and comments for a date to automatically fill in
the date completed field, and for the employee not to be
able to change that date. how can this be accomplished
and does the table structure look ok.

any help is greatly appreciated.

.

.

 

[Scott McDaniel]

Setting the Default value would not change the date as you review this data,
as the Default value is only set one time - when the record is inserted.

If your questionairre will be filled out four times, you would need four
separate records of it (linked via your CustomerID, most likely) ...
therefore, when you add each of these four records, the [date completed]
field would be filled in ...

If you use method 2, just check the value of [date completed] before
attempting to update it:

If Not IsNull(Me![date completed]) Then Exit Sub

This would force the sub routine to exit if ANYTHING has been entered in the
field
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

I'm not really sure what this means and maybe, i'm not
explaining it correctly. This questionaire is going to be
filled out atleast 4 times for each client (if not more),
I eventually am going to do a report that will show all
the questionaires that were done in a specified month. So
I would like to be able to see when the employee finished
the questionaire. You gave codes but if i put =now() will
it update that date each time a client opens that
questionaire (what happens if i open it to review it, i
dont want the date to change)

-----Original Message-----
Two methods come to mind:

1) If the user MUST complete this form entirely before saving, then just set
the Default value (in the table) of [date completed] = Now () or =Date()
2) If the user can start this record, then come back to it later to complete
it (and you want to store the date this record is finished), then you'll
need code to check this in your form's BeforeUpdate event:

Sub FormName_BeforeUpdate(Cancel = True)

If Not IsNull(Me.q1) And Not IsNull(Me.q2) etc etc Then
Me![date completed] = Now()
End If

This should work, although you may wish to move it to the form's Current
event.

Note that unless you have secured your database (including your tables),
anyone can navigate to the tables and change any data they wish.
--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

I'm trying to create a database that keeps track of our
clients.

What I need help on is a questionaire that is filled out
by am employee about a client. There is about 25 question
and the same questions are asked 4 different times (2
months after start date, 4months later, 6 months later
etc). Start date is in another table. Right now, I have
the table set up with the following fields

clientID
Term (there are 4 terms, term 1= 2 months after start date
term 2=4months later etc)
date completed
q1
q2
q3
etc, etc
comments.

what i want is when the employee finishes all the
questions and comments for a date to automatically fill in
the date completed field, and for the employee not to be
able to change that date. how can this be accomplished
and does the table structure look ok.

any help is greatly appreciated.

.

.