Auto-filling new record with (some) values from previous record...

[Guest]

Hi,

I have a form that I'm using to enter a variety of data types: text,
date/time & Yes/No. The form has 87 fields, many of which are identical from
one consecutive record to the next (and in any series of records, many these
may be blank).

What would speed up data entry for me enormously would be to copy values
from the previous record into the new record automatically. My problem is
finding a way to achieve this for the Yes/No fields. I know essentially
nothing about visual basic, and I have little prior experience with Access
(I'm using Access 2003).

I have been able to do this for those date/time & text fields for which this
is appropriate using the info presented here: "How to fill record with data
from previous record automatically in Access 2000"
(http://support.microsoft.com/?kbid=210236), which creates an
AutoFillNewRecord() function:

------------------------
Function AutoFillNewRecord(F As Form)

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set RS = F.RecordsetClone
RS.MoveLast

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

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

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

F.Painting = False

' Visit each field on the form.
For Each C In F
' 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, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function
----------------------

....but haven't found an obvious way to make this work for the Yes/No values.
Any help gratefully appreciated!

Thanks!

 

[Allen Browne]

Here's one that takes a different approach:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

Instead of needing a list of all the controls to copy over, this one copies
all except for ones you specify as exceptions.

 

[Guest]

Thanks - I'll give it a shot!

Here's one that takes a different approach:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

Instead of needing a list of all the controls to copy over, this one copies
all except for ones you specify as exceptions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a form that I'm using to enter a variety of data types: text,
date/time & Yes/No. The form has 87 fields, many of which are identical
from
one consecutive record to the next (and in any series of records, many
these
may be blank).

What would speed up data entry for me enormously would be to copy values
from the previous record into the new record automatically. My problem is
finding a way to achieve this for the Yes/No fields. I know essentially
nothing about visual basic, and I have little prior experience with Access
(I'm using Access 2003).

I have been able to do this for those date/time & text fields for which
this
is appropriate using the info presented here: "How to fill record with
data
from previous record automatically in Access 2000"
(http://support.microsoft.com/?kbid=210236), which creates an
AutoFillNewRecord() function:

------------------------
Function AutoFillNewRecord(F As Form)

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set RS = F.RecordsetClone
RS.MoveLast

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

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

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

F.Painting = False

' Visit each field on the form.
For Each C In F
' 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, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function
----------------------

...but haven't found an obvious way to make this work for the Yes/No
values.
Any help gratefully appreciated!

Thanks!

 

[Guest]

Hi - followed the directions on the webpage, but can't get it to EXCLUDE
records?? Any idea why this might be? This is my list of variables I want to
exclude...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMsg As String
Call CarryOver(Me, strMsg, “Timeâ€, “BBactivityâ€, “BKactivityâ€,
“BOactivityâ€, “DNactivityâ€, “GSactivityâ€, “KTactivityâ€, “MAactivityâ€,
“MSactivityâ€, “NKactivityâ€, “TKactivityâ€, “ZFactivityâ€, “HWactivityâ€,
“FDactivityâ€, “BNactivityâ€, “CLactivityâ€, “FLactivityâ€, “HTactivityâ€,
“JLactivityâ€, “JNactivityâ€, “KLactivityâ€, “KGactivityâ€, “KUactivityâ€,
“KWactivityâ€, “MKactivityâ€, “MLactivityâ€, “SBactivityâ€, “NBactivityâ€,
“PLactivityâ€, REactivityâ€, “RHactivityâ€, “SHactivityâ€, “WLactivityâ€,
“ZNactivityâ€, “ZMactivityâ€, “uDactivityâ€, “uEactivityâ€, “uFactivityâ€,
“uGactivityâ€, “uuactivityâ€)
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

Here's one that takes a different approach:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

Instead of needing a list of all the controls to copy over, this one copies
all except for ones you specify as exceptions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a form that I'm using to enter a variety of data types: text,
date/time & Yes/No. The form has 87 fields, many of which are identical
from
one consecutive record to the next (and in any series of records, many
these
may be blank).

What would speed up data entry for me enormously would be to copy values
from the previous record into the new record automatically. My problem is
finding a way to achieve this for the Yes/No fields. I know essentially
nothing about visual basic, and I have little prior experience with Access
(I'm using Access 2003).

I have been able to do this for those date/time & text fields for which
this
is appropriate using the info presented here: "How to fill record with
data
from previous record automatically in Access 2000"
(http://support.microsoft.com/?kbid=210236), which creates an
AutoFillNewRecord() function:

------------------------
Function AutoFillNewRecord(F As Form)

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set RS = F.RecordsetClone
RS.MoveLast

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

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

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

F.Painting = False

' Visit each field on the form.
For Each C In F
' 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, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function
----------------------

...but haven't found an obvious way to make this work for the Yes/No
values.
Any help gratefully appreciated!

Thanks!

 

[Allen Browne]

The code looks at the names of the controls. Are these your control names?

Or are these field names, and the controls are called Text1 etc?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi - followed the directions on the webpage, but can't get it to EXCLUDE
records?? Any idea why this might be? This is my list of variables I want
to
exclude...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMsg As String
Call CarryOver(Me, strMsg, "Time", "BBactivity", "BKactivity",
"BOactivity", "DNactivity", "GSactivity", "KTactivity", "MAactivity",
"MSactivity", "NKactivity", "TKactivity", "ZFactivity", "HWactivity",
"FDactivity", "BNactivity", "CLactivity", "FLactivity", "HTactivity",
"JLactivity", "JNactivity", "KLactivity", "KGactivity", "KUactivity",
"KWactivity", "MKactivity", "MLactivity", "SBactivity", "NBactivity",
"PLactivity", REactivity", "RHactivity", "SHactivity", "WLactivity",
"ZNactivity", "ZMactivity", "uDactivity", "uEactivity", "uFactivity",
"uGactivity", "uuactivity")
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

Here's one that takes a different approach:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

Instead of needing a list of all the controls to copy over, this one
copies
all except for ones you specify as exceptions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a form that I'm using to enter a variety of data types: text,
date/time & Yes/No. The form has 87 fields, many of which are identical
from
one consecutive record to the next (and in any series of records, many
these
may be blank).

What would speed up data entry for me enormously would be to copy
values
from the previous record into the new record automatically. My problem
is
finding a way to achieve this for the Yes/No fields. I know essentially
nothing about visual basic, and I have little prior experience with
Access
(I'm using Access 2003).

I have been able to do this for those date/time & text fields for which
this
is appropriate using the info presented here: "How to fill record with
data
from previous record automatically in Access 2000"
(http://support.microsoft.com/?kbid=210236), which creates an
AutoFillNewRecord() function:

------------------------
Function AutoFillNewRecord(F As Form)

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set RS = F.RecordsetClone
RS.MoveLast

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

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

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

F.Painting = False

' Visit each field on the form.
For Each C In F
' 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, ";" & (C.Name) & ";") > 0
Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function
----------------------

...but haven't found an obvious way to make this work for the Yes/No
values.
Any help gratefully appreciated!

Thanks!

 

[Guest]

Hi - got it to work now - thanks - seems that the Visual Basic code window
didn't like me using copy/paste to insert the long list of the field names
(=control names). When I typed them all in one-by-one it worked (previously
the code had compiled without giving any errors and all that happened when I
created a new record was that everything was copied across). Now the
exclusion works!

Thanks you, this will save me a lot of time.

The code looks at the names of the controls. Are these your control names?

Or are these field names, and the controls are called Text1 etc?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi - followed the directions on the webpage, but can't get it to EXCLUDE
records?? Any idea why this might be? This is my list of variables I want
to
exclude...

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMsg As String
Call CarryOver(Me, strMsg, "Time", "BBactivity", "BKactivity",
"BOactivity", "DNactivity", "GSactivity", "KTactivity", "MAactivity",
"MSactivity", "NKactivity", "TKactivity", "ZFactivity", "HWactivity",
"FDactivity", "BNactivity", "CLactivity", "FLactivity", "HTactivity",
"JLactivity", "JNactivity", "KLactivity", "KGactivity", "KUactivity",
"KWactivity", "MKactivity", "MLactivity", "SBactivity", "NBactivity",
"PLactivity", REactivity", "RHactivity", "SHactivity", "WLactivity",
"ZNactivity", "ZMactivity", "uDactivity", "uEactivity", "uFactivity",
"uGactivity", "uuactivity")
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

Here's one that takes a different approach:
Carry data over to new record
at:
http://allenbrowne.com/ser-24.html

Instead of needing a list of all the controls to copy over, this one
copies
all except for ones you specify as exceptions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a form that I'm using to enter a variety of data types: text,
date/time & Yes/No. The form has 87 fields, many of which are identical
from
one consecutive record to the next (and in any series of records, many
these
may be blank).

What would speed up data entry for me enormously would be to copy
values
from the previous record into the new record automatically. My problem
is
finding a way to achieve this for the Yes/No fields. I know essentially
nothing about visual basic, and I have little prior experience with
Access
(I'm using Access 2003).

I have been able to do this for those date/time & text fields for which
this
is appropriate using the info presented here: "How to fill record with
data
from previous record automatically in Access 2000"
(http://support.microsoft.com/?kbid=210236), which creates an
AutoFillNewRecord() function:

------------------------
Function AutoFillNewRecord(F As Form)

Dim RS As DAO.Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

' Exit if not on the new record.
If Not F.NewRecord Then Exit Function

' Goto the last record of the form recordset (to autofill form).
Set RS = F.RecordsetClone
RS.MoveLast

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

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

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

F.Painting = False

' Visit each field on the form.
For Each C In F
' 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, ";" & (C.Name) & ";") > 0
Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function
----------------------

...but haven't found an obvious way to make this work for the Yes/No
values.
Any help gratefully appreciated!

Thanks!