SQL call not working

[mecca]

I get a run-time error 3075 when running the following sql command...

Set rs = db.OpenRecordset("UPDATE CustomerProfile SET CustomerProfile.FirstName = " + Chr$(34) + txtFirstName + Chr$(34) + " WHERE (((CustomerProfile.ContactID)= " + Chr$(34) + txtContactID + Chr$(34) + " ")

Any suggestions as to why this doesn't work?

Thanks,
Dave

[WadeD]

Have you tried pasting the string into a textbox to make sure it has the correct syntax? From there, I would run directly from the database to make sure it's working before coming back into vb.

Good luck,
Wade

[mecca]

This is the sql view in Access:

UPDATE CustomerProfile SET CustomerProfile.FirstName = "Dave"
WHERE (((CustomerProfile.ContactID)="5"));

It works fine....but calling it from vb requires some tweaking in order to pull the information from the txtFirstName and txtContactID text boxes...any suggestions?

Thanks,
Dave

[JHausmann]

Try this instead:

dim sQry as string

sQry= "UPDATE CustomerProfile SET FirstName = '" & txtFirstName & "' WHERE ContactID= '" & txtContactID & "'"

Set rs = db.OpenRecordset(sQry)

[This message has been edited by JHausmann (edited 01-20-2000).]

[mecca]

JHausmann,

I put the code you wrote into my application. It gives an error message, "Invalid Operation" and highlights the following line...

Set rs = db.OpenRecordset(sQry)

Any suggestions?

[Clunietp]

the OpenRecordset method only works for SELECT queries.

Because we are doing an UPDATE, we need to use

DB.Execute sQry

Where DB is your DAO database object. Also, if ContactID is a numeric field, you don't want to have quotes around that value...

HTH

Tom

[mecca]

Thank you so much! Works great! Now I can move forward with the application!