Windows 10 
Access 2007
Hi. Here I am again. I'm a teacher (definitely a noob coder!) building an English as a second language app where my students listen to the English sentence, then choose the correct words from the combo boxes and translation from the listbox, the app checks if they are correct, then gives them points for candy. I have a simple space-repetition counter, "LevelCounter" that adds a point level every time they get the sentence correct (I'm not using specific dates because this is more flexible - they can then use it daily or weekly and it's simply spaced on use). So every sentences starts on "0", then if they get it correct, moves up a level to "1" (so they get it next use), then if they get it correct it moves to "2" (so they get it in two uses time), then 3, then 4, and so on. If they get the sentence wrong, it moves down a sentence level. On saving each sentence, that new level is copied across to a TimesUntilNextUseCounter, which counts down 1 every time the NewDay button is pressed. They don't get that sentence again until that counter hits zero. Thus the sentences are (hopefully) slowly spaced wider between views.
In order to make them independent, so that I don't have to manually move that TimesUntilNewUseCounter down a number each use, I've simply given them a button on the form, which only allows them to press it once a day (using a check box on their daily records table), that basically decreases every sentence by 1 where TimesUntilUseCounter is > 0.
But I'm getting a weird result from my table on my query. There should be 12 results but my query seems to only be retrieving one result (on the picture of the Sentences Table I've highlighted the record it finds), and that result has a TimesUntilUseCounter of 0, and thus shouldn't even be there in the results. I've wracked my brain, but it seems nonsensical to me. I've attached some pictures of the appropriate part of the data table, and the result from stepping through the code where you can see that I only get one record. What the heck am I doing wrong?? And here is my sub:
Thank you in advance. :-)
Private Sub BtnNewDay_Click()
Dim PrepDaterst As DAO.Recordset 'student records
Dim PrepDatedb As DAO.Database
Dim Chrst As DAO.Recordset 'sentences
Dim Chdb As DAO.Database
Dim iCount As Integer
Set PrepDatedb = CurrentDb
Set PrepDaterst = CurrentDb.OpenRecordset("select * from StudentRecords where ScoreDate = Date()")
'Only allow user to press this button once a day.
If PrepDaterst.RecordCount <> 0 Then
If PrepDaterst!NewPrepare = True Then
MsgBox ("1 time only!")
Me.BtnPlay.SetFocus
Me.BtnNewDay.Enabled = False
Exit Sub
End If
'If this is the first press this day, then find all released sentences that have not been seen today, have a TimesUntilNextUseCounter of more than zero, and subtract 1.
If PrepDaterst!NewPrepare = False Then
Set Chdb = CurrentDb
'Set Chrst = CurrentDb.OpenRecordset("select [Sentences.Released],[Sentences.TimesUntilNextUseCounter],[Sentences.LastAttemptDate] from Sentences where TimesUntilNextUseCounter > 0")
Set Chrst = CurrentDb.OpenRecordset("select * from Sentences where TimesUntilNextUseCounter > 0")
If Chrst.RecordCount <> 0 Then
iCount = 0
'Set Chrst = Me.RecordsetClone
Chrst.MoveLast
Chrst.MoveFirst
Do While Not Chrst.EOF
iCount = iCount + 1
Chrst.Edit
If Chrst!Sentences!Released = True And Chrst!Sentences!LastAttemptDate <> Date And Chrst!Sentences!TimesUntilNextUseCounter > 0 Then
Chrst!TimesUntilNextUseCounter = Chrst!TimesUntilNextUseCounter - 1
End If
Chrst.Update
Chrst.MoveNext
Loop
Me.BtnNewDay.Enabled = False
PrepDaterst!NewPrepare = True
PrepDaterst.Update
Chrst.Close
Set Chrst = Nothing
Set Chdb = Nothing
End If
End If
End If
PrepDaterst.Close
Set PrepDaterst = Nothing
Set PrepDatedb = Nothing
End Sub
Reply With Quote
