Adding a new record and getting ID

[ClaraBarton]

This isn't working so smoothly. I need an ID on a new record before I can add to the subform but the BeforeUpdate kicks in and won't let me move on because all fields aren't complete.

Private Sub btnNew_Click()
    On Error GoTo btnNew_Click_Error
      
    If Me.Dirty Then
        Me.Created = Date
        Me.Dirty = False
    End If

   With Me.txtRecipe
        .BorderStyle = 1
        .SetFocus
   End With
   AddNewRecipe
On Error GoTo 0
Exit Sub


Private Sub AddNewRecipe()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT * FROM t_recipe WHERE 1=0;"
    Set rs = db.OpenRecordset(strSQL)

    With rs
      .AddNew
      ![createdate] = Date
      .Update
      .Bookmark = .LastModified
    End With

Dim bDelete As Boolean
'Dim strSQL As String

bDelete = False

'If previous new record is already there or they tried
'to delete just the name or they forgot the cookbook
    If (Me!Recipe = "<New Recipe>") Or _
        (Me!Recipe = "") Or _
        IsNull(Me.cookbookidfk) Then
            If MsgBox("Recipe Name and Cookbook are required. Do you want to " _
                & "discard this new record?", vbOKCancel) = vbOK Then
            bDelete = True
        Else
            Cancel = True
        End If
    End If

Is there a simple fix here?

 

[CJ_London]

You’ve set the rules so either comply with them or change them

I don’t see a before update event so no idea what it might be doing

 

[ClaraBarton]

I don't want to save a record without the fields filled but before I can fill them I need an ID. The before update is the second code. I need both. Doesn't seem unreasonable.

 

[Cronk]

I need an ID

(a) You could add an autonumber ID to the table so that it is automatically created.

(b) If you want some other unique ID, you could add one to the last ID, or generate a random number but checking first it does not exist already.

(a) is easiest and would be what most developers would use.

 

[The_Doc_Man]

If you have enabled Relational Integrity between parent and child and/or set the parent's field rules to Required = Yes for some of the fields, you have the issue that you cannot create an orphan record, yet you are trying to do exactly that. As you are discovering, when you try to change focus to your subform, Access MUST save the parent record, but without a parent ID you cannot do that. Cronk's Autonumber suggestion is commonly done. Rethink your form to take into account that you MUST have an ID field filled in if you have an enforced relationship there. And a further bit of advice: Trying to think of a way around the requirement is the wrong path.

 

[ClaraBarton]

I have autonumber and relational integrity enabled. So explain to me how to do this... I thought the record would be saved and an ID shown when I moved to another tab on the form but it isn't. I set the save on new to get an ID.

 

[Cronk]

With rs
      .AddNew
      ![createdate] = Date
     
       YourID=rst!ID

      .Update
      .Bookmark = .LastModified
    End With

If ID is an autonumber, you can save it before the update

 

[topdesk123]

This is what I use to assign a new project number: Maybe it can work for you?

Private Sub NewProjbtn_Click()

DoCmd.GoToRecord , , acNewRec
Me.CreateDate = Date

If DCount("*", "[Projects]", "[ProjectNo] = '" & Me.ProjectNo & "'") > 0 Then
MsgBox "This project number already exists"
Me.Requery
DoCmd.CancelEvent
Else
DoCmd.RunCommand acCmdSaveRecord
Me.ProjectNo = DLast("usenumber", "usenumber") ***It looks up the last number used here***
DoCmd.RunCommand acCmdSaveRecord
Me.BidDate.SetFocus
End If
End Sub

 

[jdraw]

You may get some insight from this thread re Next Autonumber. But read carefully the comment about multi-user environment.

 

[topdesk123]

Duplication is avoided here:

If DCount("*", "[Projects]", "[ProjectNo] = '" & Me.ProjectNo & "'") > 0 Then
MsgBox "This project number already exists"
Me.Requery
DoCmd.CancelEvent