AutoFillNewRecord for multiselect field

[Patrick Livingood]

The Microsoft site (http://support.microsoft.com/kb/210236) has a function to
help copy record values to new records. The code is below. I have added a
multiselect field to my form (yes, yes I know it would be better to do this
manually, but this is a low-import keywords field and this seems like a good
use of the technology). Unfortunately I cannot figure out how to adapt the
code to also work on a multiselect field. Any help would be greatly
appreciated. The original code is below:

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

 

[Allen Browne]

As you seem to already know, multi-valued fields promise simplicity but add
unnecessary complexity. The case you describe here is just one good example
of that.

If you really want to proceed, you will need to examine the Type of the
field. If it is a multivalued field (including Attachment), the value will
be above 100. The actual numbers are listed here:
http://allenbrowne.com/ser-49.html

If it is an MVF, its Value will be a recordset. You will need to assign it
to a Recordset, and loop through the records. Similarly, you will need to
assign a Recordset to the target, and AddNew (with Update) to add the
matching records from the source to the target.

It is also possible to deal with MVFs as arrays, but the Recordset approach
is more straightforward IME.

Bottom line: avoid MVFs if you want to keep your databases simple and your
data queryable.

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

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

message
news:[email protected]...

 

[Patrick Livingood]

Thanks. I have now implemented it the standard normalized way. I am not
sure it saves on coding in the short-term because the duplicate record code
still has to be expanded, but I appreciate the warning I will run into other
problems with queries and compatibility later.

As you seem to already know, multi-valued fields promise simplicity but add
unnecessary complexity. The case you describe here is just one good example
of that.

If you really want to proceed, you will need to examine the Type of the
field. If it is a multivalued field (including Attachment), the value will
be above 100. The actual numbers are listed here:
http://allenbrowne.com/ser-49.html

If it is an MVF, its Value will be a recordset. You will need to assign it
to a Recordset, and loop through the records. Similarly, you will need to
assign a Recordset to the target, and AddNew (with Update) to add the
matching records from the source to the target.

It is also possible to deal with MVFs as arrays, but the Recordset approach
is more straightforward IME.

Bottom line: avoid MVFs if you want to keep your databases simple and your
data queryable.

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

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

message

The Microsoft site (http://support.microsoft.com/kb/210236) has a function
to
help copy record values to new records. The code is below. I have added
a
multiselect field to my form (yes, yes I know it would be better to do
this
manually, but this is a low-import keywords field and this seems like a
good
use of the technology). Unfortunately I cannot figure out how to adapt
the
code to also work on a multiselect field. Any help would be greatly
appreciated. The original code is below:

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