Consolidate Code

D

Dan

I have a Form to gather information into a Table and have placed a CheckBox
next to certain fields to have current data automatically populate the
newrecord fields when desired and have placed code in the New Record Button
and everything works just fine. (The checkboxes are not bounded to
tabledata, only on the form)

I'm just wondering if there was a better way for me to do this.

Dim CopyFile, CopySrc, CopyLoc, CopyBde, CopyCont As String
Dim CopySP, CopyMA, CopyMS, CopyBFS, CopyFB As String
CopyFile = Me.Source_File
CopySrc = Me.Source
CopyLoc = Me.Location
CopyBde = Me.Brigade
CopyCont = Me.Contractor
CopySP = Me.Service_Providers
CopyMA = Me.Mission_Area
CopyMS = Me.Mission_Set
CopyBFS = Me.Broad_Focus_Area
CopyFB = Me.Function_Bucket
DoCmd.RunCommand acCmdRecordsGoToNew
If Me.Check34 = True Then
Me.Source_File = CopyFile
End If
If Me.Check36 = True Then
Me.Source = CopySrc
End If
If Me.Check37 = True Then
Me.Location = CopyLoc
End If
If Me.Check38 = True Then
Me.Brigade = CopyBde
End If
If Me.Check39 = True Then
Me.Contractor = CopyCont
End If
If Me.Check40 = True Then
Me.Service_Providers = CopySP
End If
If Me.Check41 = True Then
Me.Mission_Area = CopyMA
End If
If Me.Check42 = True Then
Me.Mission_Set = CopyMS
End If
If Me.Check43 = True Then
Me.Broad_Focus_Area = CopyBFS
End If
If Me.Check44 = True Then
Me.Function_Bucket = CopyFB
End If

Thanks
Dan
 
H

Hans Up

Dan said:
I have a Form to gather information into a Table and have placed a CheckBox
next to certain fields to have current data automatically populate the
newrecord fields when desired and have placed code in the New Record Button
and everything works just fine. (The checkboxes are not bounded to
tabledata, only on the form)

I'm just wondering if there was a better way for me to do this.

Dim CopyFile, CopySrc, CopyLoc, CopyBde, CopyCont As String
Dim CopySP, CopyMA, CopyMS, CopyBFS, CopyFB As String
Click to expand...

CopyCont and CopyFB are declared as String. The rest are Variants. Is
that what you intended?
CopyFile = Me.Source_File
CopySrc = Me.Source
CopyLoc = Me.Location
CopyBde = Me.Brigade
CopyCont = Me.Contractor
CopySP = Me.Service_Providers
CopyMA = Me.Mission_Area
CopyMS = Me.Mission_Set
CopyBFS = Me.Broad_Focus_Area
CopyFB = Me.Function_Bucket
DoCmd.RunCommand acCmdRecordsGoToNew
If Me.Check34 = True Then
Me.Source_File = CopyFile
End If
If Me.Check36 = True Then
Me.Source = CopySrc
End If
Click to expand...

Since your check boxes indicate which values should be carried over to
the new record, I named the check boxes with a prefix of "chkCarry" plus
the name of the corresponding data control:

CopyFile -> chkCarryCopyFile
CopySrc -> chkCarryCopySrc

That naming strategy allows me to easily match up the check box with its
data control. I also use a dictionary variable to store only those
values which are to be copied into the new record. For example, I don't
store the CopyLoc value unless the chkCarryCopyLoc box is checked.

Private Sub cmdNewRecord_Click()
Dim ctl As Control
Dim dict As Object
Dim strName As String
Dim varKeys As Variant
Dim i As Long

Set dict = CreateObject("Scripting.Dictionary")

For Each ctl In Me.Controls
If Left(ctl.Name, 8) = "chkCarry" Then
If ctl.Value = True Then
strName = Mid(ctl.Name, 9)
dict.Add strName, Me.Controls(strName).Value
End If
End If
Next ctl

DoCmd.RunCommand acCmdRecordsGoToNew

varKeys = dict.Keys
For i = 0 To UBound(varKeys)
Me.Controls(varKeys(i)) = dict(varKeys(i))
Next i

Set dict = Nothing
End Sub
 
Top