Solved

ADP with subforms causing error

Posted on 2006-06-29
12
1,016 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now