Where are my tables?

S

Scott A

I have a piece of VBA that just a few days ago was working
fine. It uses the after update event of an option group
to write changes to another table.

I am now getting an error stating that the item is not
found in the collection, when I'm sure it is (or at least
was) there!!!

Here's the code...


Private Sub optStatus_AfterUpdate()

Dim db As DAO.Database
Dim rstSOPHistory As DAO.Recordset

On Error GoTo Err_optStatus_AfterUpdate

'Determine whether user would like to record updates
using vbYesNo dialog
If MsgBox("Would you like to update the status of this
SOP in the SOP History?", _
vbYesNo, "Add to SOP History") = vbYes Then

Set db = CurrentDb()
'Open tblSOPHistory dynaset
Set rstSOPHistory = db.OpenRecordset
("tblSOPHistory", dbOpenDynaset, dbAppendOnly)

'Collect data from current form and map to fields
in dynaset***
With rstSOPHistory
.AddNew
![SOPHistID] = Me!SOPID
![SOPHistCode] = Me!SOPCode
![SOPHistTitle] = Me!SOPTitle
![SOPHistVersion] = Me!SOPVersion
![SOPHistStatus] = Me!SOPStatus
![HistDate] = Date
rstSOPHistory.Update
End With

rstSOPHistory.Close
Else: Exit Sub

End If

Exit_Err_optStatus_AfterUpdate:
Exit Sub

Err_optStatus_AfterUpdate:

MsgBox Err.Description
Resume Exit_Err_optStatus_AfterUpdate

***This is where the code falls appart when stepping
through each line. It is grabbing all of the values I
want it to with the Me!Field-s, but when I mouse over the !
[tblSOPHistoryfields], it says <Item not found in this
collcetion.>

Am I not identifying the table correctly in the recordset
declaration? I'm not sure how this was working before and
is not now...

Help?

Scott
 
D

Dirk Goldgar

Scott A said:
I have a piece of VBA that just a few days ago was working
fine. It uses the after update event of an option group
to write changes to another table.

I am now getting an error stating that the item is not
found in the collection, when I'm sure it is (or at least
was) there!!!

Here's the code...


Private Sub optStatus_AfterUpdate()

Dim db As DAO.Database
Dim rstSOPHistory As DAO.Recordset

On Error GoTo Err_optStatus_AfterUpdate

'Determine whether user would like to record updates
using vbYesNo dialog
If MsgBox("Would you like to update the status of this
SOP in the SOP History?", _
vbYesNo, "Add to SOP History") = vbYes Then

Set db = CurrentDb()
'Open tblSOPHistory dynaset
Set rstSOPHistory = db.OpenRecordset
("tblSOPHistory", dbOpenDynaset, dbAppendOnly)

'Collect data from current form and map to fields
in dynaset***
With rstSOPHistory
.AddNew
![SOPHistID] = Me!SOPID
![SOPHistCode] = Me!SOPCode
![SOPHistTitle] = Me!SOPTitle
![SOPHistVersion] = Me!SOPVersion
![SOPHistStatus] = Me!SOPStatus
![HistDate] = Date
rstSOPHistory.Update
End With

rstSOPHistory.Close
Else: Exit Sub

End If

Exit_Err_optStatus_AfterUpdate:
Exit Sub

Err_optStatus_AfterUpdate:

MsgBox Err.Description
Resume Exit_Err_optStatus_AfterUpdate

***This is where the code falls appart when stepping
through each line. It is grabbing all of the values I
want it to with the Me!Field-s, but when I mouse over the !
[tblSOPHistoryfields], it says <Item not found in this
collcetion.>

Am I not identifying the table correctly in the recordset
declaration? I'm not sure how this was working before and
is not now...
Click to expand...

It's hard to say, but since the OpenRecordset call didn't raise an
error, it seems as though the table exists. Could the names of the
fields in the table have been changed? Open the table in design view
and check the field names. Or for that matter, if you are stepping
through the code, you can open the Locals window to examine the fields
in the recordset as you go along.
 
S

Scott A

Dirk - Thanks for the tip, as that is exactly what I did -
I had changed the name of one of the fields in the table,
but the name was so similar to the previous name that I
overlooked it.

Scott
-----Original Message-----
I have a piece of VBA that just a few days ago was working
fine. It uses the after update event of an option group
to write changes to another table.

I am now getting an error stating that the item is not
found in the collection, when I'm sure it is (or at least
was) there!!!

Here's the code...


Private Sub optStatus_AfterUpdate()

Dim db As DAO.Database
Dim rstSOPHistory As DAO.Recordset

On Error GoTo Err_optStatus_AfterUpdate

'Determine whether user would like to record updates
using vbYesNo dialog
If MsgBox("Would you like to update the status of this
SOP in the SOP History?", _
vbYesNo, "Add to SOP History") = vbYes Then

Set db = CurrentDb()
'Open tblSOPHistory dynaset
Set rstSOPHistory = db.OpenRecordset
("tblSOPHistory", dbOpenDynaset, dbAppendOnly)

'Collect data from current form and map to fields
in dynaset***
With rstSOPHistory
.AddNew
![SOPHistID] = Me!SOPID
![SOPHistCode] = Me!SOPCode
![SOPHistTitle] = Me!SOPTitle
![SOPHistVersion] = Me!SOPVersion
![SOPHistStatus] = Me!SOPStatus
![HistDate] = Date
rstSOPHistory.Update
End With

rstSOPHistory.Close
Else: Exit Sub

End If

Exit_Err_optStatus_AfterUpdate:
Exit Sub

Err_optStatus_AfterUpdate:

MsgBox Err.Description
Resume Exit_Err_optStatus_AfterUpdate

***This is where the code falls appart when stepping
through each line. It is grabbing all of the values I
want it to with the Me!Field-s, but when I mouse over the !
[tblSOPHistoryfields], it says <Item not found in this
collcetion.>

Am I not identifying the table correctly in the recordset
declaration? I'm not sure how this was working before and
is not now...
Click to expand...

It's hard to say, but since the OpenRecordset call didn't raise an
error, it seems as though the table exists. Could the names of the
fields in the table have been changed? Open the table in design view
and check the field names. Or for that matter, if you are stepping
through the code, you can open the Locals window to examine the fields
in the recordset as you go along.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Top