Hi again,
Probably the easiest way is;
If DCount("*", "64EmpSurvey", "SurveyNumber = " & [SurveyNumber] & ") = 0 Then
If DCount("*", "64EmpSurveyNumbers", "ValidationNum = " & [SurveyNumber])
'make the fields visible
Else '<- survey has not been done but validation number is wrong
End If
Else '<- survey has been done already
End If
(watch out for wordwrap - best to copy & paste)
If 64EmpSurvey happens to contain all the surveyNumbers already and only the
date is added on completion then you would need to add the following to the
end of the first DCount line above;
AND Not IsNull(DateCompleted)
hth, post back if you need any more help with this,
good luck,
TonyT..
Bob Waggoner said:
64EmpSurvey table - fields that show completion are: Surveynumber and
DateCompleted.
Within the Survey form, the field surveynumber has an event (before update)
that checks the number entered against the 64EmpSurveyNumbers table to see
that the number is in that table. If it is, it makes the survey responses
visible. If not, it doesn't.
:
Sorry, probably din't make myself clear enough, what are the 2 table names
with their respective fields that contain both SurveyNumbers and a record of
the Survey having been completed, eg;
64EmpSurveyNumbers - the table listing the survey numbers available
ValidationNumber - list of fields within that table
etc etc
64EmpSurvey - table with the Survey details Including the field to record
completion
SurveyNumber
SurveyCompletedDate
TonyT..
:
A different table records the surveynumber - that table is: "64EmpSurvey" The
table in the code below is where the surveynumbers that validate the form and
make the drop down lists of questions visible.
Hope this gives enough information. I need the code below and then to have
the code check to see if the number entered has been used. If used, run the
"else" condition.
Thanks again!
"Private Sub SurveyNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "64EmpSurveyNumbers", "ValidationNum = " &
[SurveyNumber]) > 0 Then
'make the fields visible
[1a].Visible = True
[2a].Visible = True
[3a].Visible = True
[4a].Visible = True
[5a].Visible = True
[6a].Visible = True
[7a].Visible = True
[8a].Visible = True
[9a].Visible = True
[10a].Visible = True
[11a].Visible = True
[12a].Visible = True
Else
MsgBox "Please use a valid survey number - see survey administrator"
End If
End Sub
:
Is this a separate table?
If so the easiest way would be to do a
DCount("*","tblSurveyResults","IsNull(UsedSurveyNumber)") (where
tblSurveyResults = the results table and UsedSurveyNumbers = the record of
the completed surveys) and if that returns 0 then do your original DCount, If
it returns 0 then exit sub. Unless that is all in one table then it can be
done in one DCount, if so post the table fields in that table & I'll try to
give the actual code to use.
TonyT..
:
Thanks, Tony.
The survey populates a table which records the surveynumber and survey data.
I have a query that updates the "used" surveynumbers - but no way to stop
someone from reusing the same number. So, the completed survey records their
survey responses, the survey number they enter, and an autoid for the record.
:
Sorry Bob, I didn't see the original post,
Are you updating the/any tables records after the employee has taken the
survey? If so you need to add in 'AND SurveyCompleted <> -1' in your DCount
code (where 'SurveyCompleted' = the field name you record the competion in.
If there is no recording of the fact that a Survey has been succesfully
completed you will need to add another field to your table/s.
hth,
TonyT..
:
The code works (Thanks, Stefan Hoffmann) that checks the employee survey
number against the table and allows the survey to activate (or become
visible). How do I keep people from putting the same number in and taking the
survey twice? Here's the original code that works for part 1 - access to the
survey.
If DCount("*", "64EmpSurveyNumbers", "ValidationNum = " & [SurveyNumber]) >
0 Then
'make the fields visible
