Results 1 to 6 of 6
  1. #1
    oohzzur is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4

    Unhappy Run time Error "the value you entered isn't valid for this field" (80020009)

    I am new on this forum and to Access VBA. I would appreciate assistance in fixing this a bug that reads "Run time Error "the value you entered isn't valid for this field" (80020009) ".
    I am writing an online exam program code that retrieves questions and options from a table and this information is displayed in text boxes on a form from which the user can select an option. I have used a recordset object for this purpose which works perfectly. I also have an unbound textbox on the form that displays the option selected by the user and want the value in the textbox attached to each record in the recordset. i was able to do this successfully but the challenge is that anytime i navigate to the beginning of file using a "previous" button on the form, I get the Run time Error "the value you entered isn't valid for this field" (80020009). i have a conditional statement that checks for BOF, but the error still persists. Below is the code i have written and the line generating the record is indicated in bold print. Any help in fixing this will be highly appreciated.

    Private Sub Form_Load()


    Set rst = CurrentDb.OpenRecordset("select id, question, A, B, C, D, E, SECT, response FROM TABLE2 WHERE TAG = TRUE ", dbOpenDynaset)
    Me.Text13 = rst!question
    Me.Text20 = rst!A
    Me.Text22 = rst!B
    Me.Text26 = rst!C
    Me.Text28 = rst!D


    Me.Text30 = rst!E


    End Sub





    Private Sub Command37_Click()


    If Not rst.EOF Then
    rst.Edit
    rst!response = Me.Text38
    rst.Update




    rst.MoveNext
    refreshpg




    Command15.BackColor = vbWhite
    Command16.BackColor = vbWhite
    Command17.BackColor = vbWhite
    Command18.BackColor = vbWhite
    Command19.BackColor = vbWhite
    Me.Text38 = ""
    Command37.Enabled = True
    Else
    Command37.Enabled = False

    End If
    End Sub

    Private Sub Command36_Click()
    If Not rst.BOF Then
    Command15.BackColor = vbWhite
    Command16.BackColor = vbWhite
    Command17.BackColor = vbWhite
    Command18.BackColor = vbWhite
    Command19.BackColor = vbWhite
    Me.Text38 = ""
    rst.MovePrevious
    Me.Text38 = rst!response
    chosenoption
    refreshpg
    Else
    Command36.Enabled = False
    End If


    End Sub

    Private Sub refreshpg()


    If Not rst.BOF And Not rst.EOF Then
    Me.Text13 = rst!question
    Me.Text20 = rst!A
    Me.Text22 = rst!B
    Me.Text26 = rst!C
    Me.Text28 = rst!D
    Me.Text30 = rst!E
    Me.Text40 = rst!id
    End If
    End Sub

    Private Sub chosenoption()


    If Me.Text38 = "A" Then
    Me.Command15.BackColor = 1000
    End If




    If Me.Text38 = "B" Then
    Me.Command16.BackColor = 1000
    End If




    If Me.Text38 = "C" Then
    Me.Command17.BackColor = 1000
    End If




    If Me.Text38 = "D" Then
    Me.Command18.BackColor = 1000
    End If




    If Me.Text38 = "E" Then
    Me.Command19.BackColor = 1000
    End If




    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't load fields via code.
    the form connects to a query. The fields load automatically.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum!

    I've looked at you code; it is hard to understand because of the object names. I also question how you are able to use a recordset over several subroutines when the record set "rst" is created in the form load event.

    Any chance you could/would post your dB for analysis?
    I think you have the check for BOF in the wrong place, but would need your dB to make sure.

  4. #4
    oohzzur is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4
    Thanks for your response Steve.
    on the issue of the record set creation, I declared the recordset object "rst" just after the line "Option Compare Database" hence it's availability to all subroutines on the module.

    secondly, please find below samples of the information contained in my table:

    ID question A B C D E IMAGE EXPLANATION ANSWER Field2 SUB tag sect Response flag tick
    1 WHAT IS THE MAKE OF MY FIRST CAR HONDA TOYOTA MITSUBISHI PEUGEOT NISSAN I BOUGHT THE HONDA ALA FROM KUNLE SHOYINKA A PERSONAL MATHS FALSE geo C FALSE 1



    Please let me know if you are still experiencing difficulty understanding the code.

    Looking forward to you assitance.

    Thank you.
    Attached Thumbnails Attached Thumbnails sample table.png  

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Tag" is a reserved word, so I changed it to "SelectQuestion".

    Look at the attached dB. I changed a lot of the code. I also added a bound form - much less code.

    See if this helps..........
    Attached Files Attached Files

  6. #6
    oohzzur is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4
    Thanks very much steve.

    Your codes were very helpful and the bug has been fixed.
    I guess its my inexperience that is on display.

    Your codes were much shorter and does the job perfectly.

    I am sure I'll be needing more of your assistance in the course of my journey in the world of access programming.

    Hope I wouldn't be out of line if I request for your personal email, so I can mail you directly.

    Thanks a lot. I really appreciate.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 07-29-2016, 10:11 PM
  2. Replies: 4
    Last Post: 03-23-2016, 07:26 PM
  3. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  4. Replies: 1
    Last Post: 04-30-2012, 10:09 AM
  5. Replies: 8
    Last Post: 11-12-2010, 10:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums