Solved

ADP with subforms causing error

Posted on 2006-06-29
12
1,022 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 85

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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