Array of text boxes on form

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

In a previous message, I requested help on inserting many
records into a table by the click of a button on a form. I
received the help I needed. I could be creating as many as
100 records, setting about a dozen fields in each record.
There is a lot of repetition. If I could set the fields on
the form as an array, I could create a loop to create
these records in far fewer statements. I would need some
handholding through this to create an array of unbound
fields in a form. An example of the statements would help
tremendously. Thanks!
 
Howard said:
In a previous message, I requested help on inserting many
records into a table by the click of a button on a form. I
received the help I needed. I could be creating as many as
100 records, setting about a dozen fields in each record.
There is a lot of repetition. If I could set the fields on
the form as an array, I could create a loop to create
these records in far fewer statements. I would need some
handholding through this to create an array of unbound
fields in a form. An example of the statements would help
tremendously. Thanks!
Click to expand...

I'm not sure of your details, but it may help to know that you can build
a string index to similarly named control using concatenation of an
index variable, like this:

Dim i As Integer
Dim varValue As Variant

For i = 1 to 12
varValue = Me.Controls("Textbox" & i).Value
Debug.Print varValue
Next i

That will print the values of text boxes named "Textbox1", "Textbox2",
.... "Textbox12".

Would you be interested in posting the code you're using now? Maybe we
can suggest ways to simplify it.
 
-----Original Message-----


I'm not sure of your details, but it may help to know that you can build
a string index to similarly named control using concatenation of an
index variable, like this:

Dim i As Integer
Dim varValue As Variant

For i = 1 to 12
varValue = Me.Controls("Textbox" & i).Value
Debug.Print varValue
Next i

That will print the values of text boxes named "Textbox1", "Textbox2",
.... "Textbox12".

Would you be interested in posting the code you're using now? Maybe we
can suggest ways to simplify it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk,
Click to expand...
I think your proposed method will work wonderfully,
especially since I already named the form fields
accordingly ie. level1, level2, level3, .... room1, room2,
room3,....etc. I have 16 sets of records to generate for
zone1 through zone16. I have included the code so far
which handles zone1 and zone2 (see below). If you can
think of an even better approach, I'll eagerly review any
suggestions. Thanks a ton!
-Howard
.
Private Sub CreateSystem_Click()
Click to expand...
Dim rst As Object

Set rst = CurrentDb.OpenRecordset("Components")

If Forms![NewAudioSystem]![Room1] <> "" Then

'Zone 1 16/4 to keypad
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]![System
ID]
rst![Component ID] = 540 '16/4
rst![Cable Number] = 1
rst![Source Level ID] = Forms![NewAudioSystem]!
[Equip Level]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Equip Room]
rst![Source Connector] = "Strip"
rst![Source Component] = Forms![NewAudioSystem]!
[Matrix]
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Notes] = "To keypad"
rst.Update

'Zone 1 Cat5e to keypad for future use
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]![System
ID]
rst![Component ID] = 539 'Cat5e
rst![Cable Number] = 2
rst![Source Level ID] = Forms![NewAudioSystem]!
[Equip Level]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Equip Room]
rst![Source Connector] = "Strip"
rst![Source Component] = Forms![NewAudioSystem]!
[Matrix]
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Notes] = "To keypad for future use"
rst.Update

If Forms![NewAudioSystem]![SpkrCnt1] > 0 Then
'Zone 1 16/2 from keypad to front left speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 3
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To front left speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt1] > 1 Then
'Zone 1 16/2 from keypad to front right speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 4
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To front right speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt1] > 2 Then
'Zone 1 16/2 from keypad to rear right speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 5
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To rear right speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt1] > 3 Then
'Zone 1 16/2 from keypad to rear left speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 6
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level1]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room1]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To rear left speaker"
rst.Update
End If
End If

If Forms![NewAudioSystem]![Room2] <> "" Then

'Zone 2 16/4 to keypad
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]![System
ID]
rst![Component ID] = 540 '16/4
rst![Cable Number] = 7
rst![Source Level ID] = Forms![NewAudioSystem]!
[Equip Level]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Equip Room]
rst![Source Connector] = "Strip"
rst![Source Component] = Forms![NewAudioSystem]!
[Matrix]
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Notes] = "To keypad"
rst.Update

'Zone 2 Cat5e to keypad for future use
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]![System
ID]
rst![Component ID] = 539 'Cat5e
rst![Cable Number] = 8
rst![Source Level ID] = Forms![NewAudioSystem]!
[Equip Level]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Equip Room]
rst![Source Connector] = "Strip"
rst![Source Component] = Forms![NewAudioSystem]!
[Matrix]
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Notes] = "To keypad for future use"
rst.Update

If Forms![NewAudioSystem]![SpkrCnt2] > 0 Then
'Zone 2 16/2 from keypad to front left speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 9
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To front left speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt2] > 1 Then
'Zone 2 16/2 from keypad to front right speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 10
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To front right speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt2] > 2 Then
'Zone 2 16/2 from keypad to rear right speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 11
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To rear right speaker"
rst.Update
End If

If Forms![NewAudioSystem]![SpkrCnt2] > 3 Then
'Zone 2 16/2 from keypad to rear left speaker
rst.AddNew
rst![Customer ID] = Forms![NewAudioSystem]!
[Customer ID]
rst![Type] = "Raw Cable"
rst![System ID] = Forms![NewAudioSystem]!
[System ID]
rst![Component ID] = 535 '16/2
rst![Cable Number] = 12
rst![Source Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Source Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Source Connector] = "Strip"
rst![Source Component] = 175 'Matrix 10-Button
Keypad (White)
rst![Target Level ID] = Forms![NewAudioSystem]!
[Level2]
rst![Target Room ID] = Forms![NewAudioSystem]!
[Room2]
rst![Target Connector] = "Strip"
rst![Target Component] = 185 'Thunder Pair
6.5in Round In-Ceiling Speakers
rst![Notes] = "To rear left speaker"
rst.Update
End If
End If

End Sub
 
-----Original Message-----


I'm not sure of your details, but it may help to know that you can build
a string index to similarly named control using concatenation of an
index variable, like this:

Dim i As Integer
Dim varValue As Variant

For i = 1 to 12
varValue = Me.Controls("Textbox" & i).Value
Debug.Print varValue
Next i

That will print the values of text boxes named "Textbox1", "Textbox2",
.... "Textbox12".

Would you be interested in posting the code you're using now? Maybe we
can suggest ways to simplify it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk,
Click to expand...
Ignore me last reply. Don't even waste your time coming up
with another solution. Your suggestion above worked like
an absolute charm my first attempt. I have one block of
code producing 16 unique zones of records. Perfect!!!!
Forever in your debt! Thanks again!
-Howard
 
Back
Top