What went wrong?Please advise!

[Guest]

I posted in General Question may be not right place?
Here again my question, please advise. Thank you.
----
I used MS Access 2K, ODBC link to SQL2K Server
I got an error code Run-time error 3146. It's highlight at ".Update"

Dim db As Database
Dim Rs1, Rs2, Rs4 As Recordset
Set db = CurrentDb
Set Rs1 = db.OpenRecordset("dbo_case", dbOpenDynaset, dbSeeChanges)
With Rs1
If Not IsNull(Me.lname) And Not IsNull(Me.fname) Then
.AddNew
!Status = 1
.Update
End If
End With
If I put a "s" after Recordset(s) I got an error: Data Method not found

 

[Guest]

You declared Rs4 as Recordset, try this replaceing Rs1 with Rs4

Dim db As Database
Dim Rs1, Rs2, Rs4 As Recordset
Set db = CurrentDb
Set Rs4 = db.OpenRecordset("dbo_case", dbOpenDynaset, dbSeeChanges)
With Rs4
If Not IsNull(Me.lname) And Not IsNull(Me.fname) Then
.AddNew
!Status = 1
.Update
End If
End With

 

[Amy Blankenship]

What are you trying to accomplish with this code?

Thanks;

Amy

 

[Guest]

Thank for reply.
I tried to add new records into a table name "Case"
regards, MN

 

[Guest]

Thank for reply.
But it is not. I do have 3 recordset open.
With Rs1 I do ...
With Rs2 I do ...
With Rs4 I do ...
Regards, MN

 

[Guest]

Someone a while ago posted a similar problem, and he said that the problem
were solved after he ran Repair and compact.

Anoter advice, try to insert a record to this table without the recordset,
see what happen.
Does this table has a key defined?

 

[Guest]

Thank you,
Tried to Compact and repair: Same Problem?
Tried to insert record w/o using recordset is OK.
Yes this table have Primary Key define as of AutoNumber number.
Regards, MN

 

[Amy Blankenship]

OK, but what does that have to do with what's going on in the form? Why
can't you just bind the form to a query that includes this table?

-Amy

 

[Amy Blankenship]

OK, but if RS1 and RS2 were already opened to recordsets, you should get an
error when you dim them again as variants, which is what you're doing in
your code. At any rate, going into the Set rs1 line, rs1 is a variant.
However, since a variant can be anything, it should still work. I was not
able to get a with block to work when one of the things in the block started
with a !.

I had to reference the entire "address". Since your with block is not that
long, this should be no hardship.

Try:

rs1.AddNew
rs1!Status = 1
rs1.Update

HTH;

Amy

 

[Guest]

Thank you for your suggestion.
But the Form bound to different table. The remain on the Add new record
screen I disigned to add to a diffrent table. I am not do Query on the form,
so will try with your advise.
Regards, MN