Hey Raghavendra Mudugal,

Oh yea i remember coming across ADO Command Objects... currently i am struggling with handling two seperate tables.



Private Sub Command21_Click()
Rem insert
rst.Open "select * from qns_table", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst1.Open "select qnsID from autonumber", cn, adOpenDynamic, adLockOptimistic
    rst.AddNew
    rst!qnsID = "qns" & Right("0000" & CStr(CInt(rst1!qnsID) + 1), 4)
    rst!question = Me.Text17
    rst.Update
    rst1.Close
    rst1.Open "update autonumber set ansID = ansID +1", cn, adOpenDynamic, adLockOptmistic
End If

rst.Close

rst2.Open "select * from ans_table", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst1.Open "select ansID from autonumber", cn, adOpenDynamic, adLockOptimistic
    rst2.AddNew
    rst2!ansID = "ans" & Right("0000" & CStr(CInt(rst1!ansID) + 1), 4)
    rst2!answer = Me.Text19
    rst2.Update
    rst1.Close
    rst1.Open "update autonumber set qnsID = qnsID +1", cn, adOpenDynamic, adLockOptimistic
End If

rst2.Close


MsgBox "Data Inserted"
End Sub



i have created two columns for autonumber (qnsID & qnsID), initially when i started on only 'qns_table'... it worked fine but right now it doesn't seem so. sorry about this.
thank you.

Astro

Hello Astro

check the column names. you said " have created two columns for autonumber (qnsID & qnsID)" both are qnsID's. Check for the spellings and tell me what the error
that you are seeing while execution.

I guess you have problem with column name. you are trying to update qnsID both the
times.

Its mainly, i guess you code needs revision.

Please check and let me know the error that it gives.

Hope this helps.

With Regards,
Raghavendra Mudugal

Hey Raghavendra Mudugal,

Ah sorry, it was qnsID & ansID... i'll run through again and let you know the error.
Thanks!

Astro

Hello CheeWee

Here is the code for updating two tables.

rst.Open "select A.cName, A.cAddress, B.cName, B.cAddress from cust as A, cust1 as B where A.cid = 'qns0001'", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst.Fields(0) = "1212"
    rst.Fields(1) = "1212"
    rst.Fields(2) = "1212"
    rst.Fields(3) = "1212"
    rst.Update

End If
rst.Close

It depends upon the SQL statement how you hold the data, but both tables should
have a common column name that links both table. So we can get that particular
row to update it.

Hope this helps.

With Regards,
Raghavendra Mudugal

Hey Raghavendra Mudugal,

I am still having some trouble with handling 2 seperate 'inserting into table' with one click event.
I have tried duplicating the codes (line 3 to 12) with another If Then loop except everything ties with ans_table (instead of qns_table),
the other approach was repeating every line of the code (line 3 to 11), using seperate record sets.


1 Private Sub Command21_Click()


2 Rem insert
3 rst.Open "select * from qns_table", cn, adOpenDynamic, adLockOptimistic

4 If Not rst.EOF Then

5 rst1.Open "select qnsID from autonumber", cn, adOpenDynamic, adLockOptimistic
6 rst.AddNew
7 rst!qnsID = "qns" & Right("0000" & CStr(CInt(rst1!qnsID) + 1), 4)
8 rst!question = Me.Text17
9 rst.Update
10 rst1.Close
11 rst1.Open "update autonumber set qnsID = qnsID +1", cn, adOpenDynamic, adLockOptimistic

12 End If

13 rst.Close
14 MsgBox "Data Inserted"


15 End Sub



Unfortunately, boths ways did not work for me. Am i working at it in a wrong approach?
Would be nice if you could shed some light =) Sorry about this.

Thank You

Astro

I cannot insert just only cName to a cust1 table, cname and caddress to cust table (already done in your example) Also,I don quite understand “rst.Fields(0) = "1212"… <The (0) and “1212” refer to> The SQL statement I could understand. My main is to have cName to cust1 table, cname and caddress to cust table, both table can insert and update the same time.. please guide me.. sorry for my poor English.. thank..

Hello Astro,

It will be better, when you get the error just post the error message and
the line number and the code of that line.

With Regards,
Raghavendra Mudugal

Hello Cheewee

Yes you can, i said you before, IT DEPENDS UPON ON THE SQL STATEMENT ON WHAT DATA
IT HOLDS. fields(0),fields(1)... are related to the column name in the sql state
ment which you open for the rscordset.

For example

rst.Open "select A.cName, A.cAddress, B.cName from cust as A, cust1 as B where A.cid = 'qns0001'", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst.Fields(0) = "1212" 'for table CUST (first column name in SQL statement)
    rst.Fields(1) = "1212" 'for table CUST (second column name in SQL statement)
    rst.Fields(2) = "1212" 'for table CUST1 (third column name in SQL statement)
    rst.Update

End If
rst.Close

Hope this makes you clear to you




With Regards,
Raghavendra Mudugal

Hey Raghavendra_Mudugal!

Finally, it's up! well, not entirely but the inital part i worked on has completed! I'll try to upload it for you to see! Haha erm now i am completely clueless on how to allow a user retrieve all that data into a questionaire form with convenience. need some suggestion =)

Thank You
Astro

Hey Raghavendra Mudugal,

Here is the url where i uploaded my project file. You gotta copy and paste the url into your web browser in order to be able to download it.
Sorry for the trouble. =)

http://www.angelfire.com/fang/fiftytwo/project.zip

Thank You
Astro