HELP!!!! Anyone can help me optimize my code???? 1

[ii128]

On my form, it has many optionbuttons and textboxs. The total fields of the table for this form is 61. I would like to make it easier to save every field.


Private Sub cmdSave_Click()
Dim aa As String
Dim ii As String
Dim i As Integer
Dim Result As String
Dim other As String
Dim speed As String
Dim model As String
Dim sn As String
Dim brand As String
Dim n1 As Integer
Dim aa1 As String
Dim n2 As Integer
Dim aa2 As String
Dim n3 As Integer
Dim aa3 As String
Dim n4 As Integer
Dim aa4 As String
Dim n5 As Integer
Dim aa5 As String
Dim n6 As Integer
Dim aa6 As String

Dim ctl As Control
Dim Str() As String
Dim n As Integer

Str = Split("CPU,Case,PCI,Sound,Hard,Memory,Modem,Mouse,Operating,SCSI,CD,Floppy,Monitor,Speakers,Netword,Motherboard", ",&quot

Set rsHard = DataEnvironment1.rsHardware
If rsHard.State = adStateOpen Then rsHard.Close
DataEnvironment1.Hardware
rsHard.AddNew

For Each ctl In Controls
For i = LBound(Str) To UBound(Str)
Result = InStr(ctl.Name, Str(i))
If Result > 0 Then
Result = 0
If TypeOf ctl Is OptionButton Then
If ctl.Value = True Then
For n1 = 0 To rsHard.Fields.Count - 1
If (rsHard.Fields(n1).Name = Str(i)) Then
rsHard.Fields(n1).Value = ctl.Caption
Exit For
End If
Next n1
End If
End If

If TypeOf ctl Is TextBox Then
other = InStr(ctl.Name, "Other&quot
If (other > 0) Then
For n2 = 0 To rsHard.Fields.Count - 1
aa2 = InStr(rsHard.Fields(n2).Name, "Other" + Str(i))
If aa2 > 0 Then
rsHard.Fields(n2).Value = ctl.Text
End If
Next n2
End If
speed = InStr(ctl.Name, "Speed&quot
If (speed > 0) Then
For n3 = 0 To rsHard.Fields.Count - 1
aa3 = InStr(rsHard.Fields(n3).Name, "Speed" + Str(i))
If aa3 > 0 Then
rsHard.Fields(n3).Value = ctl.Text
End If
Next n3
End If
model = InStr(ctl.Name, "Model&quot
If (model > 0) Then
For n4 = 0 To rsHard.Fields.Count - 1
aa4 = InStr(rsHard.Fields(n4).Name, "Model" + Str(i))
If aa4 > 0 Then
rsHard.Fields(n4).Value = ctl.Text
End If
Next n4
End If
sn = InStr(ctl.Name, "SN&quot
If (sn > 0) Then
For n5 = 0 To rsHard.Fields.Count - 1
aa5 = InStr(rsHard.Fields(n5).Name, "Model" + Str(i))
If aa5 > 0 Then
rsHard.Fields(n5).Value = ctl.Text
End If
Next n5
End If
brand = InStr(ctl.Name, "Brand&quot
If (brand > 0) Then
For n6 = 0 To rsHard.Fields.Count - 1
aa6 = InStr(rsHard.Fields(n6).Name, "Model" + Str(i))
If aa6 > 0 Then
rsHard.Fields(n6).Value = ctl.Text
End If
Next n6
End If
End If
End If
Next i
Next ctl
rsHard.Update
If rsHard.State = adStateClosed Then rsHard.Open
End Sub

 

[ii128]

Anyone????

 

[ii128]

Anyone?/

 

[strongm]

I'm sure someone will be along in a minute...

 

[mikhailwaxman]

Use array of TextBox Controls and use Property Tag for texboxes
txtBox(0).Tag="SpeedCPU"
txtBox(1).Tag="OtherCPU"
txtBox(2).Tag="ModelCPU"
txtBox(3).Tag="SNCPU"
txtBox(4).Tag="BrandCPU"
.....
.....
etc,etc
So Property Tag is FieldName in Your Database
For OptionButton Control
OptButton(0).Tag="CPU"
OptButton(1).Tag="Case"
OptButton(2).Tag="PCI"
...
...
Private Sub cmdSave_Click
Dim txtB as Textbox,oButton as OptionButton
Set rsHard = DataEnvironment1.rsHardware
If rsHard.State = adStateOpen Then rsHard.Close
DataEnvironment1.Hardware
rsHard.AddNew
For each txtB in txtBox
rsHard.Fields(txtB.Tag)=txtB.Text
Next
For each oButton in OptButton
if oButton.Value then rsHard.Fields(oButton.Tag)=oButton.Caption
Next
...
End Sub
I did not check but it shoud work

 

[chiuchimu]

Can you also tag variables?

 

[mikhailwaxman]

Yes, You can if variable is User Defined Data Type like
Type TaggedVariable
Value as varType
Tag as String
end Type

Dim TagAttay() as TaggedVariable
etc,etc

 

[ii128]

Hi, mikhailwaxman

I have problem when I try to run the code. It has compile error on txtb: "for each control variable on arrays mush be variant"


Private Sub cmdSave_Click()

Dim txtBox(5) As TextBox
Dim OptButton(3) As OptionButton
Dim txtB As TextBox, oButton As OptionButton

txtBox(0).Tag = "SpeedCPU"
txtBox(1).Tag = "OtherCPU"
txtBox(2).Tag = "ModelCPU"
txtBox(3).Tag = "SNCPU"
txtBox(4).Tag = "BrandCPU"

OptButton(0).Tag = "CPU"
OptButton(1).Tag = "Case"
OptButton(2).Tag = "PCI"

Set rsHard = DataEnvironment1.rsHardware
If rsHard.State = adStateOpen Then rsHard.Close
DataEnvironment1.Hardware
rsHard.AddNew
For Each txtB In txtBox
rsHard.Fields(txtB.Tag) = txtB.Text
Next
For Each oButton In OptButton
If oButton.Value Then rsHard.Fields(oButton.Tag) = oButton.Caption
Next

End Sub

 

[mikhailwaxman]

txtBox is Name Property of TextBox. So You Place on the Form 5 TextBoxes
With the same Name Property but different Index. The same about OptButton.You set Tag Property for each textbox as below
txtBox(0).Tag = "SpeedCPU" ' 0 is Index Property
txtBox(1).Tag = "OtherCPU"
txtBox(2).Tag = "ModelCPU"
txtBox(3).Tag = "SNCPU"
txtBox(4).Tag = "BrandCPU"
the same for OptionButton. OptButton is the Name Property of each OptionButton
OptButton(0).Tag = "CPU"
OptButton(1).Tag = "Case"
OptButton(2).Tag = "PCI"
In the Procedure should be only code below, nothing else
Private Sub cmdSave_Click()

Dim txtB As TextBox, oButton As OptionButton


Set rsHard = DataEnvironment1.rsHardware
If rsHard.State = adStateOpen Then rsHard.Close
DataEnvironment1.Hardware
rsHard.AddNew

For Each txtB In Me.txtBox
rsHard.Fields(txtB.Tag) = txtB.Text
Next
For Each oButton In Me.OptButton
If oButton.Value Then rsHard.Fields(oButton.Tag) =
oButton.Caption
Next

End Sub

I check and it works.

 

[ii128]

mikhailwaxman, Thanks a lot.