bjones8888
asked on
ADP with subforms causing error
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.
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.
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 ...
rocki is correct, of course, if you force a save this will alleviate your troubles ... however often you don't want to do this ...
ASKER
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]![frmConvert ing]!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("SumOfU ptime"), 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("SumOfD owntime"), 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
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]![frmConvert
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("SumOfU
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
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("SumOfD
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
ASKER
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.
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.
ASKER
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).
Enter Data of any one field in main table then try entry to subform
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Here's the code in my "Add" button's click event.
DoCmd.GoToRecord , , acNewRec
cmdSave_Click
ASKER
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]![frmConvert ing]!ID) Then
strSQL = "SELECT IsNull(Sum([Downtime]),0) AS SumOfUptime " & _
"FROM Downtime WHERE (((Downtime.ID)=" & [Forms]![frmConverting]!ID & ") " & _
"AND ((Downtime.Code)='UT'));"
If Not IsNull([Forms]![frmConvert
strSQL = "SELECT IsNull(Sum([Downtime]),0) AS SumOfUptime " & _
"FROM Downtime WHERE (((Downtime.ID)=" & [Forms]![frmConverting]!ID
"AND ((Downtime.Code)='UT'));"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll give that a try. I am currently off site but will know first thing in the morning. Thanks.
Hi bjones8888
Hows it going with this?
Hows it going with this?
did u try something like this
Me.Dirty = False