Edit a record set

  • Thread starter Thread starter Mauro
  • Start date Start date
M

Mauro

I am using VBA to update an existing recordset. I can not get the find to
work for me. I need to find the specific ID and then edit the record
containing that ID. The Findnext gives me an error msg 'object required'.

' ***** Start Code ******
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset
Dim rstFORM As DAO.Recordset
Dim blncriteria As Boolean

Set dbQATApp = CurrentDb
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE")
Set rstFORM = Me.txt_MONITOR_STATUS.Value

blncriteria = IsNull(DLookup("[QAT_MONT_QID]",
"[MAIN_QAT_MONITOR_INFO_TABLE]", "[QAT_MONT_INFO_ID]=" &
Me.lst_MONITOR_LIST.Value))

If blncriteria = False Then
MsgBox "no match"
Else
'*** Find record - not working*****

rstupdateMon.FindNext (QAT_MONT_INFO_ID = txt_MONITOR_STATUS.Value)
MsgBox "Found Value"

'**** write data *****

rstupdateMon.Edit

rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update
rstupdateMon.Close

End If
' ***** End Code ******
 
Sorry. Dim rstFORM As DAO.Recordset and Set rstFORM =
Me.txt_MONITOR_STATUS.Value needed to be removed from this. The error now is
'Operation is not supported for this type of object.'
 
I'm a novice so can you explain this? Me.txt_MONITOR_STATUS.Value is a
primarykey id that I'm trying to equate from the form to the table. Also, I
never used a Dynaset before so if you dont mind a brief explanation.

Marshall Barton said:
Mauro said:
I am using VBA to update an existing recordset. I can not get the find to
work for me. I need to find the specific ID and then edit the record
containing that ID. The Findnext gives me an error msg 'object required'.

' ***** Start Code ******
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset
Dim rstFORM As DAO.Recordset
Dim blncriteria As Boolean

Set dbQATApp = CurrentDb
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE")
Set rstFORM = Me.txt_MONITOR_STATUS.Value []
'*** Find record - not working*****
rstupdateMon.FindNext (QAT_MONT_INFO_ID = txt_MONITOR_STATUS.Value)
Click to expand...
[]

Are you sure that Me.txt_MONITOR_STATUS.Value is a
recordset?

The other thing that can cause a problem is that you did not
specify the type of recordset for
MAIN_QAT_MONITOR_INFO_TABLE
so it mmight be a table type recordset, which does not have
the Find... methods. You probably want to use Dynaset type
recordset.
Click to expand...
 
You have some conflicting info going with the Me.txt_MONITOR_STATUS.Value
like the other guy said. You can pretty much just eliminate the lines

Dim rstFORM as DAO.Recordset and
Set rstFORM = Me.txt_MONITOR_STATUS.Value

Also maybe you didn't show the entire code but you have
On Error GoTo Err_cmd_SAVE_RECORD_Click

But you do not have
Err_cmd_SAVE_RECORD_Click:
At the bottom of the sub to catch the error in case there is one.

And one thing I like doing when testing code is putting comment lines in
front of the error capturing code so that the debug pops up and shows you the
exact line of the error. It's much easier to debug, then when it is time to
have the database actually work, you add in the error catching lines.
 
Thank you all for your time. I get no error at this point. It only updates
the first record in my table instead of finding the value that I need it to
find and update that record. Here is what the new code looks like. I also
tried setting rstupdateMon.FindFirst "QAT_MONT_INFO_ID = 64 " which is the
hard value it should be finding based on the click event.

' ***** start code *****
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset

Set dbQATApp = CurrentDb()
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE",
dbOpenDynaset)
rstupdateMon.FindFirst "'QAT_MONT_INFO_ID' = 'Me.lst_MONITOR_LIST.Value'"

MsgBox (Me.lst_MONITOR_LIST.Value)

rstupdateMon.Edit
rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update

Exit_cmd_SAVE_RECORD_Click:
Exit Sub

Err_cmd_SAVE_RECORD_Click:
MsgBox Err.Description
Resume Exit_cmd_SAVE_RECORD_Click

End Sub

' ***** end code *****

akphidelt said:
You have some conflicting info going with the Me.txt_MONITOR_STATUS.Value
like the other guy said. You can pretty much just eliminate the lines

Dim rstFORM as DAO.Recordset and
Set rstFORM = Me.txt_MONITOR_STATUS.Value

Also maybe you didn't show the entire code but you have
On Error GoTo Err_cmd_SAVE_RECORD_Click

But you do not have
Err_cmd_SAVE_RECORD_Click:
At the bottom of the sub to catch the error in case there is one.

And one thing I like doing when testing code is putting comment lines in
front of the error capturing code so that the debug pops up and shows you the
exact line of the error. It's much easier to debug, then when it is time to
have the database actually work, you add in the error catching lines.

Mauro said:
I am using VBA to update an existing recordset. I can not get the find to
work for me. I need to find the specific ID and then edit the record
containing that ID. The Findnext gives me an error msg 'object required'.

' ***** Start Code ******
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset
Dim rstFORM As DAO.Recordset
Dim blncriteria As Boolean

Set dbQATApp = CurrentDb
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE")
Set rstFORM = Me.txt_MONITOR_STATUS.Value

blncriteria = IsNull(DLookup("[QAT_MONT_QID]",
"[MAIN_QAT_MONITOR_INFO_TABLE]", "[QAT_MONT_INFO_ID]=" &
Me.lst_MONITOR_LIST.Value))

If blncriteria = False Then
MsgBox "no match"
Else
'*** Find record - not working*****

rstupdateMon.FindNext (QAT_MONT_INFO_ID = txt_MONITOR_STATUS.Value)
MsgBox "Found Value"

'**** write data *****

rstupdateMon.Edit

rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update
rstupdateMon.Close

End If
' ***** End Code ******
Click to expand...
Click to expand...
 
rstupdateMon.FindFirst "QAT_MONT_INFO_ID = " & Me.lst_MONITOR_LIST.Value

Or if QAT_MONT_INFO_ID is a text field, then the following (all one line)

rstupdateMon.FindFirst "QAT_MONT_INFO_ID=""" & Me.lst_MONITOR_LIST.Value & """"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Thank you all for your time. I get no error at this point. It only updates
the first record in my table instead of finding the value that I need it to
find and update that record. Here is what the new code looks like. I also
tried setting rstupdateMon.FindFirst "QAT_MONT_INFO_ID = 64 " which is the
hard value it should be finding based on the click event.

' ***** start code *****
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset

Set dbQATApp = CurrentDb()
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE",
dbOpenDynaset)
rstupdateMon.FindFirst "'QAT_MONT_INFO_ID' = 'Me.lst_MONITOR_LIST.Value'"

MsgBox (Me.lst_MONITOR_LIST.Value)

rstupdateMon.Edit
rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update

Exit_cmd_SAVE_RECORD_Click:
Exit Sub

Err_cmd_SAVE_RECORD_Click:
MsgBox Err.Description
Resume Exit_cmd_SAVE_RECORD_Click

End Sub

' ***** end code *****

akphidelt said:
You have some conflicting info going with the Me.txt_MONITOR_STATUS.Value
like the other guy said. You can pretty much just eliminate the lines

Dim rstFORM as DAO.Recordset and
Set rstFORM = Me.txt_MONITOR_STATUS.Value

Also maybe you didn't show the entire code but you have
On Error GoTo Err_cmd_SAVE_RECORD_Click

But you do not have
Err_cmd_SAVE_RECORD_Click:
At the bottom of the sub to catch the error in case there is one.

And one thing I like doing when testing code is putting comment lines in
front of the error capturing code so that the debug pops up and shows you the
exact line of the error. It's much easier to debug, then when it is time to
have the database actually work, you add in the error catching lines.

Mauro said:
I am using VBA to update an existing recordset. I can not get the find to
work for me. I need to find the specific ID and then edit the record
containing that ID. The Findnext gives me an error msg 'object required'.

' ***** Start Code ******
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset
Dim rstFORM As DAO.Recordset
Dim blncriteria As Boolean

Set dbQATApp = CurrentDb
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE")
Set rstFORM = Me.txt_MONITOR_STATUS.Value

blncriteria = IsNull(DLookup("[QAT_MONT_QID]",
"[MAIN_QAT_MONITOR_INFO_TABLE]", "[QAT_MONT_INFO_ID]=" &
Me.lst_MONITOR_LIST.Value))

If blncriteria = False Then
MsgBox "no match"
Else
'*** Find record - not working*****

rstupdateMon.FindNext (QAT_MONT_INFO_ID = txt_MONITOR_STATUS.Value)
MsgBox "Found Value"

'**** write data *****

rstupdateMon.Edit

rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update
rstupdateMon.Close

End If
' ***** End Code ******
Click to expand...
Click to expand...
Click to expand...
 
thank you so much. rstupdateMon.FindFirst "QAT_MONT_INFO_ID = " &
Me.lst_MONITOR_LIST.Value worked great.

John Spencer said:
rstupdateMon.FindFirst "QAT_MONT_INFO_ID = " & Me.lst_MONITOR_LIST.Value

Or if QAT_MONT_INFO_ID is a text field, then the following (all one line)

rstupdateMon.FindFirst "QAT_MONT_INFO_ID=""" & Me.lst_MONITOR_LIST.Value & """"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Thank you all for your time. I get no error at this point. It only updates
the first record in my table instead of finding the value that I need it to
find and update that record. Here is what the new code looks like. I also
tried setting rstupdateMon.FindFirst "QAT_MONT_INFO_ID = 64 " which is the
hard value it should be finding based on the click event.

' ***** start code *****
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset

Set dbQATApp = CurrentDb()
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE",
dbOpenDynaset)
rstupdateMon.FindFirst "'QAT_MONT_INFO_ID' = 'Me.lst_MONITOR_LIST.Value'"

MsgBox (Me.lst_MONITOR_LIST.Value)

rstupdateMon.Edit
rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update

Exit_cmd_SAVE_RECORD_Click:
Exit Sub

Err_cmd_SAVE_RECORD_Click:
MsgBox Err.Description
Resume Exit_cmd_SAVE_RECORD_Click

End Sub

' ***** end code *****

akphidelt said:
You have some conflicting info going with the Me.txt_MONITOR_STATUS.Value
like the other guy said. You can pretty much just eliminate the lines

Dim rstFORM as DAO.Recordset and
Set rstFORM = Me.txt_MONITOR_STATUS.Value

Also maybe you didn't show the entire code but you have
On Error GoTo Err_cmd_SAVE_RECORD_Click

But you do not have
Err_cmd_SAVE_RECORD_Click:
At the bottom of the sub to catch the error in case there is one.

And one thing I like doing when testing code is putting comment lines in
front of the error capturing code so that the debug pops up and shows you the
exact line of the error. It's much easier to debug, then when it is time to
have the database actually work, you add in the error catching lines.

:

I am using VBA to update an existing recordset. I can not get the find to
work for me. I need to find the specific ID and then edit the record
containing that ID. The Findnext gives me an error msg 'object required'.

' ***** Start Code ******
Private Sub cmd_SAVE_RECORD_Click()
On Error GoTo Err_cmd_SAVE_RECORD_Click

Dim dbQATApp As DAO.Database
Dim rstupdateMon As DAO.Recordset
Dim rstFORM As DAO.Recordset
Dim blncriteria As Boolean

Set dbQATApp = CurrentDb
Set rstupdateMon = dbQATApp.openrecordset("MAIN_QAT_MONITOR_INFO_TABLE")
Set rstFORM = Me.txt_MONITOR_STATUS.Value

blncriteria = IsNull(DLookup("[QAT_MONT_QID]",
"[MAIN_QAT_MONITOR_INFO_TABLE]", "[QAT_MONT_INFO_ID]=" &
Me.lst_MONITOR_LIST.Value))

If blncriteria = False Then
MsgBox "no match"
Else
'*** Find record - not working*****

rstupdateMon.FindNext (QAT_MONT_INFO_ID = txt_MONITOR_STATUS.Value)
MsgBox "Found Value"

'**** write data *****

rstupdateMon.Edit

rstupdateMon("QAT_MONT_QID").Value = Me.txt_QTEAM_ID.Value
rstupdateMon("QAT_MONT_CREATE_DATE").Value = Now()
rstupdateMon("QAT_MONT_QAC").Value = Me.txt_CURRENT_USER
rstupdateMon("QAT_MONT_REP_ID").Value = Me.txt_REPRESENTATIVE_VALUE
rstupdateMon("QAT_MONT_SUP_ID").Value = Me.txt_REPRESENTATIVE_FILTER
rstupdateMon("QAT_MONT_TYPE").Value = Me.lst_MONITOR_TYPE
rstupdateMon("QAT_MONT_SCORE").Value = Me.txt_OVERALL_SCORE
rstupdateMon("QAT_MONT_FCR").Value = Me.rdo_FCR
rstupdateMon("QAT_MONT_MEMO").Value = Me.mem_MEMO

rstupdateMon.Update
rstupdateMon.Close

End If
' ***** End Code ******
Click to expand...
Click to expand...
Click to expand...
 
Back
Top