Code Help to make a Check Box Functional

[loonette]

I have created a continuous data entry form to enter information in. I use the following code to make certain information entered in the previous record repeat in the next record.

Private Sub Form_Current()
Dim rs As Object
    Set rs = Me.Recordset.Clone
 
    If rs.EOF Or Not Me.NewRecord Then
        ' don't do anything if there's no records or it is not a new record
    Else
        With rs
 
            .MoveLast
 
            Me![LotNumber] = .Fields("LotNumber")
            Me![Description] = .Fields("Description")
            Me![SalePrice] = .Fields("SalePrice")
        End With
 
    End If
 
End Sub

(Sorry, I haven't posted here in a while and I can't remember how to put the code in [code] tags)

I would like to add a checkbox to the form that when checked or unchecked would turn this code on or off. I have read through the forums and I can't find anything that is relevant to my situation. I have also played around with it myself, but I just don't know enough programming to figure it out myself. Any help will be greatly appreciated.

Thanks!
Lori

[ADezii]

There are other approaches as far as Carrying Over Values, but we'll stay with this one since you already have put work into it.

1. Declare a Private, Boolean Variable in the Form's Code Module. The Value of this Variable (True/False) will indicate whether or not the Code in the Form's Current() Event will be executed, and is Set in the AfterUpdate() Event of a Check Box (chkCarry).
   

   Private blnCarryValue As Boolean

2. Write Code in the AfterUpdate() of chkCarry that will Set the Value of the variable blnCarryValue.
   

   Private Sub chkCarry_AfterUpdate()
     blnCarryValue = chkCarry
   End Sub

3. Modify Code in the Form's Current() Event, and make it check the Value of blnCarryValue to see as to whether or not the Code should be executed.
   

   Private Sub Form_Current()
   Dim rs As DAO.Recordset
    
   Set rs = Me.RecordsetClone
    
   If blnCarryValue Then
     If rs.EOF Or Not Me.NewRecord Then
       ' don't do anything if there's no records or it is not a new record
     Else
       With rs
         .MoveLast
           Me![LotNumber] = .Fields("LotNumber")
           Me![Description] = .Fields("Description")
           Me![SalePrice] = .Fields("SalePrice")
       End With
     End If
   End If
    
   rs.Close
   Set rs = Nothing
   End Sub

[NeoPa]

This all sounds horribly more complicated than necessary Lori.

I would suggest the following idea :

Shove your code in the AfterUpdate event procedure of the form and simply set the .DefaultValue properties of the controls to the existing values. It's important to set only the .DefaultValue property, rather than the .Value as your code does, as otherwise you are dirtying the record immediately, even before the operator has had a chance to choose to do so. This is a point I find myself having to make frequently for some reason. Nevertheless it is quite a basic one.

I would think code similar to the following should do :

Private Sub Form_AfterUpdate()
    With Me
        If .NewRecord Then
            .LotNumber.DefaultValue = .LotNumber
            .Description.DefaultValue = .Description
            .SalePrice.DefaultValue = .SalePrice
        End If
    End With
End Sub

You can include code in the Open() event for the form too, if you deem it worth it (This depends on normal usage which we cannot tell).

If, when you run with this you still feel it necessary to add a CheckBox then let us know. It's not complicated, but may well prove redundant with the logic in the new place.

[ADezii]

@NeoPa:

I would like to add a checkbox to the form that when checked or unchecked would turn this code on or off.

Isn't the idea to use a Flag (via a Check Box) that will indicate whether or not the Value even gets carried over for the three Controls? Should the Flag evaluate to False, wouldn't the Default Values have to be Reset to Null so as to not carry them over to a New Record?

[NeoPa]

My guess was that the requirement, though clearly stated as you say, is simply a result of getting the main logic so wrong in the first place. As my last sentence indicates, this can be added in later if, after seeing how it should work, Lori still feels it is necessary. Sometimes (always really) it makes sense to strip away the complications first and get the basics right. Then one is in a better position to know what extras are required. Building on sand is not a good recipe for solid-standing structures.