autofill field based on previous value

[ccox]

I am trying to autofill a field based on the value of a previous field on a filtered form. I think the fact the records are filtered is throwing me off. Any help for me?

 

[RuralGuy]

What do you have so far and what is it doing?

 

[ccox]

I was creating a text box to work with the following, provided by the Microsoft site. I really need the value to be updated in my bound field. Also, it was providing a value based on the previous record, not the previous filtered record. If my current field is "Partnership", I want the new record to return this value. It was returning "Direct" which was the value in the previous unfiltered record.

Function AutoFillNewRecord (F As Form)
Dim RS As Dynaset
Dim I As Integer, RetVal
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if we are not on the new record.
RetVal = F.Bookmark
If Err = 0 Then Exit Function
Err = 0

' Go to the last record of the form recordset (to autofill from).
Set RS = F.Dynaset
RS.MoveLast

' Exit if we cannot move to the last record (no records).
If Err <> 0 Then Exit Function

' Get the list of fields to auto fill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

' If there is no criteria field, then set flag indicating
' ALL fields should be auto filled.
FillAllFields = Err <> 0

DoCmd Echo False

' Visit each field on the form.
For I = 0 To F.Count - 1
' Fill the field if ALL fields are to be filled OR if the
' ControlSource field can be found in the FillFields list.
If FillAllFields Or InStr(FillFields, ";" & F(I).ControlName _
& ";") > 0 Then
F(I) = RS(F(I).ControlSource)
End If
Next

DoCmd Echo True

End Function

 

[RuralGuy]

The code you posted is similar to How to fill record with data from previous record automatically in Access 2000, but not close enough. What is a Dynaset? You should be using the RecordSetClone of the form so you have the same filter.

 

[ccox]

When I open my form it gives me a compiler error and highlights the "Dim RS As DAO.Recordset"

 

[RuralGuy]

While viewing this code go to Tools>References and scroll down to Microsoft DAO 3.x Object Library and check it. Then recompile.

 

[ccox]

Thanks, that took care of the error. However, the value in my new record is still the value from the previous unfiltered record. My filter is for "Partnership" and I am currently on record 5. I want my new record to provide "Partnership. The new record will be #7. Since record 6 is "Direct", that is the value I get for the new record.

 

[RuralGuy]

Could you post a stripped down version of your db with enough dummy data records to demonstrate the problem?

 

[ccox]

I actually solved the problem with your help by adding a line to the code:
FillFields = ";Deal_Type;"
Thank you so much, forgot to send the update!

 

[RuralGuy]

Glad you got it working and thanks for posting back with your success.