I have a complex setup to to a relatively simple thing:
I have a form that lists 10 questions in a text box, I have another box with
the answer. I have a combo that lists the 10 possible answers. The user just
selects the answer, and the recordset advances to the next question.
Along the way, a score is kept via the
If (.answer_box = -1) Then
' Adds 1 to Tempscorebox
.Tempscore = .Tempscore + 1
after the 10th question, the answer data is stored in another table, via the
New Test button.
The reason for the 'make table' is because I need the list of top 10 to be
static, until the user decides to request another new top 10 (via a requery),
allowing the user to ‘requery’ the underlying query from within the form to
create a new list from the underlying query.
The important thing is that the top 10 recordset is a 'random' list taken
from a table feeder list of 500 records.
If I ran it directly from the query bypassing the Feeder table, the
accompying ‘answer combo box’ would be out of sync with the question
recordset, as when the form opens the combo box will requery the underlying
table, and throw up a different set of 10 answers, hence the need to place
the recordset in a ‘fixed’ feeder table. Guarenteeing that the ‘Address box’
and ‘answer combo’ reads from the same queryset
another consideration was the ‘answer combo’ has to mix up the list
otherwise the offered answers exactly mirror the questions recordset, which .
Unless you know of a way to make ‘control Source list’ appear random in the
combo box from a table.
I will look at your other suggestions for the combo and testing for last
record, I’m sure they will be a better solution and may take me closer to
resolving some of the issues described above.
To summarise.
I want a user to be able to run a 10 question form from a master list of
hundreds of questions. Be bale to have a running score, and ultimately
deliver the results to another table for other analsys, such as progress
charts, etc.
These tests are ongoing over a very long period of time, and new questions
are added over the course of months.
I am not an expreienced databaser/programmer, but not green either. I’m not
sure where I would rank myself, but I am usually determined enough to find
the solutions. And with the help/advice of people such as yourselves, have
learned a fair bit along the way.
Regards
Eric
Marshall Barton said:
Well that seems to work, but what will you do when you
decide that you need another question?
Better to test for the last record instead of the 10th
record:
If Me.CurrentRecord = Me.Recordset.RecordCount _
Then Me.Recordset.MoveFirst
I have no idea what/why you are opening those queries and
requerying everything, but if it works I can't argue with
it.
The code for the combo's AfterUpdate can be simplified, but
don't use this unless you understand why it does the same
thing.
Private Sub Combo_Answer_A_AfterUpdate()
With CodeContextObject
.answer_box = (.Combo_Answer_A = .Run_Point_Address_A)
.Tempscore = .Tempscore - .answer_box
If Me.CurrentRecord < Me.Recordset.RecordCount _
Then Me.Recordset.MoveNext
End With
End Sub
Your use of CodeContextObject seems inconsistent with the
use of Me. Shouldn't they be the same thing?
--
Marsh
MVP [MS Access]
I have fixed the 3rd problem now by inserting the following line into my
requery button.
If Me.CurrentRecord = 10 Then Me.Recordset.MoveFirst
heres the complete code just in case there is a better way of doing it.
Private Sub Command16_Click()
If Me.CurrentRecord = 10 Then Me.Recordset.MoveFirst
'checks for last record, if so, then avoids the (for new table)Me.requery
'which avoids requerying an empty deleted table
stDocName = "QRY_Delete_PointsTest_C"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings False 'disable warnings
DoCmd.OpenQuery "QRY_Append_PointsTest_C"
DoCmd.SetWarnings True 'enable warnings
Me.Requery
DoCmd.Requery "Combo_Answer_C"
[Combo_Answer_C] = "?"
'DoCmd.OpenQuery "QX_Score_Points_Sub_C"
End Sub
Phew, thanks for all your help. much appriecated.
:
I spoke too soon... and I think this one is going to be tough to resolve…
The form works ok, until a user completes the last record answer combo box
(Combo_Answer_A).
What seems to happen is the
If Me.CurrentRecord < Me.Recordset.RecordCount _
Then Me.Recordset.MoveNext
In the CODE FOR Combo_Answer_A conflicts with the code for the ‘New Test’
button, where;
DoCmd.Requery "Combo_Answer_A"
Throws up on an empty recordset because the
If Me.CurrentRecord < Me.Recordset.RecordCount _
Then Me.Recordset.MoveNext
Wants to take precedence over the CODE FOR New Test button
I hope this makes sense (though I may be completely wrong in my
assumptions), and that you can help me resolve this as it is important that
users can focus on the questions, instead of error messages.
CODE FOR Combo_Answer_A
Private Sub Combo_Answer_A_AfterUpdate()
With CodeContextObject
If (.Combo_Answer_A = .Run_Point_Address_A) Then
.answer_box = -1
End If
If (.Combo_Answer_A <> .Run_Point_Address_A) Then
.answer_box = 0
End If
If (.answer_box = -1) Then
' Adds 1 to Tempscorebox
.Tempscore = .Tempscore + 1
End If
If Me.CurrentRecord < Me.Recordset.RecordCount _
Then Me.Recordset.MoveNext
End With
End Sub
CODE FOR New Test button
Private Sub Command14_Click()
stDocName = "QRY_Delete_PointsTest_A"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings False 'disable warnings
DoCmd.OpenQuery "QRY_Append_PointsTest_A"
DoCmd.SetWarnings True 'enable warnings
Me.Requery
DoCmd.Requery "Combo_Answer_A"
[Combo_Answer_A] = "?"
'DoCmd.OpenQuery "QX_Score_Points_Sub_A"
End Sub
:
I have no idea how to do it in a macro, but the logic is to
check the form's recordset's RecordCount property against
the form's CurrentRecord property.
In VBA it would be:
If Me.CurrentRecord < Me.Recordset.RecordCount _
Then Me.Recordset.MoveNext
efandango wrote:
Now that the error:
No current record. (Error 3021)
You can’t go to the specified record
hs been resolved, it throws another error:
'You can't go to the specified record'
'You may be at the end of a recordset'
I realise that the form is at the end of a 'recordset', as it is intended.
How can I supress or deal with this error message pop-up in a more user
friendly way?
