Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    5

    Unanswered: The value you entered isnít valid for this field

    Iím working with Access 2000 and VB 6.0 to create a simple database to track vendors and their information, i.e. Vendor Id, Address, Phone. To populate the Vendor table, Iím linking to an excel spreadsheet with just the Vendor Id and Vendor Description. I created a form with a combo box showing all available Vendors and textboxes to fill in additional information for each vendor (address, phone). The problem is when I click on the combo box to select a vendor, I keep getting the same error: Run-time errorí Ė2147352567 (80020009)í: The value you entered isnít valid for this field. All except the one field are Text and all except the primary key are not required and accept Null. Following is a piece of the code I used to fill in the textboxes on my form. The bold portion is where the error occurs.

    Public Sub cboVendor_AfterUpdate()

    Dim strSQL As String

    strSQL = "SELECT * FROM Vendor WHERE Vendor_ID = " & Me.cboVendor.Column(0) & ";"

    Set cn = New ADODB.Connection
    Set rst2 = New ADODB.Recordset

    If cn.State <> adStateOpen Then
    MsgBox ("Connection Failed")
    cn.Close
    Exit Sub
    End If

    rst2.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

    Me.txtAddr1 = rst2!Address1
    Me.txtAddr2 = rst2!Address2
    Me.txtCity = rst2!City
    Me.txtState = rst2!State
    Me.txtZip = rst2!ZipCode
    Me.txtPhone = rst2!Vendor_Phone
    Me.txtFax = rst2!Fax
    Me.txtWebsite = rst2!Website
    Me.txtVendorID = rst2!Vendor_ID

    cmdAccept.Enabled = True

    cn.Close
    Set cn = Nothing
    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't see no bold!
    P.S. this should really be posted in the Access topic - you'll know next time
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    5

    Sorry

    I apologize, I forgot to bold. The line is as follows:

    Me.txtAddr1 = rst2!Address1

  4. #4
    Join Date
    Aug 2007
    Posts
    5

    The value you entered isnít valid for this field

    Iím working with Access 2000 and VB 6.0 to create a simple database to track vendors and their information, i.e. Vendor Id, Address, Phone. To populate the Vendor table, Iím linking to an excel spreadsheet with just the Vendor Id and Vendor Description. I created a form with a combo box showing all available Vendors and textboxes to fill in additional information for each vendor (address, phone). The problem is when I click on the combo box to select a vendor, I keep getting the same error: Run-time errorí Ė2147352567 (80020009)í: The value you entered isnít valid for this field. All except the one field are Text and all except the primary key are not required and accept Null. Following is a piece of the code I used to fill in the textboxes on my form. The bold portion is where the error occurs.

    Public Sub cboVendor_AfterUpdate()

    Dim strSQL As String

    strSQL = "SELECT * FROM Vendor WHERE Vendor_ID = " & Me.cboVendor.Column(0) & ";"

    Set cn = New ADODB.Connection
    Set rst2 = New ADODB.Recordset

    If cn.State <> adStateOpen Then
    MsgBox ("Connection Failed")
    cn.Close
    Exit Sub
    End If

    rst2.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

    Me.txtAddr1 = rst2!Address1
    Me.txtAddr2 = rst2!Address2
    Me.txtCity = rst2!City
    Me.txtState = rst2!State
    Me.txtZip = rst2!ZipCode
    Me.txtPhone = rst2!Vendor_Phone
    Me.txtFax = rst2!Fax
    Me.txtWebsite = rst2!Website
    Me.txtVendorID = rst2!Vendor_ID

    cmdAccept.Enabled = True

    cn.Close
    Set cn = Nothing
    End Sub

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is the datatype and length of txtAddr1?
    Also, have you tried..?
    Code:
    Me.txtAddr1.Value = rst2!Address1
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    I don't see no bold!
    P.S. this should really be posted in the Access topic - you'll know next time
    if its using VB rather than VBA then its perfectly acceptable to be here, and ont he face of it its its.. its actually a misnomer you are using VB 6.0 and JET (the actual data component.. probably Jet 3.5, Access is a front end tool not a database in its own right)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I don't see where you set adCmdText anywhere. Have you done a breakpoint on the line that is giving you the error so you can see what is in rst2!Address1? I also don't see any checking as to if you got a record or not in rst2. You should do a While Not rst2.EOF to make sure you have a current record.

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Access has an annoying habit of actually hi-liting the line after the line that is really at fault, so I expect that the problem is with

    rst2.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, healdem is right - I did just assume that this was VBA in Access after reading it. It should have really read
    Quote Originally Posted by georgev
    P.S. should this be posted in the Access Topic? IS this piece of code Access (VBA) or VB?
    George
    Home | Blog

  10. #10
    Join Date
    Aug 2007
    Posts
    5
    the datatype for the address1 field is text and the length is 100.

    Yes, I did try .Value, but I still get the same error.

  11. #11
    Join Date
    Aug 2007
    Posts
    5
    This is the situation:

    I have test data in the table and I have data that was taken from linked Excel spreadsheet. I do not get an error when I choose one of the vendors that I created. I only get it when I choose one of the vendors that was appended to the table.

    I put in the Do While Not rst2.EOF and saw that with test data, it entered the loop, but when I choose the appended data, it did not enter the loop at all...thus telling me that it does not see those records. However, when I look at the table...the records are there. Any ideas?

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to provide some sample data?
    A handful that work and a handful that don't?
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    try
    Code:
    Me.txtAddr2 = rst2!Address2 & ""
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •