Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2010
    Posts
    6

    Unanswered: Run time error 3219=== Plz Help Urgent

    Code:
    Private Sub cmdsummery_Click()
    
    Set wss = DBEngine.Workspaces(0)
    Set dbs = wss.OpenDatabase("Gagedetails.mdb")
    Set rss = dbs.OpenRecordset("tblGage")
    
    
    Do While Not rss.EOF
    
    OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
    Set rss = dbs.OpenRecordset(OSQL)
    MSQL = rss("OpeName1")
    NSQL = "INSERT INTO tblOpeName (name)" & " values (" & MSQL & ")"
    Set rss = dbs.OpenRecordset(NSQL)
    rss.MoveNext
    Loop
    
    rss.Close
    Set rss = Nothing
    End Sub
    When i tried to run this got a Error code "runtime error 3219"
    plz help

  2. #2
    Join Date
    Jul 2010
    Posts
    6

    Run time error 3219=== Plz Help Urgent

    Code:
    Private Sub cmdsummery_Click()
    
    Set wss = DBEngine.Workspaces(0)
    Set dbs = wss.OpenDatabase("Gagedetails.mdb")
    Set rss = dbs.OpenRecordset("tblGage")
    
    
    Do While Not rss.EOF
    
    OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
    Set rss = dbs.OpenRecordset(OSQL)
    MSQL = rss("OpeName1")
    NSQL = "INSERT INTO tblOpeName (name)" & " values (" & MSQL & ")"
    Set rss = dbs.OpenRecordset(NSQL)
    rss.MoveNext
    Loop
    
    rss.Close
    Set rss = Nothing
    End Sub
    When i tried to run this got a Error code "runtime error 3219 Invalid Operation"
    plz help

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where did the error occur
    what value(s) were on that line
    what have you done to try to work out what is wrong?

    is txtdate a date/time value
    what does the function sqldate do, is it returning a valid value
    what is the value of OSQL, does it look valid

    what is OpeName1
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this an Access problem (VBA) or a Visual Basic problem, bearing in mind that although VB & VBA are close relatives, they are not the same?

    also please don't post the same question in multiple threads
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2010
    Posts
    6
    Thanx for the reply, I am very new to VB and DB.

    Code:
    NSQL = "INSERT INTO tblOpeName (name)" & " values (" & MSQL & ")"
    Error occurs here.
    What i need to do is
    1. Copy Unique Operator name(No duplicates) (OpeName1) from tblGage
    2. Where Date equal to txtdate.text
    3. Insert them into another field called Name in tblOpeName

    Until this line prg works well.

    Code:
    OSQL = "SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
    Set rss = dbs.OpenRecordset(OSQL)
    MSQL = rss("OpeName1")


    sqldate funtion will return txtdate.text as #txtdate.text # where suitable to sql.


    OSQL vale looks like "Text"



    Plz help to solve this
    Thanx

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Threads merged.

  7. #7
    Join Date
    Jul 2010
    Posts
    6
    Quote Originally Posted by healdem View Post
    so is this an Access problem (VBA) or a Visual Basic problem, bearing in mind that although VB & VBA are close relatives, they are not the same?

    also please don't post the same question in multiple threads
    Hi healdem
    Sorry for the repeating posts that was a mistake.
    I am using VB6

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Although this is VB, your error is generated by Access DAO so we'll keep it in the Access forum.

    Code:
    NSQL = "INSERT INTO tblOpeName (name)" & " values ('" & MSQL & "')"
    Use ' for text, # for dates and nothing for numeric.

    Also, Date is a poor name for a column:
    List of reserved words in Access 2002 and in later versions of Access

  9. #9
    Join Date
    Jul 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    Although this is VB, your error is generated by Access DAO so we'll keep it in the Access forum.

    Code:
    NSQL = "INSERT INTO tblOpeName (name)" & " values ('" & MSQL & "')"
    Use ' for text, # for dates and nothing for numeric.

    Also, Date is a poor name for a column:
    List of reserved words in Access 2002 and in later versions of Access
    Thanx for reply.
    I changed the Column name date, and also used ' for MSQL as you said.
    Stil got same error

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looking more closely, there's lots wrong there. This is untested. If it fails please tell us:
    The exact error.
    The line that errors.
    The value printed to the Immediate Window.
    What data type the Date column is (I haven't changed the name since you haven't told us the new name).

    Code:
    Private Sub cmdsummery_Click()
    
        Set wss = DBEngine.Workspaces(0)
        Set dbs = wss.OpenDatabase("Gagedetails.mdb")
    
        OSQL = "INSERT INTO tblOpeName (name) SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
        Debug.Print OSQL
        
        dbs.Execute OSQL
        
        dbs.Close
        Set dbs = Nothing
        Set wss = Nothing
        
    End Sub

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can achieve the same more quickly and more easily:
    Code:
    NSQL = "INSERT INTO tblOpeName (name) SELECT DISTINCT OpeName1 FROM tblGage WHERE Date =" & SQLDate(txtdate.Text)
    dbs.Execute NSQL, dbFailOnError
    However pootle flump is right: it's a bad practice to use words such as "Date", "Name", "Type", etc. to name objects in the database and/or in the application.

    I presume that SQLDate properly formats txtdate.Text.
    Have a nice day!

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you need to do is develop better debugging skills

    so as a first step place a watch or breakpoint on the function/code reporting problems
    Google

    then run the code, at the breakpoint step through the code using the F8 key

    examine the contents of all variables as you go through to make certain they are what you expect and are valid.

    as the error you are reporting is to do with bad SQL and we can't see the contents of your sql you need to find out what is in there.

    it could be a malformed date
    it could be a missing or invalid parameter
    it could be a typo

    if you cannot determine what the problem is, then by all means post the actual SQL here
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Jul 2010
    Posts
    6

    Thumbs up Problem Solved

    Hi all

    Thankyou very much for the help. As u all said prblm is in my sql coding.
    pootle flump
    ur codings are working excelent. Thanks againg for your help.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure

Tags for this Thread

Posting Permissions

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