Results 1 to 10 of 10
  1. #1
    hariswk is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    4

    Avoiding empty table entries from a form when using autonumbers

    Hello,



    I think my best solution is to create an unbound form, which will be more work now, but I wanted to ask:

    I have an entry form which I want to save only with a save button, and otherwise, do not. It has an autonumber I'd like to remain consistent. I've tried the following:

    Code:
     [Private Sub Form_Unload(Cancel As Integer) 'this undoes changes to forms before it is closed/unloaded
        If MsgBox("This entry hasn't been saved. Are you sure?", vbYesNo + vbDefaultButton2, "Confirmation") = vbNo Then
            Cancel = True
            Else
            Me.Undo
        End If
    End Sub
    However, I think this leaves a blank because an auto number is assigned once any textbox is updated. Using if me.dirty then DoCmd.RunCommand acCmdUndo in place of me.undo in the code above works, but it but deletes the autonumber entry entirely

    I've tried preventing form updates via the following (where buttonpressed = true when save button is clicked):
    Code:
    Private Sub Form_Current()
    ButtonPressed = False
    End Sub
     
    Dim ButtonPressed as Boolean
    Private Sub Form_BeforeUpdate(Cancel As Integer) 'Cancel update, unless buttonpressed (save button) = true'
        If ButtonPressed = False Then Cancel = True
    End Sub
    But this causes issues when I have to reference control.text instead of control.value for some other vba i have filling out the text boxes. Does anyone have any thoughts on this? Thank you so much for any help. I'm new to access and hopefully can contribute to answering here some day.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, if you care about the missing autonumber you're probably misusing it. You are correct that it will burn a number when you cancel the update, but the autonumber is just a unique identifier. Some will argue against it ever being visible to the user. I won't, but you can't care that a number gets skipped.

    Not sure what you mean about the .Text property. You generally only use that in the change event of a control, when you need to examine the contents keystroke by keystroke. Typically that isn't needed very often. If you need to use it for some reason, you have to set focus to the control first.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us more about:
    -this proposed application in simple English
    -your requirement re numbering.

    You may find this article on Autonumbers helpful.

    Also, you may want to research the BeforeUpdate event for data and field filling validation before saving a record.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    No need for unbound form.

    1. Create a bound form;
    2. In form's BeforeUpdate event:
    2a. Check for new record;
    2b. When current record is a new record, check for obligatory fields/conditions;
    2b1. When check was passed, save the record;
    2b2. When check didn't pass, cancel update and display a messagebox with according info. The form is returned to state before you started the operation by default;
    2b3. User can edit the record anew, and then repeat the operation updating the form (Save, Next/Previous record, New record). Or user can press Esc which cancels new record and last active existing record is reactivated.

    You also can define unique indexes for form source table(s) which restrict saving records when fields the index(es) is/are composed, is/are empty.

    NB! So long new record is not saved, the autonumber is not applied - i.e. when you press Esc with new record active and not saved, the autonumber is not saved (and as follows, is unused) too.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Autonumber plays a weird psychological game with people that seems to trigger ocd but we have to just learn to get over it. To help my self get over it I just started setting the increment property to random, that did the trick for me... Ymmv

  6. #6
    hariswk is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    4
    Quote Originally Posted by orange View Post
    Can you tell us more about:
    -this proposed application in simple English
    -your requirement re numbering.
    I'm making an inventory form, where an entry is a single object being stored and later removed. I wanted to avoid skipping autonumbers, because when someone looks at the table, they might ask if entries were manually deleted/altered. However, I think I will follow the advice of pbaldy and kd2017. I can create a new field which adds a unique number to the entry only upon saving, and hide the autonumber I suppose.

  7. #7
    hariswk is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    Not sure what you mean about the .Text property. You generally only use that in the change event of a control, when you need to examine the contents keystroke by keystroke. Typically that isn't needed very often. If you need to use it for some reason, you have to set focus to the control first.
    Quote Originally Posted by ArviLaanemets View Post
    2b1. When check was passed, save the record;
    2b2. When check didn't pass, cancel update...
    So, I have a checkbox that is supposed to auto-fill the date. To do so, I had the following code:
    Code:
    Private Sub date_time_check_Click()
    If date_time_check.Value = False Then
        Date_Added.Value = ""
        Else
        Date_Added.value = Format(Date, "Medium Date")
    End If
    End Sub
    By editing the value, isn't this an "update"? Perhaps I might be having other issues, but it did not work when using the cancel update system you propose. Editing "Date_Added.Text" (with .setfocus before hand) also error-ed
    Last edited by hariswk; 05-16-2020 at 02:50 PM. Reason: misprinted code

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    Code:
    If date_time_check.Value = False Then    
      Date_Added = Null
    Else
      Date_Added = Date()
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    hariswk is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    4
    Works great! I should have thought of that. Thank you pbaldy and everyone for your suggestions, I will read into these topics. I really appreciate it

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to use a Form to Make entries on a Table
    By egnsantiago in forum Access
    Replies: 8
    Last Post: 02-01-2019, 08:04 PM
  2. Form creating empty records in source table
    By Beginner72 in forum Access
    Replies: 4
    Last Post: 10-05-2017, 11:01 AM
  3. Replies: 4
    Last Post: 03-10-2014, 11:33 AM
  4. Replies: 5
    Last Post: 06-13-2013, 08:42 AM
  5. Replies: 4
    Last Post: 11-20-2011, 01:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums