Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Data Type Conversion error

    I have a continous subform as shown
    PackID MagID InQty OutQty BalQty
    201 40 20 0 0
    202 40 0 2 18
    203 40 0 5 13
    203 40 0 4 9
    Here User can only enter or change the OutQty

    If some one were to change say row 3 OutQty to 15 from 5, then the subform will look like this (BalQty in 4th row may end up negative)
    PackID MagID In Qty OutQty BalQty
    201 40 20 0 0
    202 40 0 2 18
    203 40 0 15 3
    203 40 0 4 -1

    The control source for BalQty is DSum("Nz([InQty])-Nz([OutQty])","tblSubMag","([PartID]<=" & Nz([PartID],0) & ") And( [MagID]=" & Nz([MagID],0) & ")")

    My code for OutQty is

    Private Sub txtOutQty_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Query1", Dynaset)

    Do While Not rs.EOF
    If rs.Fields("BalQty").Value < 0 Then
    MsgBox " OutQty Error causing other records Bal Qty negative"
    End If
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    When run,I got a message Run time error 3421. Data type conversion error. Also I am not sure whether I should put the code in after update event.

    Someone please help.
    Thank you

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Which line of code is throwing the error?

    Type conversion errors are usually solved by using conversion functions like CLng(), CCur(), CDbl() and others.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    The line

    Set rs = db.OpenRecordset("Query1", Dynaset)

    Thanks

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Of course.

    What is "Dynaset"? Shouldn't that be like vbDynaset or something? Perhaps this is the source of the data type mismatch.

    Try getting rid of it: Set rs = db.OpenRecordset("Query1")
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2008
    Posts
    54
    Replacing dynaset with vbdynaset gives a compile error -variable not defined.
    Getting rid of of set rs= db.openrecordset("query1") causes a run time error "91" - object variable or with block variable not set.

  6. #6
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I believe he meant to remove "Dynaset", not remove the whole line.

    Replace: set rs = db.openrecordset("query1", dynaset)
    with: set rs = db.openrecordset("query1")

    Sam, hth
    Good, fast, cheap...Pick 2.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Access Help would indicate that dbOpenDynaset is the correct Constant for the Type argument of OpenRecordSet.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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