Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADP with subforms causing error

Posted on 2006-06-29
12
Medium Priority
?
1,026 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 225 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 525 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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