Run time error 3219=== Plz Help Urgent

Rider570 · #1 (**permalink**) 07-06-10, 22:56

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

Rider570 · #2 (**permalink**) 07-06-10, 23:02

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

healdem · #3 (**permalink**) 07-07-10, 00:27

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

healdem · #4 (**permalink**) 07-07-10, 00:36

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

Rider570 · #5 (**permalink**) 07-07-10, 02:39

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

pootle flump · #6 (**permalink**) 07-07-10, 03:05

Threads merged.

Rider570 · #7 (**permalink**) 07-07-10, 03:13

Quote:

Originally Posted by healdem

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

pootle flump · #8 (**permalink**) 07-07-10, 03:31

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

Rider570 · #9 (**permalink**) 07-07-10, 03:48

Quote:

Originally Posted by pootle flump

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

pootle flump · #10 (**permalink**) 07-07-10, 04:08

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

Sinndho · #11 (**permalink**) 07-07-10, 04:17

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.

healdem · #12 (**permalink**) 07-07-10, 04:44

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

Rider570 · #13 (**permalink**) 07-07-10, 04:53

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.

pootle flump · #14 (**permalink**) 07-07-10, 05:20

My pleasure

Internet Services

Web Development

Digital Marketing

Consumer Tech