Form's code to look up values in a table

  • Thread starter Thread starter cinnie
  • Start date Start date
C

cinnie

hello to all
Some of my procedures have code like this:

Select Case n
Case is = 1
Select Case Marker
Case is >15.4
....
Case is >12.7
....
Case is > 8.3
....
End Select
Case is = 2
Select Case Marker
Case is >14.7
....etc

In my code, n runs from case 1 to case 7, and each of these values has 3
Marker cases.

The problem is that every year the values change and I have to go into the
code and manually alter them. What's worse, next year, n may range from 1 to
5, for example, but there may be, say, 6 cases of Marker consider for each n.

Question 1: I'd much rather just use a table like below, but I don't know
how to code the nested Select case statements.

n : 1 1 1 2 ......
Marker : 15.4 12.7 8.3 14.7 ......

Question 2: There are a few places where I use a combobox showing the
values of n as a list, say 1 to 7. Again, I have to recode these each year.
How can I make the combobox show whatever distinct values of n are in the
table above?

much thanks
 
It is a VERY Good idea on your part to move this in to some type of table.
having to modify code for cases which can be listed
in a table is problematic it as you have to modify all the time as you
mention.

cinnie said:
hello to all
Some of my procedures have code like this:

Select Case n
Case is = 1
Select Case Marker
Case is >15.4
....
Case is >12.7
....
Case is > 8.3
....
End Select
Click to expand...

Just build a table:

eg:

CaseN Marker
1 8.3
1 12.7
1 15.4
2 14.7
2 17.1


etc. etc.

Now, to get a value, you go:

Dim strSql As String
Dim strMarker As String
Dim strCaseN As String
Dim strResult As String

strCaseN = 1
strMarker = 12

strSql = " SELECT TOP 1 Marker FROM tblMarkerValues" & _
" where CaseN = " & strCaseN & " and Marker <= " & strMarker & _
" ORDER BY Marker DESC"

strResult = CurrentDb.OpenRecordset(strSql)(0)

Debug.Print strResult

The above stir result will have the next lowest value, in teh above example,
we would get 8.3...
In my code, n runs from case 1 to case 7, and each of these values has 3
Marker cases.
Click to expand...

It not clear "what" you do inside of each "case" in the code..but, it sounds
like some general function could be feed values from a table...

Question 2: There are a few places where I use a combobox showing the
values of n as a list, say 1 to 7. Again, I have to recode these each
year.
How can I make the combobox show whatever distinct values of n are in the
table above?
Click to expand...

I don't really think of a "set of two values" As duplicated values.

However, just build a query and use Group by, or distinct ......

eg:

select distinct CaseN from tblMarkerValues
 
You don't really indicate what you are doing inside the case statement. I
agree with Albert that you ought to move these criteria into a table.
Structure might look like:

YearField NValue Marker SomeValue SomeEqn
2007 1 8.3
2007 1 12.7
2007 1 15.4
2007 2 14.7
2007 2 17.1

I would use a SomeValue field if the values of N and Marker should return a
single value. You could use SomeEqn to store an equation that would be
evaluated in the given situation.

The challenge here is deciding what to do when you've found a criteria that
works.

I like Albert's idea of using SQL to identify your recordsets, but you have
to be careful of the order that you loop through the recordsets. If the
values of N and Marker are in a data table, and you want to compare them to
these values in this new table, you will need to make sure you limit your
result set so that you don't perform the same action on the same record
multiple times. One way to do this might be to create a recordset:

Dim rs as DAO.Recordset
Dim strSQL as string

strSQL = "SELECT NValue, Marker FROM yourTable " _
& " WHERE [YearField] = 2007 " _
& " ORDER BY NValue ASC, Marker DESC"
set rs = currentdb.OpenRecordset
Do While not rs.eof
if x = nValue AND y > Marker Then
'do something
Exit Do
endif
rs.movenext
Loop

Without more information on what you are doing inside the Select Case
statements, or how this code is being used (is it in a forms event), I cannot
provide any more recommendations.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
strSQL = "SELECT NValue, Marker FROM yourTable " _
& " WHERE [YearField] = 2007 " _
& " ORDER BY NValue ASC, Marker DESC"
set rs = currentdb.OpenRecordset
Do While not rs.eof
if x = nValue AND y > Marker Then
'do something
Exit Do
endif
rs.movenext
Loop
Click to expand...

You do realize my sql statement does replace the above loop???

Just use:

strSql = " SELECT TOP 1 Marker FROM tblMarkerValues" & _
" where CaseN = " & strCaseN & " and Marker <= " & strMarker & _
" ORDER BY Marker DESC"

strResult = CurrentDb.OpenRecordset(strSql)(0)

the above will find the "first" value <= strMarker..and restriced to
CaseN...

There not a need for a loop here.....
 
Albert,

Actually, I misread your SQL the first time through, but my point remains
the same. Without knowing how the OP is using the current Case statement, it
is difficult to determine what to do with the information once you get it.

Unless you can code some actions (SQL action strings, macros, strings that
can be processed using Eval( ) ) into this new table, the poster is still
going to have to have specialized code that does something based on the value
of n and Marker, and I'm not sure how putting the values in a table fix that.

Maybe the approach is to write a custom function or subroutine that you pass
the values of N and Marker to. Then all you would have to edit is the
function.

Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Albert D. Kallal said:
strSQL = "SELECT NValue, Marker FROM yourTable " _
& " WHERE [YearField] = 2007 " _
& " ORDER BY NValue ASC, Marker DESC"
set rs = currentdb.OpenRecordset
Do While not rs.eof
if x = nValue AND y > Marker Then
'do something
Exit Do
endif
rs.movenext
Loop
Click to expand...

You do realize my sql statement does replace the above loop???

Just use:

strSql = " SELECT TOP 1 Marker FROM tblMarkerValues" & _
" where CaseN = " & strCaseN & " and Marker <= " & strMarker & _
" ORDER BY Marker DESC"

strResult = CurrentDb.OpenRecordset(strSql)(0)

the above will find the "first" value <= strMarker..and restriced to
CaseN...

There not a need for a loop here.....
Click to expand...
 
Back
Top