Results 1 to 7 of 7
  1. #1
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92

    Values in array not staying once new record created on a form

    Hi everyone. I've created a button on one of my forms, for the purpose of copying some data on the current record into a new record. I have created several variables to store the data that exists on the current form, which will then paste that data into the new record, when it becomes the current one. I have an array for storing multiple values, to save me from typing more code and creating even more variables. However, the value stores inside my arrays is all turning to Null once I create the new record.

    Code:
    Private Sub DuplicateButton_Click()
    
    Dim JobID_Old As Integer
    Dim WeekEnding_Old As Date
    Dim Call_Old As Variant
    Dim M1Out_Old As Variant
    Dim M1In_Old As Variant
    Dim M2Out_Old As Variant
    Dim M2In_Old As Variant
    Dim Wrap_Old As Variant
    Dim CallControl As String
    Dim M1OutControl As String
    Dim M1InControl As String
    Dim M2OutControl As String
    Dim M2InControl As String
    Dim WrapControl As String
    Dim Counter As Integer
    
    If Me.Dirty Then Me.Dirty = False
    
    'this reads all the data off the current time card that I will want to appear in the new time card record.
    Call_Old = Array(Me.D1Call, Me.D2Call, Me.D3Call, Me.D4Call, Me.D5Call, Me.D6Call, Me.D7Call)
    M1Out_Old = Array(Me.D1Meal1Out, Me.D2Meal1Out, Me.D3Meal1Out, Me.D4Meal1Out, Me.D5Meal1Out, Me.D6Meal1Out, Me.D7Meal1Out)
    M1In_Old = Array(Me.D1Meal1In, Me.D2Meal1In, Me.D3Meal1In, Me.D4Meal1In, Me.D5Meal1In, Me.D6Meal1In, Me.D7Meal1In)
    M2Out_Old = Array(Me.D1Meal2Out, Me.D2Meal2Out, Me.D3Meal2Out, Me.D4Meal2Out, Me.D5Meal2Out, Me.D6Meal2Out, Me.D7Meal2Out)
    M2In_Old = Array(Me.D1Meal2In, Me.D2Meal2In, Me.D3Meal2In, Me.D4Meal2In, Me.D5Meal2In, Me.D6Meal2In, Me.D7Meal2In)
    Wrap_Old = Array(Me.D1Wrap, Me.D2Wrap, Me.D3Wrap, Me.D4Wrap, Me.D5Wrap, Me.D6Wrap, Me.D7Wrap)
    JobID_Old = Me.JobID_notFK
    WeekEnding_Old = Me.WeekEnding
    
    'this creates the new record
    DoCmd.GoToRecord , , acNewRec
    
    'this writes the data to the new record
    Me.JobID_notFK = JobID_Old
    Me.WeekEnding = WeekEnding_Old
    
    For Counter = 0 To 6
    
        CallControl = "D" & Counter + 1 & "Call"
        M1OutControl = "D" & Counter + 1 & "Meal1Out"
        M1InControl = "D" & Counter + 1 & "Meal1In"
        M2OutControl = "D" & Counter + 1 & "Meal2Out"
        M2InControl = "D" & Counter + 1 & "Meal2In"
        WrapControl = "D" & Counter + 1 & "Wrap"
        
        Me.Controls(CallControl) = Call_Old(Counter)
        Me.Controls(M1OutControl) = M1Out_Old(Counter)
        Me.Controls(M1InControl) = M1In_Old(Counter)
        Me.Controls(M2OutControl) = M2Out_Old(Counter)
        Me.Controls(M2InControl) = M2In_Old(Counter)
        Me.Controls(WrapControl) = Wrap_Old(Counter)
    
    Next
    
    End Sub
    The data that is stores inside JobID_Old and WeekEnding_Old stays inside those variables and is properly placed on the new record, just as intended. As for the arrays, the data does go inside the arrays as planned (I stepped through the code and watched those variables in the Watch window), but as soon as the line
    Code:
    DoCmd.GoToRecord , , acNewRec
    is executed, the data inside the arrays all becomes Null. I don't understand why this would be happening. Why does it not hold the values like the JobID_Old and WeekEnding_Old variables do?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Array() is an intrinsic function. The function parameters are referencing form controls. You move to new record. The function resets content of memory - just as it does for each call of the function in the procedure.

    This should not be an issue. The function has done its job to set the variables.
    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
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    Quote Originally Posted by June7 View Post
    Array() is an intrinsic function. The function parameters are referencing form controls. You move to new record. The function resets content of memory.
    Ah, I see. So then I guess I can't do what I'm trying to do with an array. Is there anything similar I could do, or do I have to create a whole bunch more variables to store the value of each textbox individually before creating the new form?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand. You said the new record is populated. You are using array object variables and you are populating them with the Array() function. Therefore, you are using arrays.

    The content of your declared array objects should not be altered just by moving to another record.

    So what is not happening that you need to happen?
    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
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I think I figured it out. I think it is just a matter of syntax. I think I am creating the array the wrong way. Instead of the way I did it initially, I am now doing it this way:

    Dim Call_Old(0 To 6) As Single

    When doing it that way, it seems to hold the value once I create the new record. I need to change some more of my code to test it completely, but on first glance, this seems to work.

  6. #6
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    Yep, after making those changes, it all works now. So I was using the wrong syntax to create the array. I had seen it to an answer to a question on some other forum, but obviously it wasn't the correct way to do it for what I needed.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did some testing and do get that behavior. Hadn't expected. However, trying to use Array() to populate array object with defined boundaries and getting an error. Would you like to share working code?
    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. Replies: 2
    Last Post: 03-13-2015, 07:52 AM
  2. Replies: 7
    Last Post: 04-17-2012, 11:53 AM
  3. Replies: 2
    Last Post: 03-28-2012, 02:41 PM
  4. Autofilling when New Record Created from Form
    By SpdRacerX in forum Forms
    Replies: 6
    Last Post: 01-24-2012, 10:24 AM
  5. Staying on Same Record
    By mwabbe in forum Access
    Replies: 12
    Last Post: 09-02-2010, 08:28 AM

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