Solved IF with Record count (1 Viewer)

[murray83]

here is my code for starting my quiz, i have altered it as we had someone who shares a PC logon the SFM005.D002 so weren't able to take part when the other person had done so

so in my wisdom i changed the criteria and made it look for user name as well as PC login and quiz no, but this has also given raise to added side effect of allowing people to have multiple goes when i want to try and limit it to one go per question set

the issue i have now though is its throwing its code out the pram and giving me this message, see attached

below this code is the original code which hasnt been doctored by me

 Dim PlayerName As TempVars
  Dim QRoundNo As TempVars
 

    If Me.txtPCLogin.Value = "SFM005.D002" Then
    If Me.txt_UserName <> "" Then                                                       'checks wethere the persons name is in the table
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE Person = '" & txt_UserName & "' AND UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
    Else
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
    rs.Requery
    End If
        End If
            
            
            
    If rs.RecordCount < 1 Then
     TempVars!PlayerName = Me.txt_UserName.Value
     TempVars!QRoundNo = Me.QuestionRoundNo.Value
     Me.Visible = False
     DoCmd.OpenForm "frmQuestions", , , , , , Me.QuestionRoundNo
    Else
    MsgBox ("You Have Allready Taken Part")                                             'if they are says so and exits sub
    txt_UserName.Value = ""
    txt_UserName.SetFocus
    cmd_Start.Enabled = False
    Exit Sub
    End If

original code here

Private Sub cmd_Start_Click()


  Dim PlayerName As TempVars
  Dim QRoundNo As TempVars

   If Me.txt_UserName <> "" Then                                                       'checks wethere the persons name is in the table
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE Person = '" & txt_UserName & "' AND UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
    rs.Requery
    If rs.RecordCount < 1 Then
     TempVars!PlayerName = Me.txt_UserName.Value
     TempVars!QRoundNo = Me.QuestionRoundNo.Value
     Me.Visible = False
     DoCmd.OpenForm "frmQuestions", , , , , , Me.QuestionRoundNo
    Else
    MsgBox ("You Have Allready Taken Part")                                             'if they are says so and exits sub
    txt_UserName.Value = ""
    txt_UserName.SetFocus
    cmd_Start.Enabled = False
    Exit Sub
    End If
    End If

End Sub

 

[theDBguy]

Hi. One way to troubleshoot this is to assign the SQL statement to a variable, do a Debug.Print of it, and then copy and paste the result in the Query Designer. You should get some indications if there's anything wrong with it, so you can fix the code. Hope that helps...

 

[ebs17]

It's smart to indent code. Then you can read code and understand relationships:

    If Me.txtPCLogin.Value = "SFM005.D002" Then
        If Me.txt_UserName <> "" Then                 'checks wethere the persons name is in the table
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE Person = '" & txt_UserName & "' AND UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
        Else
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
            rs.Requery
        End If
    Else
        MsgBox "You haven't a recordset"
    End If
    
    If rs.RecordCount < 1 Then
        ' ...

If Me.txtPCLogin.Value = "SFM005.D002" Then ... what happens if false?

Eberhard

 

[murray83]

ok how do i debug print ?

and also say asign the SQL to variable do you mean dim it ?

 

[murray83]

Eberhard

If Me.txtPCLogin.Value = "SFM005.D002" Then ... what happens if false?

if its false it then just uses the second SQL rather then looking for name just looks for pc login and quiz no

 

[theDBguy]

ok how do i debug print ?

and also say asign the SQL to variable do you mean dim it ?

Hi. Yes. You could try something like:

Dim strSQL As String
strSQL = "SELECT blah blah blah..."
Debug.Print strSQL
...'use strSQL in your OpenRecordset

Debug.Print will show the result in the Immediate Window. You can then copy and paste it to the Query Designer and let Access give you a hint if there's anything wrong with it, like a syntax error, for example.

Hope that helps...

 

[Papa_Bear1]

theDBguy is spot on... I will often do a dim strSQL as String, and then set that variable to the string that defines the query. You would then have ...OpenRecordset(strSQL) instead. I would then pause the code at that point, use the "View Immediate" screen, and type ?strSQL in it - to grab the resulting string. Then I'll paste that into ta new temporary query to run it to see what happens --- can be easier to troubleshoot that way... (I just noticed that theDBguy mentioned very much the same approach - but with Debug.Print... All this time, I could avoided the whole pause thing, and didn't realize that it put that text in the Immediate window... a new tidbit!... thanks!)

 

[murray83]

i think i have fixed seems i had some of my code back to front, thanks for the help and i shall look into sql debug, but in this case i didnt need it but may in the future so thanks for helping and showing me something new

    If Me.txt_UserName <> "" Then                                                       'checks wethere the persons name is in the table
    If Me.txtPCLogin.Value = "SFM005.D002" Then
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE Person = '" & txt_UserName & "' AND UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
    Else
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLeaderBoard WHERE UserID = '" & txtPCLogin & "' AND QuizNo = '" & QuestionRoundNo & "' ")
    rs.Requery
    End If
        End If
            
    If rs.recordcount < 1 Then
     TempVars!PlayerName = Me.txt_UserName.Value
     TempVars!QRoundNo = Me.QuestionRoundNo.Value
     Me.Visible = False
     DoCmd.OpenForm "frmQuestions", , , , , , Me.QuestionRoundNo
    Else
    MsgBox ("You Have Allready Taken Part")                                             'if they are says so and exits sub
    txt_UserName.Value = ""
    txt_UserName.SetFocus
    cmd_Start.Enabled = False
    Exit Sub
    End If

just moved the top two lines around and bam it works

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.