Inserting records into MS-Access

[dinilkarun]

Hi,

I have two functions. The first function fills the first three fields of the first row of an MS_Access table. I want to fill the remaining fields of the same(1st) row in the second function. Presently the second function fills the remaining fields of the next(2nd) row while the remaining fields of the first row remain unfilled.
Also the first three fields of the 2nd row remain unfilled.

Please help!

Regards,
Dinil

[Baradaran]

Hi,
I would say you declare a public RS e.g.
Public RS_RecordSet As ADODB.RecordSet

A Public DataBase Connection e.g.
Public Connection As New ADODB.Connection

Then you open the connection to the database:

Connection.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & App.Path & "\database.mdb; Jet OLEDB:Database Password=yourpassword"

Connection.Open

Then in your first function you say:

RS_RecordSet.Open "Insert into (FIELD1, FIELD2, FIELD3) Values ('" _
& text1.text & "', '" & text2.text & "', '" _
& text3.text & "');", Connection, 3, 3
RS_RecordSet.Close

In your second function you write:

RS_RecordSet.Open "Insert into (FIELD4, FIELD5, FIELD6) Values ('" _
& text4.text & "', '" & text5.text & "', '" _
& text6.text & "');", Connection, 3, 3
RS_RecordSet.Close

That should be it. If you copy and paste the codes, please check for mistakes ;)

[QVeen72]

Hi,

Declare the Recordset in Form Level.
Open the Recordset In First Function
(Or Before calling first function)
Add required fields in first function. Dont close the recordset.
Now call Second Function, here Dont open the recordset again..
something like this :

Option Explicit
Dim MyRS As New ADODB.Recordset

Private Sub Myproc1()
    MyRS.Open "Select * From MyTab",Conn, adOpenDynamic, adLockOptimistic
    MyRS.AddNew
    MyRS("Fl1") = Text1.Text
    MyRS("Fl2") = Text2.Text
    MyRS("Fl3") = Text3.Text
    MyRS.Update
End Sub

Private Sub Myproc2()
   MyRS("Fl4") = Text4.Text
   MyRS("Fl5") = Text5.Text
   MyRS("Fl6") = Text6.Text
   MyRS.Update
End Sub

Regards
Veena

[dinilkarun]

Thanks for your suggestions. I tried the above given solutions but still I am unable to rectify the problem.
Please find attached the snap shot of the database. The first three fields in the first row are being populated in the first function. The second function is called multiple times, and each time one field should be updated in the same (1st) row.
Presently, when the second function is getting called, the field is updated in the next row as shown in the attachment.

Please suggest a solution!

Regards,
Dinil

[dinilkarun]

Thanks for your suggestions.
I used the 'update table' statement instead of 'insert into' statement.
Its working now....

Cheers,
Dinil