Listbox Additem in access 2000

[lee123]

I did an example of a test in access 2003 to load some questions from a table but i want to do it in access 2000 but the way it is done in access 2003 is like this:

Private Sub LoadQuestion()
    Dim NextQuestionID As Long
    QuestionText = ""
    ClearAnswerList
    NextQuestionID = Nz(DMin("QuestionID", "QuestionT", "QuestionID > " & QuestionID))
    If NextQuestionID = 0 Then
        QuestionText = "Test Is Complete"
    Exit Sub
    End If
    QuestionID = NextQuestionID
    QuestionText = DLookup("QuestionText", "QuestionT", "QuestionID=" & QuestionID)
    
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
    While Not rs.EOF
        AnswerList.AddItem (rs!AnswerID & ";" & rs!AnswerText)
        rs.MoveNext
    Wend

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing    
End Sub

but the problem is in access 2003 there is an "AddItem" and in access 2000 there isn't so how can i convert this code to use in access 2000? instead of access 2003. I cant figure it out

lee123

[ADezii]

Incorporate the following code into your own wherever appropriate, keeping in mind that this approach is for a limited number of Values that do not change.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lst As ListBox

Set lst = Me![AnswerList]

'Set up the Listbox correctly, if it isn't already
lst.RowSourceType = "Value List"
lst.ColumnCount = 2
lst.BoundColumn = 1
lst.ColumnWidths = "0 in;2 in"

Set db = CurrentDb
  
Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)

Do While Not rs.EOF
   strBuild = strBuild & rs!AnswerID & ";" & rs!AnswerText & ";"
     rs.MoveNext
Loop

lst.RowSource = Left$(strBuild, Len(strBuild) - 1)

[lee123]

well everthing is working but now i am getting an error from a button code that goes to the next question why is that.

Private Sub Command9_Click() ' this is to move to the next question
 
    Dim db As Database
    Dim rs As Recordset
    
    rs.AddNew
    rs!StudentID = StudentID
    rs!QuestionID = QuestionID
    rs!answerid = AnswerList
    rs.Update
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ResponseT")
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    
    LoadQuestion
    
End Sub

the error is pointing to the "rs.AddNew" and the error says "Object variable or with block variable not set".

well you know what here is the whole entire code so it will make sense to you

Private Sub Command8_Click()
If IsNull(StudentID) Then
    MsgBox "Must First Select A Name To Take The Test", , "Hello!"
    DoCmd.GoToControl "Studentid"
    StudentID.Dropdown
Exit Sub
End If
    StudentID.Enabled = False
    DoCmd.GoToControl "Answerlist"
    Command8.Enabled = False
    QuestionID = 0
    LoadQuestion
End Sub
Private Sub ClearAnswerList()
    Dim x
    For x = 0 To AnswerList.ListCount - 1
        AnswerList.ListIndex = ""
    Next x
End Sub
Private Sub LoadQuestion()
Dim NextQuestionID As Long
    QuestionText = ""
    ClearAnswerList
    NextQuestionID = Nz(DMin("QuestionID", "QuestionT", "QuestionID > " & QuestionID))
    If NextQuestionID = 0 Then
        QuestionText = "Test Is Complete"
    Exit Sub
    End If
    QuestionID = NextQuestionID
    QuestionText = DLookup("QuestionText", "QuestionT", "QuestionID=" & QuestionID)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lst As ListBox
  
Set lst = Me![AnswerList]
  
'Set up the Listbox correctly, if it isn't already
lst.RowSourceType = "Value List"
lst.ColumnCount = 2
lst.BoundColumn = 1
lst.ColumnWidths = "0 in;2 in"
  
Set db = CurrentDb
  
Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
  
Do While Not rs.EOF
   strBuild = strBuild & rs!answerid & ";" & rs!AnswerText & ";"
     rs.MoveNext
Loop
  
lst.RowSource = Left$(strBuild, Len(strBuild) - 1)


End Sub
Private Sub Command9_Click() ' this is to move to the next question
 
    Dim db As Database
    Dim rs As Recordset
    
    rs.AddNew
    rs!StudentID = StudentID
    rs!QuestionID = QuestionID
    rs!answerid = AnswerList
    rs.Update
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ResponseT")
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    
    LoadQuestion
    
End Sub

lee123

[ADezii]

1. rs is not defined and muse be recreated or Declared as a Form Level Variable and not previously closed.
2. Where are you pulling StudentID and QuestionID from, the List Box?

[lee123]

from the response table i have in the response table these are the fields:

ResponseID Autonumber
StudentID Number
QuestionID Number
AnswerID Number

like i said this is from an access 2003 database program i made sometime ago but since i dont have access 2003 anymore and have access 2000 pre i wanted to make it an excutable because i have the developers kit for this

there are three tables

Question Table
Response Table
Answer Table
Student Table

I did know how hard it would be to do this in Access 2000 but i thought i would ask to see if it can be done without changing the code alot But i see Access 2003 is totally Different in so many ways.

lee123

[ADezii]

Access 2003 is totally Different in so many ways.

Aside from the missing AddItem Method in Access2000, the rest of the code should have no difficulty executing. Some of the code does not make any sense to me, if you wish to Upload the Database, I'll take a closer look at it.

[lee123]

ok well i'll try to upload this ok

lee123

[ADezii]

Have the Attachment and will look it over in the next couple of days.

[ADezii]

I've created, at least what I feel, is a good starting point for you. Simply Download the Attachment, and should you have any questions, please feel free to ask. I do not think that it is the most efficient logic in this case, but I conformed to your code as much as possible.
P.S. - Keep in mind that this Algorithm requires that the Questions are numbered Sequentially ([QuestionID]) starting at 1 with no gaps in between. Break this conformity, and you'll run into problems. Open qryResponses to obtaing a better Viewpoint on the Results Table.

[lee123]

Thank You for fixing this i have tried it out and it cool...

lee123