Solved

ADP with subforms causing error

Posted on 2006-06-29
12
1,018 Views
Last Modified: 2008-03-17
I have Access 2003 and SQL Server.  My adp has a main form with a tab control.  On the second tab are two subforms, linked by the autonumber field of the main table.

When I add a new record using "DoCmd.GoToRecord , , acNewRec", it all appears ok at first.  But when I begin to type a value into the first field (which is a datetime field using a Calendar8 control), I get an error in the Form_Current event of the subforms.  The error reads as follows:

Run-time error '31004'
The value of an (Autonumber) field cannot be retrieved prior to being saved....

I've gotten around it as far as the users are concerned by putting in "On error resume next", but that's obviously a bandage, not a solution.

I've even tried a save immediately following the new record, but that didn't seem to make a difference.

What can I do to alleviate this problem?

Thanks.


0
Comment
Question by:bjones8888
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17008375
How did u try save

did u try something like this

Me.Dirty = False

0
 
LVL 84
ID: 17008395
What code are you running the Current event of the subforms?

rocki is correct, of course, if you force a save this will alleviate your troubles ... however often you don't want to do this ...
0
 

Author Comment

by:bjones8888
ID: 17008564
In trying to save, I was calling a "save" button (cmdSave_Click), which has the following statement:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

How does that differ from "Me.Dirty = False"?

The Form_Current event of the subforms recalcs some total fields by executing a sql statement.  One of the subforms records time entered on this main record.  Here's the Form_Current event of the time subform.

Private Sub Form_Current()
    On Error Resume Next
    RecalcTimes
End Sub

Private Sub RecalcTimes()
    Dim strSQL As String
    Dim rst As New ADODB.Recordset
   
    If Not IsNull([Forms]![frmConverting]!ID) Then
        strSQL = "SELECT IsNull(Sum([Downtime]),0) AS SumOfUptime " & _
                    "FROM Downtime WHERE (((Downtime.ID)=" & [Forms]![frmConverting]!ID & ") " & _
                        "AND ((Downtime.Code)='UT'));"
        If conn.State <> adStateOpen Then
            conn.Open "Provider=sqloledb;" & _
               "Data Source=XYZ-SQL-2K;" & _
               "Initial Catalog=Repts;" & _
               "User Id=apup;" & _
               "Password=ABCDEF"
        End If
        With rst
            .Open strSQL, conn, adOpenKeyset, adLockReadOnly
            If Not .EOF Then
                Me.TotalUptime.Enabled = True
                Me.TotalUptime.Locked = False
                Me.TotalUptime = CDbl(Nz(rst.Fields("SumOfUptime"), 0))
                Me.TotalUptime.Locked = True
                Me.TotalUptime.Enabled = False
            Else
                Me.TotalUptime = 0
            End If
            .Close
        End With
        strSQL = "SELECT Sum([Downtime]) AS SumOfDowntime FROM Downtime WHERE ID=" & [Forms]![frmConverting]!ID & " AND Code<>'UT';"
        With rst
            .Open strSQL, conn, adOpenKeyset, adLockReadOnly
            If Not .EOF Then
                Me.TotalDowntime.Enabled = True
                Me.TotalDowntime.Locked = False
                Me.TotalDowntime = CDbl(Nz(rst.Fields("SumOfDowntime"), 0))
                Me.TotalDowntime.Locked = True
                Me.TotalDowntime.Enabled = False
            Else
                Me.TotalDowntime = 0
            End If
            .Close
        End With
    Else
        Me.TotalUptime = 0
        Me.TotalDowntime = 0
    End If
    Set rst = Nothing
End Sub
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:bjones8888
ID: 17008579
One more thing....

This error only occurs when the project is run from a client machine on the floor.  I have the same project running on a box that has SQL Enterprise Manager on it, with the server registered pointing to that same SQL Server.  Never do I get this error when running on that box.  Only on the "shop floor".

That doesn't make sense to me, but it's reality.  I don't know why.
0
 

Author Comment

by:bjones8888
ID: 17008593
I'm sorry if I didn't make this clear before, but the code above is in the Form_Current event of "DowntimeSubform" -- which is a subform to "frmConverting".  The link between them is a field called ID, which is the PK of frmConverting's main table (and is also an Autonumber field).
0
 
LVL 6

Expert Comment

by:AHMKC1
ID: 17008649
Enter Data of any one field in main table then try entry to subform
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 75 total points
ID: 17008779
Dirty is a flag that is set whenever a change/insert has been made. It gets set to True
changing it to False forces a save

but u do that anyway with

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

I do not know if there are any subtle differences between the two though


At what point do u do a save, have u considered doing it after setting fields in RecalcTimes
0
 

Author Comment

by:bjones8888
ID: 17008843
I click the "Add" button and get positioned to a new blank record.  No problems.  I'm on the first tab - showing only the main table.  I'm on the first field.  As soon as any key is typed in that first field -- "6" for example when entering the date -- I get the runtime error, and the debugger puts me in the Form_Current event of one of the subforms.  (Actually, in RecalcTimes, which is called in the subform's current event.)

Here's the code in my "Add" button's click event.

  DoCmd.GoToRecord , , acNewRec
  cmdSave_Click

0
 

Author Comment

by:bjones8888
ID: 17008875
Notice in my code that I'm testing for the autonumber field being Null.  Apparently the value of the ID field passes the "Not IsNull" test, but isn't yet available to be used in the sql statement following.  The debugger stops on the line where I'm trying to set the value of strSQL - and I assume it's because of trying to rely on [Forms]![frmConverting]!ID.

  If Not IsNull([Forms]![frmConverting]!ID) Then
        strSQL = "SELECT IsNull(Sum([Downtime]),0) AS SumOfUptime " & _
                    "FROM Downtime WHERE (((Downtime.ID)=" & [Forms]![frmConverting]!ID & ") " & _
                        "AND ((Downtime.Code)='UT'));"
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 175 total points
ID: 17011928
Try this:

If Nz(Forms!frmConverting!ID,"") = "" then
  <other code here>
End IF

When your debugger stops, try examining the value of the ID value. Type this in the immediate window:

?Forms!frmConverting.ID

and see what you come up with
0
 

Author Comment

by:bjones8888
ID: 17012040
I'll give that a try.  I am currently off site but will know first thing in the morning.  Thanks.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17256243
Hi bjones8888
Hows it going with this?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question