[RESOLVED] Updateing Access Database

[aa9gg]

Hi...
I have several forms and I need to update an MS Access Database from a form that does not contain the database ref. I've tried this on the form that doesn't contain the record set:

Patient_Info2.datPrimaryRS.Recordset.UpdateBatch adAffectAll

, but when I go back to the form WITH the record set (Patient_Info2) and select another record I get the error "Operation was Cancel" with the record number that was suppose to be updated in the title of the message box .

If I use my "update record" command button on the Patient_Info2 form it saves it ok (button executes the same code as above) and then I can go to the next record. I need to make this automatic (from my other form) as well as with the command button.

What am I doing wrong???

[abhijit]

Hi...
I have several forms and I need to update an MS Access Database from a form that does not contain the database ref. I've tried this on the form that doesn't contain the record set:

Patient_Info2.datPrimaryRS.Recordset.UpdateBatch adAffectAll

, but when I go back to the form WITH the record set (Patient_Info2) and select another record I get the error "Operation was Cancel" with the record number that was suppose to be updated in the title of the message box .

If I use my "update record" command button on the Patient_Info2 form it saves it ok (button executes the same code as above) and then I can go to the next record. I need to make this automatic (from my other form) as well as with the command button.

What am I doing wrong???

Could you post the entire code that updates the recordset? How have you opened the recordset?

[aa9gg]

Good point...I don't see where it is actually opened! I built the form using the database wizard in VB6. It is defined in an Adodc control. The update code is:

Private Sub cmdUpdate_Click()
  On Error GoTo UpdateErr
  datPrimaryRS.Recordset.UpdateBatch adAffectAll
  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub

So is it REALLY opened????

[abhijit]

Good point...I don't see where it is actually opened! I built the form using the database wizard in VB6. It is defined in an Adodc control. The update code is:

Private Sub cmdUpdate_Click()
  On Error GoTo UpdateErr
  datPrimaryRS.Recordset.UpdateBatch adAffectAll
  Exit Sub
UpdateErr:
  MsgBox Err.Description
End Sub

So is it REALLY opened????

Firstly using bound controls is evil.

Secondly, batch updates are provider specific. The provider you use to open your connection determines if a batch update is possible.

Thirdly, I don't remember if the adodc control supports a batch update, but I am going to look that up.

[aa9gg]

Ok....re-did it using un-bound controls.

Option Explicit
Private cn As ADODB.Connection  'this is the connection object
Private rs As ADODB.Recordset   'this is the recordset object

Private Sub Form_Load()
    'instantiate the connection object
    Set cn = New ADODB.Connection
    'specify the connectionstring
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\Database\csv1000.mdb"
    'open the connection
    cn.Open
    
    'instantiate the recordset object
    Set rs = New ADODB.Recordset
    'open the recordset
    With rs
        .Open "csv1000_table", cn, adOpenKeyset, adLockPessimistic, adCmdTable
           
        'loop through the records until reaching the end or last record
        Do While Not .EOF
          tester_first_name.Text = rs.Fields("TESTER_FIRST_NAME")
            rs.MoveNext 'moves next record
        Loop
        
        If Not (.EOF And .BOF) Then
            rs.MoveFirst    'go to the first record if there are existing records
            FillFields      'to reflect the current record in the controls
        End If
        
    End With
End Sub

....filling fields with using:

tester_first_name.Text = rs.Fields("TESTER_FIRST_NAME")

...and updating DB using:

rs.Fields("TESTER_FIRST_NAME") = tester_first_name.Text
rs.Update 'this updates the recordset

Now....my problem is doing the update from another form. I tried this:

Patient_Info3.rs.Update 'this updates the recordset

but it tells me "Method or Data Member not found" What is the proper way to save/update from another form???

Thanks for all the help.....this is the best VB forum around!!!

[si_the_geek]

You are getting that error because rs is declared using the keyword Private, if you change that to Public it should get past that error. For more info see the article What is the difference between Dim/Private/Public/Global/Static/Const? from our Classic VB FAQs (in the FAQ forum)

However, if you are just using that line it will not make any changes to the fields, so wont seem to update anything. It may be best to simulate a click of your command button instead, eg:

Patient_Info2.cmdUpdate.value = True

[aa9gg]

The changes to the fields have already occurred elsewhere on the form, I just need to update/save the record. So should I use:

Patient_Info2.cmdUpdate.value = True

or

Patient_Info3.rs.Update

[abhijit]

You are getting that error because rs is declared using the keyword Private, if you change that to Public it should get past that error. For more info see the article What is the difference between Dim/Private/Public/Global/Static/Const? from our Classic VB FAQs (in the FAQ forum)

However, if you are just using that line it will not make any changes to the fields, so wont seem to update anything. It may be best to simulate a click of your command button instead, eg:

Patient_Info2.cmdUpdate.value = True

The changes to the fields have already occurred elsewhere on the form, I just need to update/save the record. So should I use:

Patient_Info2.cmdUpdate.value = True

or

Patient_Info3.rs.Update

Did you read si's post? This happened due to the scope of your variable. You could use

Patient_Info3.rs.Update

[aa9gg]

Yes...I did. Just wondering which is the better method (after I change the scope of my var.)

[si_the_geek]

The changes to the fields have already occurred elsewhere on the form,

Have the changes been written to the recordset? (eg: rs.Fields("TESTER_FIRST_NAME") = tester_first_name.Text )

If they have then Patient_Info3.rs.Update by itself is fine.

Otherwise you need to write the changes to the recordset - which I presume is what cmdUpdate does, so calling that is the simplest solution.

[aa9gg]

Hi Si...
Yes they have. Works great...thanks again!