Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2019
    Posts
    8

    Update or CancelUpdate without AddNew or Edit -- 2019

    I am having major issues with my subform. I tried a different thread and didn't get results.
    https://www.accessforums.net/showthread.php?t=3470

    I'm so annoyed that I cannot get my subform to properly allow me to add records without interruptions for the user. I have a query based subform in datasheet view form. I have two tables within this query which both have a primary key, one actually has two primary keys. I keep getting #error if the record is not properly filled out the first go. If a user tries to submit record it errors out if required fields are not meeting those parameters. I need a way for my query to know to allow user to continue to edit. I need it to continue in "edit" mode and not accept submission of record so it can reserve the generated autonumbers that are being created. I have several required fields in my details table but if they do not fill it out properly they shouldn't have to click ESC to clear the new record out just to restart entering it all over again. It's annoying for the end user. Any work arounds would be great.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Normally a form should do data entry/edit for only one table. Why are you including two tables in RecordSource?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Nov 2019
    Posts
    8
    I'm a newbie is why. hahaha

    I have four tables. For the moment I'm only utilizing three for this form. tbl_customer, tbl_quote,tbl_quotedetails. I cannot get it to attach for you to perform an analysis. Probably company policy. No, my goal is to have a excel layout subform in my main form that is filtered by my combobox that has the customer account selection.

    The purpose is to to create a new quote (Table:tbl_quote] against that specific customer [Table:tbl_customer] but simultaneously being able to create the 1st record of detail [Table:tbl_quotedetails] for the new quote. Detail table is for records to be added against a autonumber quote field in the quote talbe. Our quotes are created by multiple fields (Our initials, sales initials, quote sequence number, and _0: So ie; XX_XX_1234_0. All revisions will change from _0 to _1,_2 and so forth).
    Attached Thumbnails Attached Thumbnails errormessage.PNG   New Quote after customer is selected.PNG   New Quote.PNG  

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    QuoteDetails needs to be in its own form. Can nest subforms up to 7 deep.

    Why do you have a compound primary key? Suggest you avoid this if at all possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary in this link.
    The tutorial will lead you through a process to move from a "business description" to a properly normalized design/a blueprint for a database. You can then use that process to design your database. You will learn about tables and relationships and design in ~30 minutes if you work through one of the tutorials.
    There are other articles in that link that may be useful to you.
    Good luck.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    You must have a separate form based on customers table where you register/edit your customers.

    Then you create an unbound main form (single form!) with unbound Combo Box where you can select customer (Optionally you can use customers form as main form too - in this case customers form must be a single form). You also add a hidden unbound text box (either into unbound form, or into customers form), where quote id of currently active quote will be written by code .

    You create a continuous form based on your quotes table (quotes form). Controls linked to quote id and customer id fields in quotes table is best to set invisible.

    You create a continuous form based on your quote details table (quote details form). Controls linked to quote detail id and quote id fields in quote details table is best to set invisible.

    You add quotes form into main form as subform. When you used unbound form as main form, you use combo in main form as link Master field for subform, and customer id field as link Child field. When you use customers form as main form, customer id field will be both link Master and child fields. You write an OnCurrent event for quotes form (the source object of subform), which writes id of current quote in suborm into unbound text box in main form.

    You add quote details form as another subform into main form, and link it with unbond text box in main form (depending on size of forms, you can place one subform to left side of main form, and another to right side, or you place details subform beneath quotes subform, or on separate pages of tab control, etc.).

    Now, whenever user selects a customer in main form, all quotes for this customer are displayed in quotes subform. And all details for active quote in quotes subform are displayed in quote details subform. And when you add a new quote o quote detail row into appropriate subform, this entry will be automatically linked to active customer/quote.

  8. #8
    Join Date
    Nov 2019
    Posts
    8
    Hello All.

    So I was fiddling around with the project I'm working on yesterday and came across a solution with the beforeupdate event. The only issue I am now having is my record is reset and values that were entered by the user are no longer there. Is there a way to prompt to save record and if the user clicks no then instead of cancelling new record it allows the user to continue modifying that record?

    Recordsource for my main form is my CUSTOMER TABLE.
    Recordsource for my subform on the main form is a query that includes two different tables; QUOTE TABLE AND QUOTEDETAILS TABLE.

    QUOTE TABLE has CUSTOMERID field with a relationship connecting back to CUSTOMER TABLE.
    QUOTEDETAILS has QUOTEID field with a relationship connecting back to the QUOTE TABLE.

    My subform’s purpose is only for data entry of a new quote# that will include a new record (original record) that will be created also in the quotedetails table. So simentanesouly they are creating two new records (from separate tables behind the scenes) when creating a new “quote” on this form.

    I created a save record prompt on the beforeupdate event. The issue I am now having is the values the user already inputted are being reset if they click no.


    Here is my code in the beforeupdate event. Maybe it’s something in this code because I found it online and unclear what the heck I’m doing.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
       ' This procedure checks to see if the data on the form has
       ' changed. If the data has changed, the procedure prompts the
       ' user to continue with the save operation or to cancel it. Then
       ' the action that triggered the BeforeUpdate event is completed.
     
       Dim ctl As Control
     
       On Error GoTo Err_BeforeUpdate
     
       ' The Dirty property is True if the record has been changed.
       If Me.Dirty Then
          ' Prompt to confirm the save operation.
          If MsgBox("Note:" & vbCrLf & "If all REQUIRED (*) FIELDS are not filled out new records will not be saved after the YES button is selected below. If an error message displays under the QUOTEID field after record has been saved push ESC and start over." & vbCrLf & vbCrLf & ">> Please click YES to SAVE NEW RECORD." & vbCrLf & vbCrLf & ">> Please click NO to cancel and REVIEW your record BEFORE saving.", vbYesNo + vbQuestion, _
                  "Save Record") = vbNo Then
             Me.Undo
          End If
       End If
     
    Exit_BeforeUpdate:
       Exit Sub
     
    Err_BeforeUpdate:
       MsgBox Err.Number & " " & Err.Description
       Resume Exit_BeforeUpdate
    End Sub
    Last edited by bw.brandiwoodson; 12-05-2019 at 07:24 AM. Reason: TYPO

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you don't want the record committed but not remove values entered, then trying removing Me.Undo.

    Use Cancel = True to prevent committing edits to table.

    Testing for Dirty is not necessary - BeforeUpdate event only triggers if record is dirty.

    Review https://docs.microsoft.com/en-us/off...reupdate-event
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Nov 2019
    Posts
    8
    Quote Originally Posted by June7 View Post
    If you don't want the record committed but not remove values entered, then trying removing Me.Undo.

    Use Cancel = True to prevent committing edits to table.

    Testing for Dirty is not necessary - BeforeUpdate event only triggers if record is dirty.

    Review https://docs.microsoft.com/en-us/off...reupdate-event
    Thank you so much this helped a great deal. It's now working. How do I get the prompt message to display custom wording? Instead of "Yes" or "No" I wanted it to say "Save Record" and "Go back and review record before submitting". Is this possible vs the standard yes and no displayed text?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A MsgBox can display whatever text you want in the prompt. But the response choices must be from a defined set and cannot be customized. Prompt wording directs user choice.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Update or CancelUpdate without AddNew or Edit
    By westcoastbmx in forum Forms
    Replies: 5
    Last Post: 11-07-2019, 09:01 PM
  2. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  3. Replies: 1
    Last Post: 07-18-2015, 07:59 PM
  4. Replies: 9
    Last Post: 04-01-2011, 10:31 AM
  5. Replies: 3
    Last Post: 03-07-2011, 08:37 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