Results 1 to 3 of 3
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Save Button VBA - Saving Multiple Days to Conference in DB overwrites previous save


    Hi everyone,

    I have a Save button whose code, I'll admit, is superfluous, but I can't figure out how to make it work how I want. Further, can you please take a look at my If MsgBox statement and tell me why that, while it DOES save, when the user selects "YES" to add a day, it saves over the previously saved day.

    Example: I have a 3-day conference. Person A is going to Conference XYZ on Days 2 and 3 only.
    I click the button to add my person to the conference, select Day 2, and hit Save. Works perfectly.
    I get the popup asking if I want to add additional days and click Yes. I select Day 3. It overwrites the Day 2 I had previously put in; there's gotta be something I'm missing in my VBA. Is it obvious to anyone? Thanks!

    Code:
    Private Sub btnSave_Click()
    
        DoCmd.RunCommand acCmdSaveRecord 'I previously had DoCmd.Save; changed only to see if this made a difference--doesn't
        Forms![Conference Search].Refresh 'This allows the "Conference Search" form to refresh, making it apparent which days the person is attending
    
        If CurrentProject.AllForms("Person Search").IsLoaded = True Then
             Forms![Person Search].Refresh 'This allows my "Person Search" form to  refresh, so if anyone's looking at that form, it's immediately obvious  that the person went to that conference on that day.
        End If
    
        If MsgBox("Do you want to add your person to any other conference days?", vbYesNo, "Add Additional Days?") = vbYes Then
            DoCmd.RunCommand acCmdSaveRecord
            cboDays.Available.SetFocus
        Else
            DoCmd.Close , ""
            Forms![Conference Search].Refresh
            If CurrentProject.AllForms("Person Search").IsLoaded = True Then
                Forms![Person Search].Refresh
            End If
        End If
    
    End Sub
    Other than the obvious re-statement of my code (which it seems I have to do to get the refreshes working properly), is there anything overt you can see as to why my Save "works" but when I want to add an additional day, it will just overwrite that previous save?

    Thank you!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is your table structure? Are the days stored on another table? If so, each subsequent day would be an append.

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    I see what I was doing. I never said to open a new record each time I saved, so it was staying on whatever static record it had opened and just re-writing. I fixed this by adding in the following bit of code in the msgbox dialog box (changes in red, if it shows):

    <It's pulling IDs from a query, which is doing a search on a different form for all persons in the db; when I load up the form I'm talking about below, it's pulling in the "Person_ID_FK" and the "Conference_ID_FK" from the respective values on the Person Search form.>

    Code:
    ...
    If MsgBox("Do you want to add your person to any other days?", vbYesNo, "Add Additional Days") = vbYes Then
        Dim PersonID As String 'Set a variable for PersonID
        Dim ConferenceID As String 'Set a variable for ConferenceID
        PersonID = Me.Person_ID_FK 'I took the current value of the "Person_ID_FK" and set it to a variable, so that it can paste it in to the appropriate box whenever I create a new record
        ConferenceID = Me.Conference_ID_FK 'I took the current value of the "Conference_ID_FK" and set it to a  variable, so that it can paste it in to the appropriate box whenever I  create a new record
        DoCmd.GoToRecord , , acNewRec 'Create a new record; this will blank out the aforementioned Conference_ID_FK and Person_ID_FK fields because now the form isn't pulling those fields from the previously-opened Person Search form
        Me.Conference_ID_FK = Conference ID 'Set the Conference_ID_FK field to the variable saved above
        Me.Person_ID_FK = PersonID 'Set the Person_ID_FK field to the variable saved above
        cboDaysAvailable.SetFocus
    Else
        DoCmd.Close , ""
        Forms![Conference Search].Refresh
    ...

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

Similar Threads

  1. Replies: 6
    Last Post: 11-19-2017, 08:41 PM
  2. Replies: 6
    Last Post: 11-14-2013, 07:37 AM
  3. Replies: 8
    Last Post: 09-27-2012, 11:12 AM
  4. Replies: 7
    Last Post: 10-28-2011, 03:42 AM
  5. VB coding for saving when click on save button in form
    By cwwaicw311 in forum Programming
    Replies: 1
    Last Post: 02-04-2010, 11:11 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