• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

DoCmd.RunCommand acCmdSaveRecord returns Invalid use of Null

Hello,
Any help would be appreciated.

Problem: I keep getting an error message "Invalid use of Null" returned on DoCmd.RunCommand acCmdSaveRecord.  I want to save the record, then immediately return a field to use.  I'm trying to return [Forms]![CreateSCRSub]![SCR ID] which is the primary key and auto-incremented.

Using: Access 2000 & XP database front-end with a SQL 7 server back-end.  This error happens almost everytime on AccessXP a sporadically on the Access 2000 front-end.

Some example code:
____
Private Sub SaveSCR()
On Error GoTo Err_SaveSCR

        Dim dbsMSCR As Database
        Dim rsNew As Recordset
        Dim rsDependent As Recordset
        Dim rsTempRecord As Recordset
        Dim rsChangeRequest As Recordset

        'Copy the Form stuff to the record
        DoCmd.OpenForm "CreateSCRSub", acNormal, , , acFormAdd, acHidden
        [Forms]![CreateSCRSub]!Name = txtName
        [Forms]![CreateSCRSub]!Software = Software
        [Forms]![CreateSCRSub]!Description = Description
        [Forms]![CreateSCRSub]!Priority = Priority
        [Forms]![CreateSCRSub]!Originator = Originator
       
        'Save Record to Get the Number
        DoCmd.RunCommand acCmdSaveRecord
        mySCRID = [Forms]![CreateSCRSub]![SCR ID]

        'Set to Curent DB
        Set dbsMSCR = CurrentDb
        'Open the BugNumbers Table
        Set rsNew = dbsMSCR.OpenRecordset("BugNumbers", dbOpenDynaset, dbSeeChanges)
        'Open the Dependencies Table
        Set rsDependent = dbsMSCR.OpenRecordset("Dependancy List", dbOpenDynaset, dbSeeChanges)
        'Open the TempBufDepTable
        Set rsTempRecord = dbsMSCR.OpenRecordset(strMyUniqueName, dbOpenTable, dbReadOnly)
       
        If Not (rsTempRecord.EOF) Then
            rsTempRecord.MoveFirst
            While Not (rsTempRecord.EOF)
                If (rsTempRecord("Dependency")) Then
                    rsDependent.AddNew
                    rsDependent("Source SCR ID") = mySCRID
                    rsDependent("Depends Upn SCR ID") = rsTempRecord("Dependency")
                    rsDependent.Update
                    rsDependent.MoveNext
                End If
                rsTempRecord.MoveNext
            Wend
        End If
       
       
        'Close RecordSets
        rsTempRecord.Close
        rsNew.Close

        'Close Forms
        DoCmd.Close acForm, "UserCreateSCRSub"
        DoCmd.Close acForm, "CreateSCRSub", acSaveYes
 
       
        'Mark State as Closed
        Set rsChangeRequest = dbsMSCR.OpenRecordset("Change Requests", dbOpenDynaset, dbSeeChanges)
        rsChangeRequest.FindFirst "[SCR ID] = " & mySCRID
        rsChangeRequest.Edit
        rsChangeRequest("State") = enumCreated
        rsChangeRequest.Update
       
        rsChangeRequest.Close
        Set dbsMSCR = Nothing
       
'Error Handeling
Exit_SaveSCR:
    Exit Sub

Err_SaveSCR:
    MsgBox Err.Description
    Resume Exit_SaveSCR
       
End Sub
_____________


Thanks
0
dtbaker
Asked:
dtbaker
  • 4
  • 4
1 Solution
 
shanesuebsahakarnCommented:
Might be to do with the time needed for the back end database to create/update the autonum. You could try something like:
DoCmd.RunCommand acCmdSaveRecord
While IsNull([Forms]![CreateSCRSub]![SCR ID])
Me.Refresh
Wend

The Me.Refresh line may or may not be needed, but try it out and see if it helps.
0
 
johnj11Commented:
please shane you are so intelligent i need your help
0
 
dtbakerAuthor Commented:
Sorry Shanesuebsahakarn,
Gives an endless loop. :(
Just the Me.Refresh doesn't work either.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
shanesuebsahakarnCommented:
Very odd, it would seem that the data isn't being committed. I don't suppose replacing the doCmd with:
Me.Dirty = False

makes a difference ?
0
 
dtbakerAuthor Commented:
Sorry Shanesuebsahakarn,
Gives an endless loop. :(
Just the Me.Refresh doesn't work either.
0
 
dtbakerAuthor Commented:
I tried Me.Dirty = False before to save the record and I get a totally different error in both Access xp and 2000.
Got this error message "You entered an expression that has an invalid reference to the property Dirty."
0
 
shanesuebsahakarnCommented:
Ah, where is your code being run from ? Is it in a form module, or a general code module ?

You could try using the fully qualified path:
[Forms]![CreateSCRSub].Dirty = False
0
 
dtbakerAuthor Commented:
Shanesuesahakarn,

Using the fully qualified path worked.  Thanks for you quick and simple solutions.
0
 
shanesuebsahakarnCommented:
NP, glad I could help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now