update table records from form

J

Jon_H

I am trying to update the records in particular fields(table=Issues,
Field=Status) on a form but keep getting the following compile error.
"Method or Data member not found" when the following is started from a
button on the form.

Private Sub update_Click()
Dim conDatabase As ADODB.Recordset
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "UPDATE Issues SET Status = " & Status
conDatabase.Execute strSQL
conDatabase.close
Set conDatabase = Nothing
Exit_update_Click:
Exit Sub
End Sub

any help would be greatly appreciated.
cheers
Jon_H
 
S

Stefan Hoffmann

Jon_H said:
I am trying to update the records in particular fields(table=Issues,
Field=Status) on a form but keep getting the following compile error.
"Method or Data member not found" when the following is started from a
button on the form.

Private Sub update_Click()
Dim conDatabase As ADODB.Recordset
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "UPDATE Issues SET Status = " & Status
conDatabase.Execute strSQL
conDatabase.close
Set conDatabase = Nothing
Exit_update_Click:
Exit Sub
End Sub

any help would be greatly appreciated.
cheers
Jon_H



mfG
--> stefan <--
 
S

Stefan Hoffmann

hi,

Jon_H said:
Private Sub update_Click()
Dim conDatabase As ADODB.Recordset
You declaration is incorrect. Try
Dim conDatabase As ADODB.Connection
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "UPDATE Issues SET Status = " & Status
conDatabase.Execute strSQL
conDatabase.close
Set conDatabase = Nothing
Exit_update_Click:
Exit Sub
End Sub


mfG
--> stefan <--
 
J

Jon_H

I have fixed it with this but found I had to include the close command as it
only updates when the form is closed ???


Private Sub update_Click()
Dim ctl As Control
On Error GoTo Err_Update_Click
' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If
Exit_update:
DoCmd.close
Exit Sub
Err_Update_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_update
End Sub

Cheers
Jon_H

and mfG 2u2 Stefan
 
S

Stefan Hoffmann

hi,

Jon_H said:
I have fixed it with this but found I had to include the close command as it
only updates when the form is closed ???
You have to change the dirty state of your form.
Private Sub update_Click()
Dim ctl As Control
On Error GoTo Err_Update_Click
' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If
Exit_update:
DoCmd.close
Drop the Close, try:
Me.Dirty = False
Exit Sub
Err_Update_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_update
End Sub

mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error? 2
Invalid use of New Keyword 1
Problem with some replace code 2
VBA HELP 1
VB INSERT INTO 8
Error in INSERT TO Statement 7
Select Stament 2
error with my ADO code! 2

Top