New Project - But Invalid use of Null doing me in (1 Viewer)

[murray83]

You'd think with what i have been doing i would be able to fix this

but nope, have looked on stack overflow, which popped up on google search

so quick overview of issue, a user/player puts in a name if its not in the list then that is incorrect and gets the user 100 points, but it breaks down, it does put the 100 in team 1 ( as so far just coded it in to frmRound1a for Team 1.

I believe the issue is something to do with it trying to hide some of the lines for the column

some help little or small be grateful cheers

 

[June7]

I am not familiar with this game. What is the point of NoPoint - triva knowledge?

Why 4 tables for teams instead of 1 table with a field for TeamID?

 

[murray83]

the game is a TV game show, and the point is to get the most obsucre hence pointless ( in my case thogh NoPoint ) so lower the score the better

 

[theDBguy]

Hi. Have you tried simply using the Nz() function? Just curious...

 

[June7]

Val() errors on Null as do all conversion functions. So use Nz() or no conversion function at all.

Nz(Team1Score.Value, 0)

 

[murray83]

Hi. Have you tried simply using the Nz() function? Just curious...

this is code to check for the score which has a null check

'---check for value of result
If Not IsNull(DLookup("Answers", "tblQuestionChoices", "Answers = Team1Guess1.value ")) Then
        Team1Score.Value = DLookup("Score", "tblQuestionChoices", "Answers = Team1Guess1.value ")
        Else
        'sql below here for score input if wrong
        MsgBox "That's Incorrect, and scores you 100", vbOKOnly, "To Bad"
        DoCmd.RunSQL ("Update tblTeam1 set [Player1Score] = 100 where [Player1]= Team1P1.value ") 'adds the score for the 1st palyers guess in the team
End If

but is then followed by more code like this

'---Adds tick to say been used and allow removal
DoCmd.RunSQL ("Update tblQuestionChoices set [Said] = 1 where [Answers]= Team1Guess1.value ") 'adds a tick to the one said so cant be said again

'---check if that answer has allready been given and remove it but keep in an archive table
DoCmd.OpenQuery "qryChoiceUsed"             'adds the used choice to the question to archive table
DoCmd.OpenQuery "qryChoiceUsed_Remove"      'removes the used choice from main table so cant be used again

If Val(Team1Score.Value) = 100 Then
End If

If Val(Team1Score.Value) = 99 Then
Me.Line0.Visible = False
End If

If Val(Team1Score.Value) = 98 Then
Me.Line0.Visible = False
Me.Line1.Visible = False
End If

If Val(Team1Score.Value) = 97 Then
Me.Line0.Visible = False
Me.Line1.Visible = False
Me.Line2.Visible = False
End If

If Val(Team1Score.Value) = 96 Then
Me.Line0.Visible = False
Me.Line1.Visible = False
Me.Line2.Visible = False
Me.Line3.Visible = False

etc

until you get to 0/zero

If Val(Team1Score.Value) = 0 Then
Me.Line0.Visible = False
Me.Line1.Visible = False
Me.Line2.Visible = False
Me.Line3.Visible = False
Me.Line4.Visible = False
Me.Line5.Visible = False
Me.Line6.Visible = False
Me.Line7.Visible = False
Me.Line8.Visible = False
Me.Line9.Visible = False
Me.Line10.Visible = False
Me.Line11.Visible = False
Me.Line12.Visible = False
Me.Line13.Visible = False
Me.Line14.Visible = False
Me.Line15.Visible = False
Me.Line16.Visible = False
Me.Line17.Visible = False
Me.Line18.Visible = False
Me.Line19.Visible = False
Me.Line20.Visible = False
Me.Line21.Visible = False
Me.Line22.Visible = False
Me.Line23.Visible = False
Me.Line24.Visible = False
Me.Line25.Visible = False
Me.Line26.Visible = False
Me.Line27.Visible = False
Me.Line28.Visible = False
Me.Line29.Visible = False
Me.Line30.Visible = False
Me.Line31.Visible = False
Me.Line32.Visible = False
Me.Line33.Visible = False
Me.Line34.Visible = False
Me.Line35.Visible = False
Me.Line36.Visible = False
Me.Line37.Visible = False
Me.Line38.Visible = False
Me.Line39.Visible = False
Me.Line40.Visible = False
Me.Line41.Visible = False
Me.Line42.Visible = False
Me.Line43.Visible = False
Me.Line44.Visible = False
Me.Line45.Visible = False
Me.Line46.Visible = False
Me.Line47.Visible = False
Me.Line48.Visible = False
Me.Line49.Visible = False
Me.Line50.Visible = False
Me.Line51.Visible = False
Me.Line52.Visible = False
Me.Line53.Visible = False
Me.Line54.Visible = False
Me.Line55.Visible = False
Me.Line56.Visible = False
Me.Line57.Visible = False
Me.Line58.Visible = False
Me.Line59.Visible = False
Me.Line60.Visible = False
Me.Line61.Visible = False
Me.Line62.Visible = False
Me.Line63.Visible = False
Me.Line64.Visible = False
Me.Line65.Visible = False
Me.Line66.Visible = False
Me.Line67.Visible = False
Me.Line68.Visible = False
Me.Line69.Visible = False
Me.Line70.Visible = False
Me.Line71.Visible = False
Me.Line72.Visible = False
Me.Line73.Visible = False
Me.Line74.Visible = False
Me.Line75.Visible = False
Me.Line76.Visible = False
Me.Line77.Visible = False
Me.Line78.Visible = False
Me.Line79.Visible = False
Me.Line80.Visible = False
Me.Line81.Visible = False
Me.Line82.Visible = False
Me.Line83.Visible = False
Me.Line84.Visible = False
Me.Line85.Visible = False
Me.Line86.Visible = False
Me.Line87.Visible = False
Me.Line88.Visible = False
Me.Line89.Visible = False
Me.Line90.Visible = False
Me.Line91.Visible = False
Me.Line92.Visible = False
Me.Line93.Visible = False
Me.Line94.Visible = False
Me.Line95.Visible = False
Me.Line96.Visible = False
Me.Line97.Visible = False
Me.Line98.Visible = False
Me.Line99.Visible = False
MsgBox "That Scores You NoPoint, Well Done", vbOKOnly, "Well Done"
End If

Me.Refresh
    
'shows the score after lines have been removed
Team1GrandScore.Visible = True

'sql below here for score input
DoCmd.RunSQL ("Update tblTeam1 set [Player1Score] = Team1Score.Value where [Player1]= Team1P1.value ") 'adds the score for the 1st palyers guess in the team


Me.Team1Guess1Score.Requery
Me.Team1GrandScore.Requery

'clear guess out for next team
Me.Team1Guess1.Value = ""

'set focus so can hide shit
Me.txtTeam1Focus.SetFocus

'hides button and text entry
Me.Team1Answer1.Visible = False
Me.Team1Guess1.Visible = False

'show team 2 stuff
Team2Answer1.Visible = True
Team2Guess1.Visible = True

 

[MajP]

FYI,
All of that code can simply be done

 Dim Score As Integer
  score = nz(teamscore1.value,-1)
  If Not Score = -1 Then
    For i = 0 To 99 - Score
      me.controls("line" & i ).visible = false
    Next i
  End If

just to save you a few hundred lines of code.

 

[murray83]

FYI,
All of that code can simply be done

 Dim Score As Integer
  score = nz(teamscore1.value,-1)
  If Not Score = -1 Then
    For i = 0 To 99 - Score
      me.controls("line" & i ).visible = false
    Next i
  End If

just to save you a few hundred lines of code.

cheers on top of that i guess i could also have one which is not null, for the answers in the actual table and then wouldn't need lines of code for that as well ??

 

[murray83]

have tried that but not with much success

as you can see if you check it out, file attached

it puts the 100 in but then faults and points to the new bit of code, maybe got it wrong way round

 

[June7]

If you want to use MajP's suggested code then it should replace yours, not just be added into the procedure.

Fix the textbox name from teamscore1 to Team1Score. Also have not fixed the Val() error.

Score = Nz(Team1Score.Value, -1)
If Not Score = -1 Then
    For i = 0 To 99 - Score
        Me.Controls("line" & i).Visible = False
    Next i
End If

If Nz(Team1Score.Value, 0) = 0 Then
     MsgBox "That Scores You NoPoint, Well Done", vbOKOnly, "Well Done"
End If

Me.Refresh

Here is another reduction in lines of code:

Public Sub Restore()
'restores count bar after each go just like in the show
Dim x As Integer
For x = 0 To 99
    Me("Line" & x).Visible = True
Next
End Sub