Copy data from one record to a new record on a sub form

[Guest]

I have a form showing patient details with a sub form displaying all
associated referral details for the patient. One patient can have more than
one referral and I would like to be able to copy data from some of the fields
on the current referral when adding a new referral for the same patient. Is
this possible?

 

[Guest]

Yes, it is possible to do that. There are a number of ways, but one that is
fairly straight forward is to use the Tag property of the controls on the
form you want to replicate. I would suggest a command button on your form to
copy the record:

Private Sub cmdCopyRec_Click()
Dim varCtls As Variant
Dim lngCtr As Long

'Build an array of the controls to copy
varCtls = Array("txtPatientID","txtRefNo", "chkNotifed","chkRefDate")

'Loop throught the array and put the current value in the Tag

With Me
For lngCtr = 0 to UBound(varCtls)
.Controls(varCtls(lngCtr)).Tag = .Controls(varCtls(lngCtr)).Value
Next lngCtr
Docmd.GotoRecord ,,,acNewRec
For lngCtr = 0 to UBound(varCtls)
.Controls(varCtls(lngCtr)).Value = .Controls(varCtls(lngCtr)).Tag
Next lngCtr
End With
End Sub

 

[Guest]

Thank you for your help

I am fairly new to Access code and I am having some problem following your
suggestion could you please simplify. Also some of the fields on the referral
records are selected via a combo box will these fields still copy?

 

[Guest]

Yes, they will still copy. What ever the value of the control is will used.

 

[Kimmba]

This is first time I am using an array. This code works great if the current record's fields have values. However, I get a type mismatch error when the field value is null. How do you account for null field values when using an array? Thank you very much.