select case not working correctly

[mpearce]

I developed a survey application for my companies helpdesk team. heres the schema:

TBL_survey
ticketnumber(pk)
FullName
q1Answe
q2Answer
q3Answer
q4Answer
comments

i am using a select case to figure out whether or not the user has submitted a survey previously for a given ticket. but for some reason regardless of whether or not a survey exists for a ticket the code doesnt let the user know that they have already submitted once. the code would run like it is a new entry. of course the record isnt written to the table due to the primary key, but i would like to let the user know there is already an entry for that ticket.

heres the code:

Sub writeToTable(ByVal previous_submission As Integer, ByVal areyousure As Integer, _
ByVal ticketnumber As String, ByVal fullname As String, _
ByVal finalanswer As String, ByVal finalanswer2 As String, _
ByVal finalanswer3 As String, ByVal finalanswer4 As String, _
ByVal comments As String)

areyousure = MsgBox("You are about to submit the following information:" & vbCrLf & vbCrLf & _
"Ticket Number: " & ticketnumber & vbCrLf & _
"Full Name: " & fullname & vbCrLf & vbCrLf & _
"Question 1 Response: " & finalanswer & vbCrLf & _
"Question 2 Response: " & finalanswer2 & vbCrLf & _
"Question 3 Response: " & finalanswer3 & vbCrLf & _
"Question 4 Response: " & finalanswer4 & vbCrLf & vbCrLf & _
"Comments: " & comments, vbYesNo, "Are you sure?")

If areyousure = 6 Then
previous_submission = DCount("ticketnumber", "surveyresponse", "ticketnumber = '" & [ticketnumber] & "'")
MsgBox "ticket number: " & ticketnumber 'just for testing
MsgBox "previous_submission: " & previous_submission 'just for testing

Select Case previous_submissiion
Case Is = 1
MsgBox "There is already a survey submitted for this ticket. Thank you for your submission", _
vbCritical, "You have already submitted once"
Case Is = 0
MsgBox "process complete"
'CurrentDb.Execute "insert into SurveyResponse(ticketnumber,fullname,q1answer,q2answer," & _
"q3answer,q4answer, comments) " & _
"values('" & [ticketnumber] & "', '" & [fullname] & "', '" & _
[finalanswer] & "', '" & [finalanswer2] & "', '" & [finalanswer3] & "', '" & _
[finalanswer4] & "', '" & [comments] & "')"
End Select

ElseIf areyousure = 7 Then
Exit Sub
End If
End Sub



any help is appreciated.

16-Oct-09: Edited by geekgirlau. Reason: insert line breaks

[geekgirlau]

Have you checked the value of previous_submission? I think you'll find it should be

previous_submission = DCount("[ticketnumber]", "surveyresponse", "ticketnumber = '" & [ticketnumber] & "'")

[mpearce]

Have you checked the value of previous_submission? I think you'll find it should be

previous_submission = DCount("[ticketnumber]", "surveyresponse", "ticketnumber = '" & [ticketnumber] & "'")

i figured it out i should have looked at the spelling more carefully. the dcount statement was functioning correctly. but i was referring to previous_submissiion in the line that reads select case instead of previous_submission lol.

[geekgirlau]

It's a good idea to always have Option Explicit turned on for this very reason - it's all too easy to miss a typing error and spend hours searching for the problem.

[mpearce]

so i got the survey fully working with with an input form and all. I split the database using the database splitter. Then I find out that the company will no longer be using access in the next few months. Basically meaning the survey referenced in this post useless.

So what i really could use is some guidance and assistance in making this application lighter most likely it will have to be converted to a web app.

3 main things need to happen

1. user needs to fill in the form

-creating the form layout pretty much makes sense to me
2. user submits form

-currently when the user clicks submit the controls on the form get set to variables and then a query commits the record to a table.
-there is logic in place so that the user is alerted of any blank fields or if they try to submit twice.
-what i dont really understand is how to convert VBA code to a web language
3. help desk team queries the records in the table in order to gain insight into users opinions

so in summary the layout of the form makes sense. i just am a little confused on how to link a web front end to a back end database as well as having select queries and insert queries executed.

Take a look at the attached file and give me some pointers on this.

Thanks