Solved

ADP with subforms causing error

Posted on 2006-06-29
12
1,020 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

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.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
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 …

830 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