Assign values from the last record

[aian]

I was looking for a way to assign values to fields from a previous record.

After trying Microsoft's solution (that didn't work), I searched and found this article which worked like a charm.

The only problem is that when I use the code in a form that has had no previous records, Access pops up an error message saying that there are no previous records found (logically...), after which (when pressing OK) everything works perfectly. The above page says:

If you do not wish to see any error messages, you could just set the Before Insert property of the form to:
=CarryOver([Form], "")

The problem is that I don't know where to insert this line. If I insert it in the BeforeInsert property, the actual code in step 9 of the page above doesn't work anymore.

I contacted Allen Browne via email but he responded that they are not able to provide support for all the free stuff on the website

So my question is where exactly should I insert the =CarryOver([Form], "") command?

And is it supposed to be inserted exactly as it is written, or do I have to replace the [Form] with the actual name of the form I'm inserting it to?

Thank you in advance

 

[RuralGuy]

In the BeforeInsert property of the form put:
=CarryOver(Me, "")

 

[aian]

Thanks RuralGuy, but in the BeforeInsert property of the form I have already put [Event Procedure] as per the instructions on step 7 of the page article I mentioned. Should I delete that?

 

[RuralGuy]

Thanks RuralGuy, but in the BeforeInsert property of the form I have already put [Event Procedure] as per the instructions on step 7 of the page article I mentioned. Should I delete that?

YES. Replace it with what I posted.

 

[aian]

OK, I replaced it. And when I hit enter to save the change, access pops up an error message saying "the expression you entered contains invalid syntax"

I also entered the same without the "=" at the beggining and even though access accepted that, when I tried to use the form, Access says cannot find the macro CarryOver(Me, "")

What am I doing wrong?

 

[RuralGuy]

Did you put the other code in a standard module as requested and not in the form's module?

 

[aian]

Yes, of course. And then I edited the BeforeInsert property exactly as it is said in the page, putting:

    Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim strMsg As String
        Call CarryOver(Me, strMsg)
        If strMsg <> vbNullString Then
            MsgBox strMsg, vbInformation
        End If
    End Sub

as mentioned at steps 6-10. See screenshot here.

I have followed the steps exactly as provided in the page. The only different thing I did was to change the name of the module (step 4): instead of "module1" I named it "COPY_VALUE_FROM_PREV_REC". (hope that doesn't affect the functionality of the code anywhere).

Why doesn't Access accept the =CarryOver(Me, "") ?

Here's the error's screenshot when I enter =CarryOver(Me, "") and hit enter on the keyboard. It reads "the expression you entered contains invalid syntax" (sorry for the Green screenshot).

If I am not doing something wrong, do you think there is some other way to import the error suppressing function in the form? With a macro to be run on load maybe? (just guessing, I don't know).

Thank you again for your time RG...

 

[RuralGuy]

I'm going to take a different approach here. I assume the GREEN message is the error you want to supress.

    'Find the record to copy, checking there is one.
    If Not bCancel Then
        Set rs = frm.RecordsetClone
        If rs.RecordCount <= 0& Then
            bCancel = True
            [COLOR="Green"]strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no recrods." & vbCrLf[/COLOR]
        End If
    End If

These messages are printed in your form after returning from the CarryOver procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim strMsg As String
        Call CarryOver(Me, strMsg)
        [COLOR="Green"]If strMsg <> vbNullString Then
            MsgBox strMsg, vbInformation
        End If[/COLOR]
    End Sub

I would choose to test for no records and not call the CarryOver procedure in your form:

Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim strMsg As String
        [COLOR="Green"]If Me.Recordset.RecordCount > 0 Then
           Call CarryOver(Me, strMsg)
           If strMsg <> vbNullString Then
               MsgBox strMsg, vbInformation
           End If
        End If[/COLOR]
    End Sub

 

[aian]

It works greatly!

Thank you, thank you, thank you!!!!!

 

[RuralGuy]

Great! Glad I could help.