Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Please don't validate me !!!!

    Howdy,

    Windows XP, Acc 2003, Combo Box, unbound, format property is blank, rowsource is a single column of data (text, 50 characters, allow empty and not required in the table).

    The only events assigned is as follows:
    Code:
    Private Sub cboLot_Enter()
    
        If Len(Me.cboRoll & "") = 0 Then
            MsgBox "Enter the roll number first!", vbInformation + vbOKOnly, "Lot Number"
            DoCmd.GoToControl ("cboRoll")
        End If
    End Sub
    
    Private Sub cboLot_AfterUpdate()
    
        Me.txtData7 = Me.cboRoll
        Me.lblNewLot.Visible = False
        If Not IsNull(Me.cboRoll) And Not IsNull(Me.cboLot) Then
            If DCount("ThicknessKey", "tblThickness", "Lot = '" & Me.cboLot & "'") = 0 Then Me.lblNewLot.Visible = True
            Me.cmdGo.Enabled = True
            DoCmd.GoToControl ("cmdGo")
        Else
            Me.cmdGo.Enabled = False
        End If
    End Sub
    I do not have it set to validate, yet, for some reason it will occasionally reject a value that is alpha-numeric. It never chokes on a numeric value.

    An interesting side note: I am unable to reproduce it in my environment (although I am running it in Acc 97 in Windows 2000 and the sporadic problem occurs in Acc 2003 in Windows XP).

    I can not see anything here that would sendup a canned "Data format not valid" error from Access - especially on an occasional basis. Am I missing something here?


    tc
    Last edited by tcace; 11-03-05 at 00:09.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Additional Info:

    I can now recreate the error on my PC, but with a perplexing twist. I entered "aaa332" as my test data. It took the first time no problem.

    The second time through, I selected it from the combo list (I didn't even type it!) and the message came up "The text you entered isn't valid for this field". It would not let me select that value at all - or type it in.

    So, I canceled, exited and went back in and now ... I can no longer reproduce the error message .......

    What gives?

    tc

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Per your private message ... I'm contemplating ...

    At 1st blush I see nothing ... BUT, I need some more detail ... Column types, lengths, etc ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The error is when entering data into an unbound combo? How odd.

    Are you able to step through code? Sometimes in situations like this where I have no clue what is going on I set a handler for every relevent event I can think of (BeforeUpdate, AfterUpdate, Change etc) and pop a break point in each - see if I can narrow down to something more specific.

    Having said that, I can't really think of owt except a corruption issue though. Perhaps an old export objects to new db, decompile, compact etc routine might be in order if you get nowhere.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    - The combo itself is unbound (so is the parent form).

    - An event on a prior combo sets the RowSource for this combo and requerys it.

    - The RowSource is an SQL statement that selects a single field from a table where the index matches a parrticular value (supplied by the prior combo).

    - The field is a text field, with the following parameters of note:
    a) Required = No
    b) Allow Zero Length = Yes
    c) Type = Text
    d) Length = 50

    - Note that since the combo is unbound, the parameter of the rowsource field should be irrelevant.

    - The combo has the following parameters of note:
    a) Limit to List = False
    b) Validation Rule and Validation Text both empty
    c) Format empty
    d) Decimal Places = Auto

    - The only circumstance the code checks for is an empty string and to see if it exists already. In any case, the code is not writing any data to the table at this juncture.

    - I've tested this countless times in the last 48 hours and have only been able to reproduce the error once (which is especially odd since I was using the same text value every time). The one time it did error, control-break did not put me in code, which makes me think it wasn't running code when the error occured. It's almost as though Access is randomly turning the validation on.

    - I won't be able to test in on my client's computer until next week, where it appearently happens all the time (the client wants a fix emailed to him yesterday, of course).

    - Tonight, I will take pootle flump's advise and try moving selected objects into a new DB.
    Last edited by tcace; 11-03-05 at 12:58.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Is the combo in question a Value List? Post an example of your query for the rowsource of this combo ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    cboLot
    here's the property sheet:
    Name: cboLot
    Decimal Places: Auto
    Row Source Type: Table/Query
    Row Source:
    Code:
    SELECT [Lot] FROM tblThickness WHERE PartKey = 1 AND Roll = '1147' GROUP BY Lot ORDER BY Lot;
    Column Count: 1
    Column Heads: No
    Column Widths: 2"
    Bound Column: 1
    List Rows: 8
    List Width: 2"
    Limit To List: No
    Auto Expand: Yes
    IME Hold:No
    IME Mode: No Control
    IME Sentence Mode: Phrase Predict
    Visible: Yes
    Display When: Always
    Enabled: No (it becomes enabled after the user selects a part number)
    Locked: No
    Allow AutoCorrect: Yes
    Tab Stop: Yes
    (I'll omit the formatting properties, they are irrelevant)
    Help Contex ID: 0
    Tag: Con2 (I use this to let the code know it's paret of control group 2)
    After Update and On Enter events shown in first post
    Reading Order: Context
    Keyboard Language: System
    Scroll Bar Align: System
    Numeral Shapes: System
    In Hyperlink: No

    Any properties not shown (other than formatting things) are blank.

    The row source changes due to the event on the previous combo box, shown here:
    Code:
    Private Sub cboRoll_AfterUpdate()
    
        Me.txtData6 = Me.cboRoll
        Me.lblNewLot.Visible = False
        If Not IsNull(Me.cboRoll) And Not IsNull(Me.cboLot) Then Me.cmdGo.Enabled = True Else Me.cmdGo.Enabled = False
        If Len(Me.cboRoll & "") > 0 Then
            If NewRollOK(Me.cboRoll) Then
                Me.cboLot.RowSource = "SELECT [Lot] FROM tblThickness WHERE PartKey = " & Me.cboPart & " AND Roll = '" & Me.cboRoll & "' GROUP BY Lot ORDER BY Lot;"
                Me.cboLot = Null
                DoCmd.GoToControl ("cboLot")
            Else
                DoCmd.GoToControl ("cboRoll")
            End If
        End If
    End Sub
    Still perplexed .....

    tc

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DOH!!!! Um ... Where is your REQUERY after assigning the RowSource to the combobox???? THAT would net you the "Data not a valid format" error since Access does'nt know the underlying type for the column you're querying for ... Then of course, it could be you've fallen into one of those wierd cracks in Access
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Where is your REQUERY after assigning the RowSource to the combobox?
    Mike, I think you nailed it on the head - here's why: I got a bit lazy when I made this form. The very first combo box pulled the part ID, which uses an autonumber index. This combo is the third or fourth I put on the form, and I simply copy and pasted the control.

    Since I was setting the rowsource through code, I didn't bother with the original rowsource on the control (actually, it should be blank for performance reasons).

    So, apparently, a combo box can inherit the properties of the rowsource field, even after the rowsource changes. Adding the Requery would fix that, but even more to the point, I have cleared the unneeded rowsource property.

    I still don't understand the combination of events that would make it work sometimes and not others, but, either way, the cause has been found.

    Thanks!!!!

    tc

Posting Permissions

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