Filling in an array of text boxes based on a query?

[tjandt]

I wasn't sure whether to post this here or in the programming forum
but here goes.

I've been working on a problem to try and plot values into an array o
text boxes, and am really stuck. I'm hoping some guru out there is u
for the challenge on this one, as it seems beyond me :

I have a 'lakes' table with the following fields
*Lake Nam
Lake Siz
Lake Stat
Lake Imag

I have a 'fish types' table that has the following fields
*Fish Typ
Fish Abbreviatio

I have a 'caught' table that has the following fields
Fish Typ
Lake Nam
Caught X Coor
Caught Y Coor

* - Primary Ke

I have one to many relationships with referential integrity set fro
the Lakes and Fish tables to the Caught table using the Lake Name an
Fish Type fields accordingly

I have a form based on the Lakes table that has the image of the lak
with X and Y coordinates on it

So the image would look like this (where the X's are unbound tex
boxes)

A B C
[------------------
1 | xx xx xx xx
2 | xx xx xx xx
3 | xx xx xx xx
4 | xx xx xx xx
[------------------

Also on the form, i have two combo boxes that display a list of uniqu
values for all the fish types caught on that lake and a button tha
says 'Plot

What I'd like to do is as follows

If user selects fish from first combo box, the text boxes on the lef
fill with the fish abbreviation of the fish caught in that locatio
(example: If user selects sunfish and I caught a Sunfish (S) at C3
the the first text box on C3 would populate with the letter S

If user selects fish from the second combo box, the text boxes on th
right fill with the fish abbreviation of the fish caught in tha
locatio
(example: If user selects Bass and I caught a Bass (B) at A1, the th
right text box on A1 would populate with the letter B

If user selects fish from both combo boxes, the text boxes on the lef
fill from combo box one, and the text boxes on the right fill fro
combo box two
(example: If user selects sunfish and I caught a Sunfish (S) at C3
the the first text box on C3 would populate with the letter S and I
user selects Bass and I caught a Bass (B) at A1, the the right tex
box on A1 would populate with the letter B.

Finally, I'd like to add color coordination to this so that if th
count of a fish at a coordinate exceeds a certain limit, the color o
the letter would change at that location.
(Example: If I caught 1-5 bass at a location, text would be black.
6-10, text would be yellow, 11-20, text would be orange, and over 20
text would be red

The text boxes are currently named txtA1, A1-2, txtB1, txtB1-2, etc
for the left and right box at each coordinate respectively

I am not hugely skilled at programming, and from what a co-worker an
I came up with, think this needs some kind of advanced query, mixe
with some nested loops of code

Can anyone out there lend a hand on this? It would be hugel
appreciated! I'd be happy to e-mail you a sample database wit
sample data if you think you could tackle it. I am leaving on
fishing trip this coming weekend, and was really hoping to have i
done by then :

Thanks in advance
Ti

 

[Marshall Barton]

I wasn't sure whether to post this here or in the programming forum,
but here goes.

I've been working on a problem to try and plot values into an array of
text boxes, and am really stuck. I'm hoping some guru out there is up
for the challenge on this one, as it seems beyond me

I have a 'lakes' table with the following fields:
*Lake Name
Lake Size
Lake State
Lake Image

I have a 'fish types' table that has the following fields:
*Fish Type
Fish Abbreviation

I have a 'caught' table that has the following fields:
Fish Type
Lake Name
Caught X Coord
Caught Y Coord

* - Primary Key

I have one to many relationships with referential integrity set from
the Lakes and Fish tables to the Caught table using the Lake Name and
Fish Type fields accordingly.

I have a form based on the Lakes table that has the image of the lake
with X and Y coordinates on it.

So the image would look like this (where the X's are unbound text
boxes).

A B C D
[------------------]
1 | xx xx xx xx |
2 | xx xx xx xx |
3 | xx xx xx xx |
4 | xx xx xx xx |
[------------------]

Also on the form, i have two combo boxes that display a list of unique
values for all the fish types caught on that lake and a button that
says 'Plot'

What I'd like to do is as follows:

If user selects fish from first combo box, the text boxes on the left
fill with the fish abbreviation of the fish caught in that location
(example: If user selects sunfish and I caught a Sunfish (S) at C3,
the the first text box on C3 would populate with the letter S)

If user selects fish from the second combo box, the text boxes on the
right fill with the fish abbreviation of the fish caught in that
location
(example: If user selects Bass and I caught a Bass (B) at A1, the the
right text box on A1 would populate with the letter B)

If user selects fish from both combo boxes, the text boxes on the left
fill from combo box one, and the text boxes on the right fill from
combo box two.
(example: If user selects sunfish and I caught a Sunfish (S) at C3,
the the first text box on C3 would populate with the letter S and If
user selects Bass and I caught a Bass (B) at A1, the the right text
box on A1 would populate with the letter B.)

Finally, I'd like to add color coordination to this so that if the
count of a fish at a coordinate exceeds a certain limit, the color of
the letter would change at that location.
(Example: If I caught 1-5 bass at a location, text would be black.
6-10, text would be yellow, 11-20, text would be orange, and over 20,
text would be red)

The text boxes are currently named txtA1, A1-2, txtB1, txtB1-2, etc.
for the left and right box at each coordinate respectively.

I am not hugely skilled at programming, and from what a co-worker and
I came up with, think this needs some kind of advanced query, mixed
with some nested loops of code.

Can anyone out there lend a hand on this? It would be hugely
appreciated! I'd be happy to e-mail you a sample database with
sample data if you think you could tackle it. I am leaving on a
fishing trip this coming weekend, and was really hoping to have it
done by then

OK, you have the right kind of names for the text boxes, so
we only need to change that a little. Name the left text
boxes txtLA1,txtLA2,...,txtLB1, . . . and the right text
boxes txtRA1,txtRA2, . . .

The button's click procedure could be along the lines of
this air code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim strSQL As String

For Each ctl In Me.Section(0).Controls
If Left(ctl.Name, 4) Like "txt[LR]??" Then
ctl = Null
End If
Next ctl

strSQL = "SELECT DISTINCT C.[Fish Type], C.[Lake Name], " _
& "C.[Caught X Coord], C.[Caught Y Coord] " _
& "T.[Fish Abbreviation] " _
& "FROM Caught As C " _
& "INNER JOIN [fish types] As T " _
& "ON C.[Fish Type] = T.[Fish Type] " _
& "WHERE C.[Lake Name] = """ & Me.txtLakeName _
& """ AND (C.[Fish Type] = " & Me.combo1 _
& " OR C.[Fish Type] = " & Me.combo2 & ")"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
If rs![Fish Type] = Me.combo1 Then
Me("txtL" & rs![Caught X Coord] & rs![Caught Y Coord]) _
= rs![Fish Abbreviation]
Else
Me("txtR" & rs![Caught X Coord] & rs![Caught Y Coord]) _
= rs![Fish Abbreviation]
End If
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set db = Nothing

 

[tjandt]

Thanks for the reply! I will be trying this out ASAP

I think I kind of understand what you are doing, but was wondering i
I could get a little help clarifying what is happening in some of th
code? I'm a little fuzzy on the first looop, and the SQL statement

'go through all of the controls on the form
For Each ctl In Me.Section(0).Control

'Check to see if the name matches the ones we want. Don't we want t
have the line start out If Left(ctl.Name,6) to capture the whol
field name instead of just the first four characters, or to the ? no
need to be defined by the Left function
If Left(ctl.Name, 4) Like "txt[LR]??" The

'set the value of the control to Null (is this just to clear the gri
before populating it?
ctl = Nul
End I
Next ct

'Define the SQL statement. What are the C. and T. references for?
never saw those before in SQL. Do the _ mean I should keep that al
on the same line, or is that so VBA knows to keep it all on the sam
line

strSQL = "SELECT DISTINCT C.[Fish Type], C.[Lake Name], "
& "C.[Caught X Coord], C.[Caught Y Coord] "
& "T.[Fish Abbreviation] "
& "FROM Caught As C "
& "INNER JOIN [fish types] As T "
& "ON C.[Fish Type] = T.[Fish Type] "
& "WHERE C.[Lake Name] = """ & Me.txtLakeName
& """ AND (C.[Fish Type] = " & Me.combo1
& " OR C.[Fish Type] = " & Me.combo2 & ")

Thanks so much for the help! I can't wait to give this a try

Ti

 

[Marshall Barton]

Responses in line below.
--
Marsh
MVP [MS Access]

I think I kind of understand what you are doing, but was wondering if
I could get a little help clarifying what is happening in some of the
code? I'm a little fuzzy on the first looop, and the SQL statement.

'go through all of the controls on the form?
For Each ctl In Me.Section(0).Controls

Not quite. The loop is through all the controls in the
detail section. Change it to 1 if the text boxes are in the
form's header section, 2 for the footer. You could loop
throught all the controls on the form, but I saw no reason
to spend time looking at irrelevant controls.

'Check to see if the name matches the ones we want. Don't we want to
have the line start out If Left(ctl.Name,6) to capture the whole
field name instead of just the first four characters, or to the ? not
need to be defined by the Left function?
If Left(ctl.Name, 4) Like "txt[LR]??" Then

You caught me ;-) I changed my mind about how to do this
halfway through my response and failed to fixed up the rest
of the statement. It should be:

If ctl.Name Like "txt[LR]??" Then

'set the value of the control to Null (is this just to clear the grid
before populating it?)
Exactly!

'Define the SQL statement. What are the C. and T. references for? I
never saw those before in SQL. Do the _ mean I should keep that all
on the same line, or is that so VBA knows to keep it all on the same
line?

strSQL = "SELECT DISTINCT C.[Fish Type], C.[Lake Name], " _
& "C.[Caught X Coord], C.[Caught Y Coord] " _
& "T.[Fish Abbreviation] " _
& "FROM Caught As C " _
& "INNER JOIN [fish types] As T " _
& "ON C.[Fish Type] = T.[Fish Type] " _
& "WHERE C.[Lake Name] = """ & Me.txtLakeName _
& """ AND (C.[Fish Type] = " & Me.combo1 _
& " OR C.[Fish Type] = " & Me.combo2 & ")"

C and T are aliases for the full table names. It makes it
easier to type and read the query when the table names are
short.

The space + underscore at the end of the lines is VBA's way
of continuing a single statement on multiple lines. I tried
to indent the lines to make it easier to understand, but it
looks like your newsreader program stripped the tab
characters out. Assuming I got all the names right and
didn't make any typing errors (big assumption), you should
be able to Copy/Paste my suggested code into the button's
Click procedure.

The SQL statement looks for all records that match the
current record's lake and either of the combo box's fish
types. The second loop just cycles through the found
records stuffing the fish abbrev into the appropriate text
box.