?
Solved

DoCmd.RunCommand acCmdSaveRecord returns Invalid use of Null

Posted on 2003-03-03
9
Medium Priority
?
538 Views
Last Modified: 2010-05-18
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
Comment
Question by:dtbaker
[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
  • 4
  • 4
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8059777
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
 

Expert Comment

by:johnj11
ID: 8060505
please shane you are so intelligent i need your help
0
 

Author Comment

by:dtbaker
ID: 8060738
Sorry Shanesuebsahakarn,
Gives an endless loop. :(
Just the Me.Refresh doesn't work either.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8060868
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
 

Author Comment

by:dtbaker
ID: 8060914
Sorry Shanesuebsahakarn,
Gives an endless loop. :(
Just the Me.Refresh doesn't work either.
0
 

Author Comment

by:dtbaker
ID: 8060927
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 200 total points
ID: 8060938
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
 

Author Comment

by:dtbaker
ID: 8065703
Shanesuesahakarn,

Using the fully qualified path worked.  Thanks for you quick and simple solutions.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8065729
NP, glad I could help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…
Suggested Courses

800 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