VB recordset with Sybase SQL Anywhere

Hi,
Suppose I have 2 tables:

 tblCustomers             tblContacts
PK noCust                PK noContact
   nameCust                 nameContact
                         FK noCust

noCust and noContact have a default value of autoincrement.

My problem is that, with MS Access 97, I can't had a customer and a contact at the same time:

        rstCust.AddNew
        rstCust!nameCust = Me.nameCust
        rstCust.Update
       
        rstCust.Bookmark rstCust.LastModified            

        rstContacts.AddNew
        rstContacts!nameContact = Me.nameContact
ERROR-> rstContacts!noCust = rstCust.noCust
        rstContacts.Update

I have a message that says that my record has been deleted. Is there a way (there should be) to make this work? All I want to do is to add those records in the same "transaction".

Any help would be appreciated, thanks
Jeff
jeanfrancoismelanconAsked:
Who is Participating?
 
EggSpurtConnect With a Mentor Commented:
Jeff,

I don't have a very satisfactory answer (maybe you do by now in which case please share it) but I've had a similar problem trying to use addnew. In my case I entered values for several fields at once, all the fixed length data types updated successfully but long varchar/text fields remain blank. Though this doesn't cause an error on the .update command it throws up errors if you subsequently try to read or work with the values that failed to add.
One workaround which may work here is to update and close the recordset then immediately reopen a recordset on this record (in your case where nameCust = Me.nameCust).
I think you would then be able to read rstCust!noCust
In my case I was then able to edit the record and enter the values for all the long varchars etc.
I'm still searching for a proper explanation and solution which is how I came across your question.

Cheers,
John
0
 
jeanfrancoismelanconAuthor Commented:
And my problem is with the autoincrement clause... I can't get back the value of noCust... the bookmark/lastmodified dosen't seem to work.
0
 
ChrisKingCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: EggSpurt

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ChrisKing
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.