473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Listbox Additem in access 2000

lee123
556 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub LoadQuestion()
  2.     Dim NextQuestionID As Long
  3.     QuestionText = ""
  4.     ClearAnswerList
  5.     NextQuestionID = Nz(DMin("QuestionID", "QuestionT", "QuestionID > " & QuestionID))
  6.     If NextQuestionID = 0 Then
  7.         QuestionText = "Test Is Complete"
  8.     Exit Sub
  9.     End If
  10.     QuestionID = NextQuestionID
  11.     QuestionText = DLookup("QuestionText", "QuestionT", "QuestionID=" & QuestionID)
  12.  
  13.     Dim db As Database
  14.     Dim rs As Recordset
  15.     Set db = CurrentDb
  16.  
  17.     Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
  18.     While Not rs.EOF
  19.         AnswerList.AddItem (rs!AnswerID & ";" & rs!AnswerText)
  20.         rs.MoveNext
  21.     Wend
  22.  
  23.     rs.Close
  24.     db.Close
  25.     Set rs = Nothing
  26.     Set db = Nothing    
  27. 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
Mar 22 '10 #1
9 6218
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Dim lst As ListBox
  4.  
  5. Set lst = Me![AnswerList]
  6.  
  7. 'Set up the Listbox correctly, if it isn't already
  8. lst.RowSourceType = "Value List"
  9. lst.ColumnCount = 2
  10. lst.BoundColumn = 1
  11. lst.ColumnWidths = "0 in;2 in"
  12.  
  13. Set db = CurrentDb
  14.  
  15. Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
  16.  
  17. Do While Not rs.EOF
  18.    strBuild = strBuild & rs!AnswerID & ";" & rs!AnswerText & ";"
  19.      rs.MoveNext
  20. Loop
  21.  
  22. lst.RowSource = Left$(strBuild, Len(strBuild) - 1)
Mar 22 '10 #2
lee123
556 512MB
well everthing is working but now i am getting an error from a button code that goes to the next question why is that.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click() ' this is to move to the next question
  2.  
  3.     Dim db As Database
  4.     Dim rs As Recordset
  5.  
  6.     rs.AddNew
  7.     rs!StudentID = StudentID
  8.     rs!QuestionID = QuestionID
  9.     rs!answerid = AnswerList
  10.     rs.Update
  11.  
  12.     Set db = CurrentDb
  13.     Set rs = db.OpenRecordset("ResponseT")
  14.     rs.Close
  15.     db.Close
  16.     Set rs = Nothing
  17.     Set db = Nothing
  18.  
  19.     LoadQuestion
  20.  
  21. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2. If IsNull(StudentID) Then
  3.     MsgBox "Must First Select A Name To Take The Test", , "Hello!"
  4.     DoCmd.GoToControl "Studentid"
  5.     StudentID.Dropdown
  6. Exit Sub
  7. End If
  8.     StudentID.Enabled = False
  9.     DoCmd.GoToControl "Answerlist"
  10.     Command8.Enabled = False
  11.     QuestionID = 0
  12.     LoadQuestion
  13. End Sub
  14. Private Sub ClearAnswerList()
  15.     Dim x
  16.     For x = 0 To AnswerList.ListCount - 1
  17.         AnswerList.ListIndex = ""
  18.     Next x
  19. End Sub
  20. Private Sub LoadQuestion()
  21. Dim NextQuestionID As Long
  22.     QuestionText = ""
  23.     ClearAnswerList
  24.     NextQuestionID = Nz(DMin("QuestionID", "QuestionT", "QuestionID > " & QuestionID))
  25.     If NextQuestionID = 0 Then
  26.         QuestionText = "Test Is Complete"
  27.     Exit Sub
  28.     End If
  29.     QuestionID = NextQuestionID
  30.     QuestionText = DLookup("QuestionText", "QuestionT", "QuestionID=" & QuestionID)
  31.  
  32. Dim db As DAO.Database
  33. Dim rs As DAO.Recordset
  34. Dim lst As ListBox
  35.  
  36. Set lst = Me![AnswerList]
  37.  
  38. 'Set up the Listbox correctly, if it isn't already
  39. lst.RowSourceType = "Value List"
  40. lst.ColumnCount = 2
  41. lst.BoundColumn = 1
  42. lst.ColumnWidths = "0 in;2 in"
  43.  
  44. Set db = CurrentDb
  45.  
  46. Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
  47.  
  48. Do While Not rs.EOF
  49.    strBuild = strBuild & rs!answerid & ";" & rs!AnswerText & ";"
  50.      rs.MoveNext
  51. Loop
  52.  
  53. lst.RowSource = Left$(strBuild, Len(strBuild) - 1)
  54.  
  55.  
  56. End Sub
  57. Private Sub Command9_Click() ' this is to move to the next question
  58.  
  59.     Dim db As Database
  60.     Dim rs As Recordset
  61.  
  62.     rs.AddNew
  63.     rs!StudentID = StudentID
  64.     rs!QuestionID = QuestionID
  65.     rs!answerid = AnswerList
  66.     rs.Update
  67.  
  68.     Set db = CurrentDb
  69.     Set rs = db.OpenRecordset("ResponseT")
  70.     rs.Close
  71.     db.Close
  72.     Set rs = Nothing
  73.     Set db = Nothing
  74.  
  75.     LoadQuestion
  76.  
  77. End Sub
lee123
Mar 22 '10 #3
ADezii
8,834 Expert 8TB
  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?
Mar 22 '10 #4
lee123
556 512MB
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
Mar 22 '10 #5
ADezii
8,834 Expert 8TB
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.
Mar 23 '10 #6
lee123
556 512MB
ok well i'll try to upload this ok

lee123
Attached Files
File Type: zip Test.zip (16.0 KB, 120 views)
Mar 23 '10 #7
ADezii
8,834 Expert 8TB
Have the Attachment and will look it over in the next couple of days.
Mar 23 '10 #8
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Test Questions.zip (27.5 KB, 177 views)
Mar 23 '10 #9
lee123
556 512MB
Thank You for fixing this i have tried it out and it cool...

lee123
Mar 23 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: R.Wieser | last post by:
Hello All, I'm trying to get a "Virtual Listbox" to work. I've currently got a form, and used CreateWindowExA to create a ListBox with the LBS_OWNERDRAWFIXED and LBS_NODATA flags on it. I've...
6
by: Kin®sole | last post by:
Hi I am trying to add two variables to a list box and keep everything in line.I get the result below Gaz £2000 Jimmy £3000 fred £4000 but would like to get this result
3
by: mibispam | last post by:
Hallo, kann mir vielleicht jemand erklären, wieso ich neue Einträge in eine Listbox nicht per MeineListbox.additem hinzufügen kann?? Ich bekomme da immer wieder einen Laufzeitfehler. In...
4
by: Alan Lane | last post by:
Hello world: I have a ListBox that I fill as a ValueList from a SQL query. The SQL looks like: SELECT Format(COST,"$#,000") As ItemCost FROM tblPricing ... I put semicolons between each...
1
by: andrew.panin | last post by:
Hi there! I've got a trouble with these things. What's going on? 1ST STEP: we have ListBox item. Let's call it ListBox1. We're adding four values to it using AddItem method:...
6
by: serviceman via AccessMonster.com | last post by:
Hi again gang... I have downloaded a great little list box select form from Stephen Lebans that i would like to use in my student email project. The "available" object is populated thus: Private...
1
by: Selesti | last post by:
I've created a form dialog box with several checkboxes to determine which offices to include in a sales report. However, I'm getting an "Object doesn't support this property or method" error from MS...
3
by: ML | last post by:
I have used Allen Brown's technique for filling a listbox on a form with the names of files in a certain disc folder. It works well. I am now giving the user the option to print the form...
1
by: Sunray | last post by:
I have a form called the sales form and i have 2 sets of listboxes So what happens is. i add items form the bottom set of list boxes which are bound to a data base to the top set of list boxes which...
2
risk32
by: risk32 | last post by:
Hello. I am having a problem with Excel VB. I'm trying to use the value of items in a listbox to manipulate a caption to a label. Any suggestions? I haven't tried to use index, and frankly I don't...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.