Display the contents of an Arary (or more than one) on a form.

[FatMan]

Hi all:
I have a form that has three combo boxes on it. Due to the way the user
wants to do data entry I have had to allow multiple entries in each of the
combo boxes. I have done this through the use of arrays and a bit of code on
the after update event. The code is pretty much the same for each
array/combo box with only the name of the array and counter changing. What
I would like to know now is there a way to open a form and print or dump the
contents of the arays so the user can see the selections they have made.

Code:
Private Sub SprayBlockID_AfterUpdate()

Dim i As Integer

'set counter to ensure first entry is entered into the first element
intBlCount = intBlCount + 1

'assign entry (BlockID) to array
intBlockID(intBlCount) = Me.SprayBlockID


Debug.Print "Counter: " & intBlCount & " Array: " & intBlockID(intBlCount)


End Sub

Any help is greatly appreciated.

Thanks,
Darren

 

[Steve Sanford]

How about using a list box with the "RowSourceType" property set to "Value
List"? Loop thru the array and build a string of selections and set the
"RowSource" to the string of the selections built from the array. You could
use one form and 1 list box or a form and 3 list boxes.

Instead of an array, I would/might have used a table (for each combo box) to
store the selections. A list box and a query would display the selections -
sorting is much easier than trying to write code to sort the array (if
needed).

HTH

 

[FatMan]

Steve:
Thanks for the input. Just to give a bit more on the background there are
other fields on the form and what I will need to do is to create a new record
for all the fields for each element in the arrays. What I am doing is
developing a database that will keep track of the agriculture chemicals used
for pest control in orchards. The orchards are divided into blocks. What
the growers what is to be able to enter multiple values for the chemicals
mixed in a tank, multiple values for the pest targeted and multiple values
for the blocks that the chemicals were sprayed over. The fields would
include:

Date: 03-Mar-09
Farm Name: FatMan Farm
Operator: FatMan
Weather: warm & sunny
Rate/ha: 2
Unit of Measure: Kg
Blocks: B1, B2, B3
Chemical: C1, C2
Pest: P1

The above example (a very small example) would require 6 records to be added:
B1P1C1
B1P1C2
B2P1C1
B2P1C2
B3P1C1
B3P1C2

My problem....growers do not want to set down and enter 6 records. They
just want to enter the data press a button and have the comptuer add all the
required records. So I thought I could use three arrays (Block, Pest and
Chemical) to store the multiple entries and then loop through the arrays and
create one record for each item in the array. Before I add the records I
want to give the user a chance to edit the arrays...thus the need to display
them on a form.

Steve, if I try your suggestion about displaying them on a form through a
list box can I give the users a chance to edit an entry and then load them
back into the array before I do the add.

Sorry for the long reply folks but I wanted it to be complete.

Thanks,
FatMan

 

[Steve Sanford]

I don't think you really need to use arrays. For selecting the block numbers,
I would use a multiselect list box. The row source would be a query based on
a table. the table would have two fields: FarmName and BlockNumber

FarmName BlockNumber
--------------- ---------------
FatMan Farm B1
FatMan Farm B2
FatMan Farm B3
TallMan Farm B1
TallMan Farm B3
TallMan Farm B5
TallMan Farm B7
TallMan Farm B9

If youhave a combo box to select the farm name (cboFarmName), the query for
the Block list box would be something like:

SELECT blocknumber FROM tblBlocks WHERE [FarmName] = '" & Me.cboFarmName &
"';"

Using the multiselect list box you can select any or all of the listed
blocks. Do the same for the chemicals and pests. You can select/unselect
right in the list box. From there you loop thru each of the selections (just
like an array) using code to create the records.

My list boxes have two buttons placed under the list box, one to clear all
selections and the other to reverse the selection.


Or you can use arrays

HTH

 

[FatMan]

Steve:
Thanks for the advice. Never thought of the multiselect list box and using
code to add the records. Will explore this option and thank you greatly for
the suggestion.

Regards,
Fatman

I don't think you really need to use arrays. For selecting the block numbers,
I would use a multiselect list box. The row source would be a query based on
a table. the table would have two fields: FarmName and BlockNumber

FarmName BlockNumber
--------------- ---------------
FatMan Farm B1
FatMan Farm B2
FatMan Farm B3
TallMan Farm B1
TallMan Farm B3
TallMan Farm B5
TallMan Farm B7
TallMan Farm B9

If youhave a combo box to select the farm name (cboFarmName), the query for
the Block list box would be something like:

SELECT blocknumber FROM tblBlocks WHERE [FarmName] = '" & Me.cboFarmName &
"';"

Using the multiselect list box you can select any or all of the listed
blocks. Do the same for the chemicals and pests. You can select/unselect
right in the list box. From there you loop thru each of the selections (just
like an array) using code to create the records.

My list boxes have two buttons placed under the list box, one to clear all
selections and the other to reverse the selection.


Or you can use arrays

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve:
Thanks for the input. Just to give a bit more on the background there are
other fields on the form and what I will need to do is to create a new record
for all the fields for each element in the arrays. What I am doing is
developing a database that will keep track of the agriculture chemicals used
for pest control in orchards. The orchards are divided into blocks. What
the growers what is to be able to enter multiple values for the chemicals
mixed in a tank, multiple values for the pest targeted and multiple values
for the blocks that the chemicals were sprayed over. The fields would
include:

Date: 03-Mar-09
Farm Name: FatMan Farm
Operator: FatMan
Weather: warm & sunny
Rate/ha: 2
Unit of Measure: Kg
Blocks: B1, B2, B3
Chemical: C1, C2
Pest: P1

The above example (a very small example) would require 6 records to be added:
B1P1C1
B1P1C2
B2P1C1
B2P1C2
B3P1C1
B3P1C2

My problem....growers do not want to set down and enter 6 records. They
just want to enter the data press a button and have the comptuer add all the
required records. So I thought I could use three arrays (Block, Pest and
Chemical) to store the multiple entries and then loop through the arrays and
create one record for each item in the array. Before I add the records I
want to give the user a chance to edit the arrays...thus the need to display
them on a form.

Steve, if I try your suggestion about displaying them on a form through a
list box can I give the users a chance to edit an entry and then load them
back into the array before I do the add.

Sorry for the long reply folks but I wanted it to be complete.

Thanks,
FatMan

 

[Mike Painter]

Date: 03-Mar-09
Farm Name: FatMan Farm
Operator: FatMan
Weather: warm & sunny
Rate/ha: 2
Unit of Measure: Kg
Blocks: B1, B2, B3
Chemical: C1, C2
Pest: P1

The above example (a very small example) would require 6 records to
be added: B1P1C1
B1P1C2
B2P1C1
B2P1C2
B3P1C1
B3P1C2

My problem....growers do not want to set down and enter 6 records.
They just want to enter the data press a button and have the comptuer
add all the required records. So I thought I could use three arrays
(Block, Pest and Chemical) to store the multiple entries and then
loop through the arrays and create one record for each item in the
array. Before I add the records I want to give the user a chance to
edit the arrays...thus the need to display them on a form.

How about three subforms based on the B, P, and C tables.
The subforms have at least the descriptor and a check box.
Associated with each form is a "select all" and "Clear all" button.
The grower checks the ones he wants and pushes an "Update" button.
The update button creates a query which is a Cartesian set based on the
items checked and use that to add your records.

The select and clear buttons may not be needed and you could just reset
everything with the update button.
In my application involving lab tests and doctors they frequently ran the
same reports so leaving teh boxes checked sped things up.

(In case you are not aware a Cartesian set is all possible combinations and
what you get if you drop tables on the the query designer and don't join
them.)

 

[FatMan]

Mike:
I like the ideas of the subforms and use the sartesian set to update the
tables. I think i will try this method as well as of that suggested by Steve
and pick the one the growers like the best.

Thanks,
FatMan

 

[Ron2006]

Don't forget to cover yourself by checking that at least one of the
items has been checked in each of the possible selections.

This might be easier with the subforms and using dcount on some
queries executed when then press the create button.