Autofill form with previous record

J

JS

Our database tracks experiments and often the experiment
is replicated with minor modifications. In those cases it
would be nice to auto-populate the form with most of the
data from the original experiment. The the tech can just
fill in the ExperimentID field and change whatever is
different. Does anyone have any ideas on how I could
accomplish this? I'm a VBA novice so details would be nice.
 
A

Alfred Nash

On way to do it is to add code to the On Enter event of the properties

If Me.NewRecord and IsNull(me.fieldname) then SendKeys ^', true

That line of code will copy data for that field from the last record in the
database.

Enjoy
 
A

Al Borges

Hi JS/Nash:

This is an issue that I've run into several times. I've used the
recordsetclone successfully in the past, but a couple of weeks ago I was
working on a chemotherapy protocol form, which naturally would profit from a
carryover button for cycle 2, then 3, 4, etc. Only the dynaset method seemed
to work well, since I wanted the exact previous order- with the dynaset you
can order your records with the last one being a truely most recent record
posted, and likewise this goes for the previous record. Here's the actual
code from my database:
------------------------------------------------------------
Private Sub Command63_Click()
' this is sample code from Alberto Borges, MD
' (e-mail address removed)
On Error GoTo command63err
If MsgBox("Do you wish to overwrite any printed information? The date" &
Chr(13) & "will be the current date, but the Surface Area and Calvert" &
Chr(13) & "fields will reflect prior order's variable entries.", vbYesNo +
vbQuestion, "Copy new order information?") = vbNo Then
MsgBox "Event is cancelled.", vbExclamation
Else
[CDATE] = DATE
ii = Forms![*chemotherapy form]![EmbChemo1].Form![ORDER]
Set rst = Forms![*chemotherapy form]![EmbChemo1].Form.RecordsetClone
'Clone form's recordset
With rst
.FindFirst "[order] = " & ii
.MovePrevious
'Go back one record
'In case current record is 1st
If Not .EOF Then 'Grab specified field's value
oo = IIf(IsNull(![CHEMO]), " ", ![CHEMO])
kk = IIf(IsNull(![CHNM1]), " ", ![CHNM1])
ll = IIf(IsNull(![CHNM2]), " ", ![CHNM2])
DX = IIf(IsNull(![CHNM3]), " ", ![CHNM3])
rr = IIf(IsNull(![CHNM4]), " ", ![CHNM4])
XX = IIf(IsNull(![CHNM5]), " ", ![CHNM5])
yy = IIf(IsNull(![CHEM1]), 0, ![CHEM1])
zz = IIf(IsNull(![CHEM2]), 0, ![CHEM2])
.MoveNext
.Edit
![CHEMO] = oo
![CHNM1] = kk
![CHNM2] = ll
![CHNM3] = DX
![CHNM4] = rr
![CHNM5] = XX
![CHEM1] = yy
![CHEM2] = zz
.UPDATE
.MovePrevious
ww = IIf(IsNull(![CHEM3]), 0, ![CHEM3])
yy = IIf(IsNull(![CHEM4]), 0, ![CHEM4])
zz = IIf(IsNull(![CHEM5]), 0, ![CHEM5])
ii = IIf(IsNull(![CALC1]), 0, ![CALC1])
uu = IIf(IsNull(![CALC2]), 0, ![CALC2])
vv = IIf(IsNull(![CALC3]), 0, ![CALC3])
nn = IIf(IsNull(![CALC4]), 0, ![CALC4])
mm = IIf(IsNull(![CALC5]), 0, ![CALC5])
rr = Nz(![MGU1], ".")
ss = Nz(![MGU2], ".")
' now go back to your new record
.MoveNext
.Edit
![CHEM3] = ww
![CHEM4] = yy
![CHEM5] = zz
![CALC1] = ii
![CALC2] = uu
![CALC3] = vv
![CALC4] = nn
![CALC5] = mm
![MGU1] = rr
![MGU2] = ss
.UPDATE
.MovePrevious
ww = IIf(IsNull(![SA]), 0, ![SA])
yy = IIf(IsNull(![HT]), 0, ![HT])
zz = IIf(IsNull(![WT]), 0, ![WT])
ii = IIf(IsNull(![DOSERED]), 0, ![DOSERED])
uu = IIf(IsNull(![Calvert]), 0, ![Calvert])
vv = IIf(IsNull(![CalvertAdj]), 0, ![CalvertAdj])
.MoveNext
.Edit
![SA] = ww
![HT] = yy
![WT] = zz
![DOSERED] = ii
![Calvert] = uu
![CalvertAdj] = vv
.UPDATE
.MovePrevious
oo = Nz(![MGU3], ".")
rr = Nz(![MGU4], ".")
ss = Nz(![MGU5], ".")
tt = Nz(![INSTR1], ".")
XX = Nz(![INSTR2], ".")
DX = Nz(![INSTR3], ".")
CPT = Nz(![INSTR4], ".")
SQLStmt = Nz(![INSTR5], ".")
kk = Nz(![PRECHEMO], ".")
.MoveNext
.Edit
![MGU3] = oo
![MGU4] = rr
![MGU5] = ss
![INSTR1] = tt
![INSTR2] = XX
![INSTR3] = DX
![INSTR4] = CPT
![INSTR5] = SQLStmt
![PRECHEMO] = kk
.UPDATE
.MovePrevious
oo = Nz(![-2HR], ".")
rr = Nz(![-1HR], ".")
ss = Nz(![-1/2HR], ".")
DX = Nz(![FUTUREINST], ".")
CPT = Nz(![REFERENCE], ".")
.MoveNext
.Edit
![-2HR] = oo
![-1HR] = rr
![-1/2HR] = ss
![FUTUREINST] = DX
![REFERENCE] = CPT
.UPDATE
.Close
Me.Refresh
[CHEMO].SetFocus
End If
End With
End If
Exit Sub
command63err:
MsgBox "An error has occurred! If it persists, please notify software
administrator.", vbCritical
Exit Sub
End Sub

Regards,
Al
 
M

Malcolm Cook

JS,

Controls have a .defaultvalue property which you can set programatically, or
write some code to let your users set their own defaults for future data
entry based on values in the current record.

Following my sig is a function I've used that lets users set the default for
the current field (control) based on its value in the current row.

You could fairly easily modify it to set the default for all the controls,
or some predefiend set of controls which you know in advance that the users
like to carry over from record to record. If you took that route, you might
like to call that function in your data entry form's After_update, with the
effec that the most recently entered record always supplies the defaults for
the next record.

Good luck,

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA



Public Function mnuCurrentControlSetDefault()
' Bind this to AutoExec Macro, say, ^+0 (a.k.a. control shift 0).
' Then, user can interactively set a default value for a control based on a
current value.
' if the default is already set, toggles it off.

Dim strCurText As String

On Error GoTo HandleErr
'Call EnterProc("AutoKeyFunctions.mnuCurrentControlSetDefault")


With Screen.ActiveControl
If .Locked Then Err.Raise 513, , .Controls(0).caption & " is locked.
Cancelled!"
Dim frmParent As Access.Form
Set frmParent = ACControlParentForm(Screen.ActiveControl) '
Screen.ActiveDatasheet '.ActiveDataAccessPage '.ActiveForm
If frmParent.Dirty Then frmParent.Dirty = False 'may generate an error
strCurText = .Text
Dim strNewDefault As String
strNewDefault = Chr(34) & .Text & Chr(34)
If (.DefaultValue = strNewDefault) Then
.DefaultValue = ""
Else
.DefaultValue = strNewDefault
End If
.Text = strCurText
End With

ExitHere:
'Call ExitProc("AutoKeyFunctions.mnuCurrentControlSetDefault")
Exit Function

' Error handling block added by VBA Code Commenter and Error Handler Add-In.
DO NOT EDIT this block of code.
HandleErr:
Select Case Err.Number
Case 513
ErrReport errInformation, Err
Case 514
ErrReport errExclamation, Err
Case 11 ' 2474 ' The expression you entered requires the control to be
in the active window.
ErrReport errExclamation, Err
'case n
' ErrReport true err "AutoKeyFunctions.mnuCurrentControlSetDefault"
Case 2113 ' The value you entered isn't valid for this field.
ErrReport errInformation, Err, "{Description} For example, you may have
entered text in a numeric field, or a number that is larger than the field
size permits"

Case 2135 ' This property is read-only and can't be set.
ErrReport errExclamation, Err, "Default value can not be set for this
field"

Case Else
ErrReport errCritical, Err, ,
"AutoKeyFunctions.mnuCurrentControlSetDefault"
End Select
Resume ExitHere
' End Error handling block.
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top