Wierd Access 2000 to 2003 issues

[poporacer]

I have a DB that was written in Access 2000 that works. When I try to run it in Access 2003 it doesn't work. I tried to convert it from 2003 but that did not fix the problem. The form has several controls (text boxes, checkboxes and combo boxes) The form controls are populated from the result of queries from cascading combo boxes. The controls have TAG info to identify the table that the data is derived from. The form is then checked to see if any of the controls are "dirty". The problem seems to lie in the code to save the data. The code has a few functions that it calls, they are:

glbstrActiveLogNumber [color #ff0000]'This is used to get the currently selected Incident Log Number[/color]

glbstrActiveIDNumber[color #ff0000] 'This is used to get the currently selected Staff member[/color]

ControlPresent [color #ff0000] 'This is used to see if the control has a corresponding Field name (the controls are named the same as relevant Field names)[/color]


And the code that is not working is:

Private Function SaveData()
    Dim myControl As Control
    Dim myField As Field
    Dim rstStaff As Recordset
    Dim rstIncidents As Recordset
    
    Set rstIncidents = CurrentDb.OpenRecordset(QuerytblOffense)
    rstIncidents.FindFirst "[LogNum] = '" & glbstrActiveLogNumber & "'"
    If (rstIncidents.NoMatch = False) Then
        rstIncidents.Edit
        For Each myField In rstIncidents.Fields
            If (ControlPresent(myField.Name, Me)) Then
                Set myControl = Me.Controls(myField.Name)
                If (myControl.Tag = "IncidentData") Then
                    rstIncidents(myControl.Name) = myControl
                End If
            End If
        Next myField
        rstIncidents.Update
    End If
    
    Set rstStaff = CurrentDb.OpenRecordset(QueryStaffInIncident)
    rstStaff.FindFirst "[IDNum]='" & glbstrActiveIDNumber & "'"
    If (rstStaff.NoMatch = False) Then
        rstStaff.Edit
        For Each myField In rstStaff.Fields
            If (ControlPresent(myField.Name, Me)) Then
                Set myControl = Me.Controls(myField.Name)
                If (myControl.Tag = "StaffData") Then
                    rstStaff(myControl.Name) = myControl
                End If
            End If
        Next myField
        [highlight] rstStaff.Update[/highlight]'This doesn't write the data
    End If
    Call RefreshMe
End Function

The data doesn't get saved when run in 2003? Any suggestions and any other quirks I need to be on the look out for?

 

[pbaldy]

It runs without error, but doesn't save? Or does it error? For starters, I would make sure the DAO reference is checked in Tools/References, and disambiguate your declarations:

Dim rstStaff As DAO.Recordset
Dim rstIncidents As DAO.Recordset

 

[poporacer]

The DAO 3.6 object library was selected. The information does not get saved....No error Message either. I have used the dim statement may times without the DAO reference and have never had any issues. I changed the reference to DAO.recordset and when run I did not get an error. After looking some more, If I opened the form from the control Panel, I don't get an error message, if I open the form directly from the the browser window I get the following error when the Save Data function is run:
Run Time error 3218; could not update; currently locked. and the error is triggered by
rstStaff.Update
I got the same error with and without the DAO.Recordset indication. In the Control Panel, I have a button to open the form and is is real basic just:

Private Sub EditDataBtn_Click()
    DoCmd.OpenForm PanelEditIncident 
    DoCmd.Close acForm, Me.Name
End Sub

Any ideas???

 

[jal]

I don't use DAO, but with ADO recordsets I seem to recalll having problems if my routine failed to BOTH:

rs.Close
Set rs = Nothing

 

[gemma-the-husky]

are you sure ado isnt checked as well as dao in references - its an obvious possibility

 

[pbaldy]

As long as the declaration is disambiguated, having both references checked should not be an issue. Both are checked in many of the applications I create. That said, I'm still stumped on the original issue. Can you post a sample db that exhibits the problem?

 

[poporacer]

Here is the DB. The problem lies in the form frmEditIncident.To duplicate the problem, click on the Data button and then select an incident from the Log# box and then select a name from the name box. Change any of the data and save the changes. The save data function does not work. I hope you have a Merry Christmas!!!

 

[jwhite]

Sorry to report, it updated fine for me, Ac2003 on Win XP SP2.

 

[poporacer]

fdcusa, did it save the data changes??

 

[jwhite]

Update = Save, yes.

 

[poporacer]

Wierd...I ran it on three different computers at work-----Access 2000 on XP worked fine, Access 2003 with XP didn't work....And then on another XP with 2003 didn't work, then I tried it at home Access 2007 on Vista...didn't work.

 

[missinglinq]

When apps run fine on one machine and not on another or run fine in one version and not when run under a newer version, the first thing you have to think about are missing references. Developers far more knowledgeable than myself say that missing references can cause errors that have nothing to do with functions within the missing libraries!

 

[RexesOperator]

Out of curiosity (and this may not be related) - have you tried creating a blank database and then importing everything? That fixed a few problems I was having.

 

[poporacer]

Well I found the problem...I think. The query that I was using, was pulling information from two tables and a query with a couple of inner joins...It worked fine for populating the form but had issues with updating the recordset (the query was too complex) I just created another query that contained the relevant information that needed to be saved and it worked great....But then, why did the complex query work in 2000?
Thanks for your help!!!