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.

 

[Pat Hartman]

Forcing Access to save the record in the click event of the new button is illogical and possibly the root of your problem.

 

[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

 

[Pat Hartman]

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.

If your form is bound to Jet or ACE, as soon as you type in the first character to dirty the form, you will see the autonumber populate. However, Access does not have such a tightly bound relationship with SQL Server or other RDBMS', therefore, you will not see the autonumber until the record is saved. BUT, you should not be saving the record before it is complete. It sounds like you want to start the main form record and then before finishing it, move to entering a subform record. That is an illogical workflow. You need to either change your BeforeUpdate code to allow incomplete records to be saved --- really poor practice. Or, change the logic of the workflow so that the parent record can be completed before the child records are entered.

Sometimes you have business logic that requires a certain set of child records in order for the parent record to be valid. The solution for that is to add an ActiveYN column to the parent table. The default is no. This control should always be locked since it isn't the user who decides whether the data is valid. In the AfterUpdate event of the child record, run the validation code that determines if the set is now complete.. If it is, then run an update query to update the ActiveYN flag on the parent table to true and requery the main form. To close the loop, the rest of the application needs to ignore any record where ActiveYN = false. Only the data entry form should ever show the inactive records. You also probably want to run a query on startup looking for inactive records so you can prompt users to complete the record or delete it.

 

[Pat Hartman]

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.

A tab is just a control on a form. Moving to a new tab does not cause Access to move focus to a different record. The only time moving to a new tab will force a record to save is if the new tab contains a subform control AND you place focus into the subform.

Access automatically saves in a number of instances.
1. Close the form
2. Scroll to a different record
3. Click from mainform into a subform or vice versa
4. Close the application.

One situation where you MUST force a save is if you use code to open a different form or report. This is especially important if you want the new form/report to reflect data from the source form. So, immediately prior to any DoCmd.OpenForm or DoCmd.OpenReport, save the current record. If the record is not dirty, nothing will happen. If the record is dirty, Access will save the current record.

 

[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.

 

[Pat Hartman]

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

I wouldn't recommend it. Requery forces Access to save the current record. So, looks like your code saves the record whether it is a duplicate or not. In one path of the If, you save using Requery, in the other, you use the better method of an explicit save and for some reason, save twice. Saving a record that has only a PK is really poor practice. Every record should have at least one required field in addition to the primary key and the validation should occur in the Form's BeforeUpdate event so that you can cancel the save if there is any invalid or missing data in the record.

 

[topdesk123]

Duplication is avoided here:

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

 

[Pat Hartman]

Duplication is avoided here:

Except that Me.Requery SAVEs the duplicate record. Cancelling the button click does not do anything.

If you have a unique index on the ProjectNo, that would reject the duplicate but your code is NOT stopping the duplicate from being saved.

Validation of this type, to be effective, must occur in an event that has a Cancel event. I would do the validation in the Form's BeforeUpdate event. If you are going to generate the next number yourself rather than use an autonumber, you should generate the sequence number at the end of the code in the form's BeforeUpdate event. If a duplicate is generated, loop to generate a new number. Make sure the loop doesn't become endless by limiting the number of retries to 5. If that many duplicates are generated, there is something else wrong.

If you'd like to learn more about validation, these videos will be worth your time.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk